Create ExpiryDate during table definition in Oracle-CodePudding

I'm trying to create a table named MEMBER. The datatype for REGISTERDATE is DATE and the default value is SYSDATE. There is another column named EXPIRYDATE, datatype is DATE and the default value is 1 year after SYSDATE. So how am I going to create for the column of EXPIRYDATE when creating the table definition? I tried REGISTERDATE DATE DEFAULT SYSDATE, EXPIRYDATE DATE 365, It showed error..

CodePudding user response：

You're so close, you're just missing the default keyword and sysdate after expirydate date

-
create table MEMBER (
    REGISTERDATE date default sysdate,
    expirydate date default sysdate 365
)

test:

insert into MEMBER(REGISTERDATE) values (sysdate)

output:

CodePudding user response：

Not all years have 365 days; on average, only 3 out of 4. But you can literally define an expression that expresses "1 year after":

create table member (
    id number(*,0) not null primary key,
    registerdate date default sysdate,
    expirydate date default sysdate   interval '1' year
);

Remember that Oracle won't use the default value if you provide your own, even if it's null. You need to complete omit the field from the INSERT INTO statement.

Demo