Constrains Using Date

I'm trying to create a constrain to check the record is no greater than 2016. Here is the record in my database Here is my query: ALTER TABLE SIGHTINGS ADD CONSTRAINT CK_SIGHT

Solution 1:

One option is to use the extract() function as you just want to check for the year:

ALTERTABLE SIGHTINGS 
   ADDCONSTRAINT CK_SIGHTING_DATE 
   CHECK (extract(yearfrom SIGHTING_DATE) <2016);

or use an ANSI date literal:

ALTERTABLE SIGHTINGS 
   ADDCONSTRAINT CK_SIGHTING_DATE 
   CHECK (SIGHTING_DATE <date'2016-01-01');

Solution 2:

you have make in date specifcation

ALTERTABLE SIGHTINGS 
   ADDCONSTRAINT CK_SIGHTING_DATE 
   CHECK (SIGHTING_DATE <=/*TO_DATE('01-JAN-16','DD-MON-YY') as I was pointed your should specify 4 digits for year*/ 
                 TO_DATE('01-JAN-2016','DD-MON-YYYY'));

or

ALTERTABLE SIGHTINGS 
   ADDCONSTRAINT CK_SIGHTING_DATE 
   CHECK (SIGHTING_DATE <=DATE'2016-01-01');

another one things its what do you mean when you say "no greater than 2016" Your check alow dates in 01-jan-2016 but not allow 02-jan-2016. If you would like to include whole 2016 year write

SIGHTING_DATE <DATE'2017-01-01'

or trunc(SIGHTING_DATE,'yy') <=DATE'2016-01-01'