DATE is not a date!

Consider:

• DATE is not a date.
• DATE '2012-06-22' is not a date.
• CURRENT_DATE is not a date.
• SYSDATE is not a date.
• TRUNC(SYSDATE) is not a date.
• TO_DATE('22/06/2012','DD/MM/YYYY') is not a date.

Oracle SQL does not have a native date datatype.

Explanation: the datatype called “DATE” actually represents a Date+Time. It always has a time portion. For example, the literal expression DATE '2012-06-22'has a time component that means midnight, to the nearest second. It is only by convention that a DATE with a time of 00:00:00 is used to represent the whole day, rather than exactly midnight; but this convention only works if the developer carefully writes his code to ensure this is true.

It is theorized that the ancients solved “once and for all” the problem with dates vs. times.

To an experienced Oracle developer, this is not a problem, it feels perfectly natural to say “DATE” but internally to be thinking in terms of time values. It’s easy to forget that we’re even doing this subconsciously. To an outsider (e.g. business analysts, testers, Java developers), it can be confusing – and it is often not immediately obvious that the confusion exists. It’s not until you start explaining why SYSDATE <= DATE '2012-06-22' evaluates to FALSE, even if today is the 22nd day of the month of June in the year of our Lord 2012 that you realise they have been labouring under a false assumption: that a DATE is a date, that it represents the full 24-hour period that a normal human would call “22 June 2012”.

If I was invited to change the name of just one thing in Oracle (and everyone was willing to make all the changes necessary to their code to accommodate my whim) it would be to change “DATE” to “DATETIME”.

I ask you: is there anything else in Oracle that confuses outsiders more often than this misnomer?

P.S. Now for a freebie: here is a summary of a number of transformations that may be done to remove the TRUNC function call around a date column (a and b are all of type DATE):

 TRUNC(a) =  TRUNC(b)  =>  (a BETWEEN TRUNC(b) AND TRUNC(b)+0.99999)
 TRUNC(a) <  TRUNC(b)  =>  a < TRUNC(b)
 TRUNC(a) <= TRUNC(b)  =>  a < TRUNC(b)+1
 TRUNC(a) >  TRUNC(b)  =>  a >= TRUNC(b)+1
 TRUNC(a) >= TRUNC(b)  =>  a >= TRUNC(b)
 TRUNC(a) =  b         =>  (a BETWEEN b AND b+0.99999
                            AND b = TRUNC(b))
 TRUNC(a) <  b         =>  (a < TRUNC(b)+1
                            AND NOT (a=TRUNC(a) AND b=TRUNC(b)))
 TRUNC(a) <= b         =>  a < TRUNC(b)+1
 TRUNC(a) >  b         =>  a >= TRUNC(b)+1
 TRUNC(a) >= b         =>  (a >= TRUNC(b)
                            AND NOT (b=TRUNC(b)))

Jeffrey Kemp 22 June 2012

pop-quiz-hotshot / SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.