Dates without time

Oracle’s DATE data type stores both date and time components. If a date is stored without specifying a time component then the time is implicitly set to 0 hours, 0 minutes and 0 seconds (00:00:00), which is exactly midnight at the start of the date.

Nothing unusual with this but quite regularly a data model will call for dates without any time component. The problem resulting from this scenario is how to ensure that time components other than 00:00:00 don’t manage to find their way into the data. If dates with time manage to find their way into such data then various problems occur. For example, checks for equality will fail, e.g.:

FROM   ...

Range searches will fail to find data on the last date of the range, e.g.:

FROM   ...

GROUP BY expressions will report groups down to the specific time, e.g.:

FROM   ...

The solution to the problems noted about could be to TRUNC the date whenever the date column is referenced, e.g.:

FROM   ...

and this is what regularly occurs in applications I have had to deal with. The line of thinking here is clearly “Since I’m not sure if a date with a time component has managed to get into the system, I’ll ensure I only use the date part of the DATE column”. Unfortunately all those TRUNC functions are overhead and can easily to lead of suboptimal query execution plans.

Other systems have taken the approach of trying to prevent time components being stored by removing the time components from the dates when storing the data, e.g.:


Clearly this is the better approach than storing the time component and trying to deal with dates with time. However the problem still remains that a rogue entry can make its way into the data. Perhaps an infrequently used update routine is missing the TRUNC or perhaps some data patch simply used SYSDATE instead of TRUNC(SYSDATE). At the end of the day you’re still not 100% certain that all the entries are “timeless”. Once doubt sets in then you’ll find TRUNCs cropping up into the SELECTs and it’s all downhill from there…

The proper solution to this problem is, like all good solutions should be, quite simple. Since Oracle does not provide a date only data type create one! This is done by adding a CHECK constraint:

ADD CONSTRAINT date_col_chk
CHECK ( date_col = TRUNC(date_col) )

With the above constraint in force you can rest in the comfort of knowing that all the dates in the column are guaranteed have a time component of 00:00:00. No need for any messy TRUNC functions littered throughout the code. DML operations trying to sneak time components onto the dates will be rejected with an ORA-02290 exception.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s