Re: TO_DATE ignores
Date: Thu, 21 Feb 2013 13:46:57 +0000
Message-ID: <51262551.7020008_at_dunbar-it.co.uk>
Of course, whatever you have for the default date format (NLS_DATE_FORMAT) makes a difference.
With 'dd/mm/yyyy', attempting to convert "01/03/2001" without any formt string works fine, but with 'DD-MON-YY' it fails with ORA-01843: not a valid month.
If you use 2 character years, dd/mm/yy, then what does '01/02/03' represent when no format is supplied? Oracle do advise using 4 character years - but the default they provide is a YY. Hmmm.
The SQL Reference Manual, in the section on Format Models, it says:
"Oracle Database converts strings to dates with some flexibility. For example, when the TO_DATE function is used, a format model containing punctuation characters matches an input string lacking some or all of these characters, provided each numerical element in the input string contains the maximum allowed number of digits - for example, two digits '05' for 'MM' or four digits '2007' for 'YYYY'...".
Followed by a couple of examples.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34924 - scroll down to below table 3-15 of datetime format elements.
HTH
Cheers,
Norm.
-- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 21 2013 - 14:46:57 CET