DB2 - increment CURRENT_DATE

1
votes

I'm just trying to set the expire_date_membership to a date that is going to be 2 years after the current date, but don't seam to get the syntax right. Can someone help?

CREATE TABLE Members (
   pid INTEGER NOT NULL, 
   registration_date_membership DATE DEFAULT CURRENT_DATE, 
   expire_date_membership DATE DEFAULT (CURRENT_DATE + 24 MONTHS), 
   membership_fees DOUBLE, 
   overdue_fees DOUBLE, 
   PRIMARY KEY(pid),
   FOREIGN KEY (pid) REFERENCES Person)

Error message:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "DEFAULT" was found following "ate_memebership DATE". Expected tokens may include: "CHECK". SQLSTATE=42601

2
sqldatedb2

2 Answers

2
votes

You're missing WITH keyword.

Your query should be :

CREATE TABLE Members (
   pid INTEGER NOT NULL, 
   registration_date_membership DATE WITH DEFAULT CURRENT_DATE, 
   expire_date_membership DATE WITH DEFAULT (CURRENT_DATE + 24 MONTHS), 
   membership_fees DOUBLE, 
   overdue_fees DOUBLE, 
   PRIMARY KEY(pid),
   FOREIGN KEY (pid) REFERENCES Person)

More : Default values

0
votes

Hope this will help you, but instead of DATE datatype, it has changed to TIMESTAMP.

registration_date_membership TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, expire_date_membership TIMESTAMP WITH DEFAULT TIMESTAMPADD(256,40,TIMESTAMP((CURRENT TIMESTAMP)),