Wrong way to Query on Dates – the Persistent Offender

Some of you, hopefully most of you, will find nothing new in this little posting. Except maybe the realisation that you are not the only one utterly frustrated by the ever-reoccuring nature of this. Actually, I should say “frustrated by these“. There are two things here I keep coming across, year after year after year…Actually, three things. {Maybe I am frustrated too easily, I need a holiday….}

{Maybe I should start a “weekend rant” as well as a”Friday Philospohy” thread…}

OK, you have a massive ORDERS table with a column ORDER_DATE on it, type DATE. This column has a normal B-tree index on it. You want to query for all ORDERS placed on a date, say 1st November 2009, so you issue a query with the following WHERE clause:

WHERE ORDER_DATE = ’01-NOV-09′

But you would not, would you? DATE columns can (and often do) contain a time portion. And in this case it does, so you would track down whoever specified this table, give them a “talking to”, get the column renamed to ORDER_DATETIME and use something like:

WHERE TRUNC(ORDER_DATETIME)=’01-NOV-09′

But you would not, would you? The function on ORDER_DATETIME will prevent the index being used (unless you added a function-based index, you clever so-and-so).
Now this is the first second thing that I keep seeing and have to run through with people. Some people simply have not yet been told that functions on columns stop indexes from being used, others know this but do not quite understand that a TRUNC {or SUBSTR on the first X characters} still stop indexes being used by Oracle, even though they know that “functions stop indexes being used” – It seems sensible to them, as humans, that if they had a printed index they would not think twice about doing a range check with the index. But the CBO is not a human and is not so smart. So you explain this to them.

So maybe you get to the point where the following is the WHERE clause to use:

WHERE ORDER_DATETIME >= ’01-NOV-09′
AND ORDER_DATETIME < ’02-NOV-09′

That will work. It will use the index on ORDER_DATETIME, it will pass unit testing, it will get released and work fine.

Until someone runs this on a system with a different NLS_DATE_FORMAT.
Or in a session with a different NLS_DATE_FORMAT.
Or on an application server/middle tier where the NLS_DATE_FORMAT is different and it all falls over.
{and for NLS_DATE_FORMAT, keep in mind that NLS_LANG and NLS_TERRITORY can implicitly alter NLS_DATE_FORMAT, so anyone supporting database systems over national boundaries may well be nodding their heads sagely right now}.

It falls over because there is an implicit data conversion of the string ’01-NOV-09′ to a date. It uses your default date format, which is not the same on all databases, on all middle tiers or even all sessions (I always alter my session to show the time portion and I seem to be one of the first to hit these issues, maybe as a result). It is far less common, but I must come across this NLS issue once a year minimum and usually it has been a bit of a major issue (like feeding back utterly the wrong data to a regulatory body, Ouch).

So, you need to explicitly state your character to date format conversion. And to all developers reading this, Yes I know, it is a pain to type those extra bits, but you get paid to do it! 🙂
WHERE ORDER_DATETIME >= TO_DATE(’01-NOV-2009′,’DD-MON-YYYY’)
AND ORDER_DATETIME < TO_DATE(’02-NOV-2009′,’DD-MON-YYYY’)

That will work and keep working.

So:

• Call columns that hold a DATE with a time portion blar_DATETIME
• Don’t have functions on the column, move them over to the other side of the WHERE statement(s)
• Explicitly specify your date conversions. Always. Every time.

I know, I know, most of you knew all of that. Those of you who didn’t, well probably no one had told you before but now you have been told. And that is the heart of it, these things are easy to explain, easy to understand, but somehow seem to be missed when people are getting going with Oracle. They just need telling.

And I’m only able to tell so many. So, the rest of you, will you please tell all inexperienced Oracle people about these three things as I don’t think I can face another 20 years of telling people these things 🙂

{The serious final line is, there are lots and lots of interesting gotchas, what-ifs, quirks on things in Oracle, all of which are good to share, but perhaps the most important things to keep sharing are those things “we all know”, but those with less experience don’t.}