HomeUncategorized › The trouble with dates

The trouble with dates

Judging from the amount of forum posts around the subject of dates, you would dates in Oracle are difficult, confusing and just plain awkward to use.

However, far from being difficult, I think dates in Oracle are really, really easy.

The DATE datatype in Oracle holds date and time (to the nearest second) information and stores it in its own internal format. Everyone knows that date+time consists of year, month, day, hours, minutes and seconds, so Oracle gives you a method of allowing you to specify which bits are the year, which are the month, etc. What could be simpler than that?

It’s the TO_DATE() function that allows you to pass in a string and have Oracle read it as a date. Similarly, it’s the TO_CHAR() function that allows you to take an Oracle DATE value and convert it back into a string that you and I can understand.

Let’s look at an example:

to_date('01/01/2011', 'dd/mm/yyyy')
to_char(sysdate, 'dd Month yyyy hh24:mi')

Granted, it’s a wee bit of typing, but it’s not difficult. The list of available format masks can be found here. It’s worth nothing that you can also use these format masks with TRUNC and ROUND, for example if you need to find the quarter start date of a given date etc.

People abuse dates in many ways in Oracle, such as only using 2-digit years (why, I’m not sure. It’s as if Y2K never happened!) or worse, when they want to compare dates, they first convert them to strings.

This is a bad idea in many ways – for one, if you don’t organise your date-as-a-string correctly, comparisons are meaningless – eg. ‘2 Feb 1999’ is greater than ‘1 Jan 2011’, because ‘1’ < '2' in the string comparison. For another, when you convert the DATE into a string (or number), you remove vital information from the optimizer. This can lead to incorrect cardinality estimates which could throw your execution path off. Why? Well, how many days are there between 1st January 2011 and 31st December 2010? Of course, the answer is 1. But if you were converting those to numbers, the question becomes "What is the difference between 20110101 and 20101231" to which the answer is, of course, 8870. 1 vs 8870 - that's a fair amount of difference! By converting dates to strings or numbers, you leave the optimizer no option than to do the equivalent of sticking its finger in the air to guess how many rows it's going to retrieve! Don't be afraid of Oracle's DATE format, it's simple to use and can help you do some very powerful things without much work from you - that's got to be a bonus! (Grouping results into quarters? No problem! Pivoting by month? Not a problem either! etc etc)

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>