Re: TO_DATE ignores

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
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-l

Received on Thu Feb 21 2013 - 14:46:57 CET