CODEWITHSUNDEEP
sqloracle-database
Potato
Potato

Reputation: 172

How to Update end date by adding duration to start date SQL

As in title i don't know how to add duration (from the same table) to start date, to get end date in result, table is looks like:

create table NR1
(
  id         INTEGER not null,
  price      INTEGER,
  price2     INTEGER,
  start_date DATE,
  end_date   DATE,
  duration   NUMBER
)

i tried something like this, but i have some errors:

update nr1
set end_date =dateadd(MONTH, nr1.duration, nr1.start_date);

it should works, but i have problems with MONTH, at all SQL developer says that.

Upvotes: 0

Views: 340

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

You are using syntax form a different database platform. The Oracle equivalent is the add_months() function:

update nr1
set end_date = add_months(nr1.start_date, nr1.duration);

If you just add a plain number to a date that's treated as a number of (whole or partial) days. You can also use intervals (via numtodsinterval()) for other periods of time, but using that for months can be difficult.

Months and days are simple through the basic functionality and functions though.

If you're on a recent version of Oracle you should consider using a virtual column so you don't have to maintain both values.

Upvotes: 3

Swapnil Sonawane
Swapnil Sonawane

Reputation: 1

These are perfectly working SQL statement.

SELECT DATEADD(HOUR, 5, '5:00');
SELECT DATEADD(MINUTE, 5, '5:00');
SELECT DATEADD(SECOND, 5, '5:00');

You can try queries here: W3Schools Link

Upvotes: -3

Aleksej
Aleksej

Reputation: 22949

You probably need add_months:

update NR1
set end_date = add_months(start_date, duration)

Upvotes: 3

Related Questions