Insert date now interval 2 days-CodePudding

for some reason I am getting a syntax error:

dflgudv3jfgeq0=> INSERT INTO habit_instances (habit_id,completed,create_date,due_date)
dflgudv3jfgeq0-> VALUES
dflgudv3jfgeq0-> (1,FALSE,NOW(),NOW() INTERVAL 2 DAYS);
ERROR:  syntax error at or near "2"
LINE 3: (1,FALSE,NOW(),NOW() INTERVAL 2 DAYS);

From what I've read this should be the correct syntax.

CodePudding user response：

The interval value needs to be enclosed in single quotes:

INSERT INTO habit_instances (habit_id, completed, create_date, due_date)
VALUES (1, false, now(), now()   interval '2 days');

The specification '2 days' as a single string is a Postgres extension to the SQL standard. A SQL standard compliant way of writing this would be interval '2' day or written with a full interval specification: interval '2' day to minute

CodePudding user response：

INSERT INTO habit_instances (habit_id,completed,create_date,due_date)
VALUES (1,FALSE,NOW(),NOW() INTERVAL '2 day');

both forms 2 day and 2 days work

CodePudding user response：

In SQL standard run below code.

INSERT INTO habit_instances (habit_id,completed,create_date,due_date) 
     VALUES (1, FALSE, GETDATE(), DATEADD(day, 2,GETDATE()));