Strange Oracle date behavior

Question

I had a strange problem in one of our production databases. Long story short, simple query:

select id, trunc(stdate) from table_name where trunc(stdate) = '05-FEB-09';  

returned no rows. However,

select trunc(stdate) from table_name where id = sought_after_id;

returned '05-FEB-09'. Only after I tried:

update table_name set stdate = '05-FEB-09' where id = sought_after_id;

my original query worked as expected:

select id, trunc(stdate) from table_name where trunc(stdate) = '05-FEB-09';
> sought_after_id, '05-FEB-09'

So, what was happening with my stdate values?

Answer 1

Hi Igor,

you should always compare dates with dates (apples to apples...) and not rely on implicit conversion.

Since TRUNC(date) returns a date you should compare it to a date:

select id, trunc(stdate) from table_name where trunc(stdate) = DATE '2009-02-05'

or

select id, trunc(stdate) 
  from table_name 
 where trunc(stdate) = TO_DATE('05-FEB-09', 'DD-MON-RR'))

Update In response to Igor's first comment:

Relying on implicit data conversion makes the result of the query dependent upon several session parameters. One of these parameters must have been modified if you're seeing different results now from the days before. You can make your query "session-independent" by not relying on implicit conversion.

It is worth insisting that your first query depends upon the parameters of the client session. If the session modifies its default date display setting NLS_DATE_FORMAT your query will not return the same results.

On a related note, the DD-MON-RR is a perfectly acceptable date format for display, but it is not well-suited to use in your code since there is an ambiguity concerning the century AND you rely on the NLS_DATE_LANGUAGE parameter for the months.

Answer 2

There are two things it might be. The first is that the YY date mask might be obscuring different values for century; if we don't specify one using YYYY it defaults to the current century. The second is that Oracle dates contain a time element. If we don't specify a time it defaults to midnight.

SQL> select * from d
  2  /

D1                ID
--------- ----------
19-JAN-10          1
19-JAN-10          3
19-JAN-10          2

SQL> select * from d
  2  where d1 = to_date('19-JAN-10', 'DD-MON-YY')
  3  /

D1                ID
--------- ----------
19-JAN-10          3


SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI-SS'
  2  /

Session altered.

SQL> select * from d
  2  /

D1                           ID
-------------------- ----------
19-JAN-1910 00:00-00          1
19-JAN-2010 00:00-00          3
19-JAN-2010 12:00-00          2

SQL>

Only one record - #3 - matched to_date('19-JAN-10', 'DD-MON-YY') because #1 had a different century and #2 had a different time.

edit

All rows in my table have a same century and 00:00-00 time element.

Well that is true now, but perhaps only because you have fixed the data. There was definitely something different about that particularly date, otherwise Oracle would have treated it the same as all the others.

By running the update you have erased the differences. Ex post facto it is not possible for us to tell you what was wrong with the data. If you have an audit trail you ought to consult it, because it would be helpful for you to know what part of your system inserted or updated that record with an invalid date mask, so you can fix it.