DATE is not a date!
DATEis not a date.
DATE '2012-06-22'is not a date.
CURRENT_DATEis not a date.
SYSDATEis 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.
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)))
27 June 2012 - 10:30 am
I’ve gotten many a bad plan with range partitioned tables on a “date” column. Without a real date datatype it makes more sense to use list partitioning.
28 June 2012 - 5:14 pm
One of the most confusing things for “outsiders” would be the lack of support for empty strings in the Oracle database. Especially, for a Java developer, where “” and null are not the same (and can be bound to PreparedStatements differently), this can be a major source of confusion.
28 June 2012 - 5:17 pm
Yes, the “empty strings are NULL” thing has got to be at the top of the “top confusing things in Oracle” list 🙂