Re: select to_date('2004','YYYY') NOT Jan 1 2004 - Here's why

OK, I found out ( should have paid more attention in class, many years ago)

Oracle 'knows' what dates should look like ( depending on your NLS_DATE_FORMAT) so, when you request a to_date conversion, Oracle will substitute default values for any part of the date you do not specify in the format string: If no Month, the Current month is used.
If no Day, the First day of the Month is used( because all months have a first) If no Year, the Current year
Time is Midnight, if not provided.

Turkbear <john.g_at_dot.spamfree.com> wrote:

>"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote:
>
>>
>>"Jarson" <jarsonk_at_nospam.com> wrote in message
>>news:pdi8c.18858$A_2.978850_at_news20.bellglobal.com...
>>> When I run the following SQL
>>>
>>> SQL> select to_date('2004','YYYY') from dual ;
>>>
>>> The result is 01-Mar-04. I was expecting 01-Jan-04.
>>> Is it returning March because that is the current month, or does it always
>>> return March? Does anyone else get this (I'm on Oracle 8.1.6)?
>>>
>>> Jarson
>>>
>>>
>>Why? You have only specified the year and not the month and the day. If
>>you want 1/1/2004 then specify that.
>>Jim
>>
>
>That does not explain the 'assumption' Oracle is making, that you want the 1st day of the current month for the year
>indicated...Why does it even query SYSDATE???
>
Received on Wed Mar 24 2004 - 12:10:54 CST