In Oracle, why does this return March 1st?

Question

In Oracle, this returns 03/01/2010. That does not make sense to me. Anybody know why?

SELECT TO_DATE( '2010' ,'yyyy' ) AS STRANGE_YEAR_RESULT
FROM DUAL

I've tried on Oracle 10g and 11g.

Answer 1

I don't think there is any sensible reason, it's just "what it does". It also got discussed on the OTN forums about a year ago.

Answer 2

Don't know, but my guess is that months are zero based, so Jan = 0, Mar = 2, etc.

"10" might be a Y2K problem in the making, but it's being interpreted as 2010.

And if no day of month is given, perhaps it's assuming the first day of the month.

Why test this? You'd never want to code this way.

Answer 3

Oracle needs a complete DateTime in its Date type value field, thus making it take the first day of the current month, I would guess, since you required no other information than the year. Remember that you always need to cast through TO_DATE() and TO_CHAR() dates in Oracle. Assuming so, Oracle "knows" that you will get the information required.