Something I didn't know up TO_DATE

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Look at this (my session date format is DD/MM/YYYY):

select to_date('2011','YYYY') d from dual;

D                   
---------------------
01-02-2011 00:00:00

select trunc(to_date('2011','YYYY'),'Y') d from dual;

D                   
---------------------
01-01-2011 00:00:00 

I didn't know that when converting a year from characters to a date, Oracle would return the value using the current month (in the SQL reference I can't find anything about this behavior).

I found this out because while working on some queries spanning over a 12 month period supposedly starting from the first month of the year, I could not understand why I was seeing a missing January 2011 and an unwanted January 2012 in the result set.

Initially I feared that this was something introduced with Oracle 11gR2, but I later verified that this happens on Oracle 10gR2 as well, so it must have always been like this.

Fortunately in my applications I never store date information as strings, so I don't really expect this to affect my software, but finding this at a customer's site, makes me think that I'll need to search each and every occurrence of this and replace it with the TRUNCated expression in order to make their queries consistent. 

You never stop learning.