adding a min and max date and getting error: literal does not match format string-CodePudding

I am trying to create a table for bookings and want there to be a check constraint where the customer can only insert the D.O.B from a certain year to another certain year but keep getting the same error message

Any help would be very appreciated

create table guest
( Guest_ID      varchar2(8)       primary key, 
 Family_Name    varchar2(20)      not null,
 Given_Name     varchar2(20)      not null,
 Date_of_Birth  date              check (Date_of_Birth between date '01/01/1904' and 
                                         date '01/01/2004' ) not null,
 Address        varchar2(80)      not null
);

CodePudding user response：

Use the following code:

create table guest
( Guest_ID      varchar2(8)       primary key, 
 Family_Name    varchar2(20)      not null,
 Given_Name     varchar2(20)      not null,
 Date_of_Birth  date              check (Date_of_Birth between to_date('01/01/1904','DD/MM/YYYY') and 
                                         to_date('01/01/2004','DD/MM/YYYY')) not null,
 Address        varchar2(80)      not null
);

CodePudding user response：

The Tip from jarlh(comments) works. Problem might be your Date format.

create table guest
(Guest_ID      varchar2(8)       primary key, 
 Family_Name    varchar2(20)      not null,
 Given_Name     varchar2(20)      not null,
 Date_of_Birth  date              check (Date_of_Birth between date '1904- 
                                  01- 01' and date '2004-01-01' ) not null,
 Address        varchar2(80)      not null
 ); 

insert into guest values ('t','t','t','18-NOV-03','t')

works fine.

Fiddle

CodePudding user response：

I would simplify that and prefer EXTRACT because you don't care about the day, but only want to check a range of years...

...CHECK (EXTRACT(YEAR FROM Date_of_Birth) BETWEEN 1904 and 2003 )...

...unless you really have to accept the 1st of January 2004 as valid date,too.