Re: TO_DATE ignores
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Feb 2013 13:46:57 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
