What checks this Oracle constraint

Question

I do not understand this Oracle constraint:

ALTER TABLE TableName ADD (
CONSTRAINT CHK_DATE
CHECK (date_start = trunc(date_start, 'dd') and date_end = trunc(date_end, 'dd')));

It always throws an ORA-02290 error. Could someone explain this for me, please.

Answer 1

Oracle provides no way to store date values without any time. You can keep the time part always at midnight, a lot of users do that. However, if somehow some minutes or hours creep into the column, the date arithmetic can fail, probably without anybody noticing.

Your constraint just makes sure that the date+time columns date_start and date_end values stick to the expectations and don't store any time component.

An example:

SELECT t, CASE WHEN t=trunc(t,'DD') THEN 'check ok' ELSE 'check fails' END AS chk
  FROM (select (DATE '2013-06-26' - INTERVAL '1' HOUR)+10*level*(1/24/60) as t from dual connect by level < 12);

25.06.2013 23:10:00 check fails
25.06.2013 23:20:00 check fails
25.06.2013 23:30:00 check fails
25.06.2013 23:40:00 check fails
25.06.2013 23:50:00 check fails
26.06.2013 00:00:00 check ok
26.06.2013 00:10:00 check fails
26.06.2013 00:20:00 check fails
26.06.2013 00:30:00 check fails
26.06.2013 00:40:00 check fails
26.06.2013 00:50:00 check fails

Answer 2

It checks that both date_start and date_end have a time value of 00:00:00 (i-e no time component).

The DATE datatype in Oracle is a point in time (precise to the second). Thus it always has a time component.

For many applications, it is sometimes useful to use whole days without the time component because calculations are easier with whole days. Thus to make sure all date inserted will have no time component, you can add a constraint to the column.

To insert or update, use either a format without time (or with time=00:00:00):

to_date('2013-01-01', 'yyyy-mm-dd')

or use TRUNC.

Answer 3

This constraint says both of the fields DATE_START and DATE_END must be truncated dates, i.e. must have nothing specified for hours, minutes, and seconds. Thus, the following INSERT should work:

INSERT INTO TABLENAME (DATE_START, DATE_END)
   VALUES (TRUNC(SYSDATE), TRUNC(SYSDATE)+INTERVAL '1' DAY)

while the following will not:

INSERT INTO TABLENAME (DATE_START, DATE_END)
   VALUES (SYSDATE, SYSDATE+INTERVAL '1' DAY)

Share and enjoy.