Constrains using DATE

Question

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_SIGHTING_DATE 
   CHECK (SIGHTING_DATE <=TO_DATE('01-JAN-16'));

But I got an error says: ERROR at line 1:

ORA-02436: date or system variable wrongly specified in CHECK constraint.

I've checked some similar questions on this website but there solutions doesn't solve my problem.

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Oct 11 '16 at 05:58

score 3 · Accepted Answer · answered Oct 11 '16 at 06:02

3

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

ALTER TABLE SIGHTINGS 
   ADD CONSTRAINT CK_SIGHTING_DATE 
   CHECK (extract(year from SIGHTING_DATE) < 2016);

or use an ANSI date literal:

ALTER TABLE SIGHTINGS 
   ADD CONSTRAINT CK_SIGHTING_DATE 
   CHECK (SIGHTING_DATE < date '2016-01-01');

answered Oct 11 '16 at 06:02

I've tried both options, but I get this error: ORA-02293: cannot validate (S4369490.CK_SIGHTING_DATE) - check constraint violated. – Sean Li Oct 11 '16 at 23:10
@SeanLi: then apparently you are inserting rows that violate that rule (e.g. a date that is in 2016) – Oct 11 '16 at 23:21
Do you mean there are already some existing records violate that rule? – Sean Li Oct 13 '16 at 10:41

Michael Piankov · Answer 2 · 2016-10-11T06:30:04.973

0

you have make in date specifcation

ALTER TABLE SIGHTINGS 
   ADD CONSTRAINT 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

 ALTER TABLE SIGHTINGS 
   ADD CONSTRAINT 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'

edited Oct 11 '16 at 06:30

answered Oct 11 '16 at 05:27

Michael Piankov

1,989
1
8
19

2

`'01-JAN-16','DD-MON-YY'` depends on local NLS_DATE_LANGUAGE, it may fail for other languages than english. Also two digit year `16` is not very smart. – Wernfried Domscheit Oct 11 '16 at 05:42
How it depend? I write a chacter string and specify a format. In whole century from 2000 to 2099 it will return date 01 january 2016. – Michael Piankov Oct 11 '16 at 06:17
When I try your first proposal I get the same error: **ORA-02436: date or system variable wrongly specified in CHECK constraint** – Wernfried Domscheit Oct 11 '16 at 06:22

Constrains using DATE

2 Answers2

Linked