CODEWITHSUNDEEP
sql
jerome22
jerome22

Reputation: 1

How to insert date using value from previous column

How to add days using value from the previous column

CREATE TABLE rent (
    date of first rent DATE NOT NULL,
    date of 2nd rent   DATE NOT NULL,
);

INSERT INTO rent VALUES(
  TO_DATE('2011-02-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS'),
  DATEADD(day,7,'2011-02-11 16:00:00')
);

I expect it to write down date of first rent as 2011-02-11 16:00:00 and date of 2nd rent as 2011-02-18

But that's not selecting the previous column and instead just adding the date to a known value.

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use insert . . . select:

INSERT INTO rent (date1, date2)
    SELECT date1, date1 + interval '7' day
    FROM (SELECT TO_DATE('2011-02-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS') as date1
          FROM dual
         ) x;

Note that this lists the columns being inserted. This is a best practice to avoid unnecessary bugs.

The DATEADD() function is defined (primarily) in SQL Server. Oracle uses the more standards-compatible INTERVAL arithmetic.

Upvotes: 1

Related Questions