Re: TO_DATE ignores
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Feb 2013 13:29:40 +0000
Hi Fergal,
On 21/02/13 12:45, Fergal Taheny wrote:
> Hi,
> Tried this on 10.2.0.4 and 11.1.0.7
>
> I guess I'm not the first to stumble across this.
>
> I'm extracting data from an application where some dates are stored free
> form in varchars (I know. Vendor supplied application.)
> ...
As far as I remember, Oracle will attempt to convert your varchar "date"
to a date, even if it fails to match the supplied format string. Some
date strings it can cope with while others, it cannot.
SQL> alter session set nls_date_format = 'dd/mm/yyyy';
SQL> select to_date('01/MAR/2001','dd-mm-yyyy') from dual;
01/03/2001
That one worked even though the format string was nothing like the
supplied date string - as you have discovered.
SQL> select to_date('01/MAR/2001','dd-mm-yy') from dual;
01/03/2001
That one also worked. Even though the format string was shorter than the
date string.
SQL> select to_date('01/MAR/2001','yy-mm-dd') from dual;
ORA-01830: date format picture ends before converting entire input string
That wasn't so good!
SQL> select to_date('01/MAR/2001','yyyy-mm-dd') from dual;
ORA-01858: a non-numeric character was found where a numeric was expected
Neither was that one.
So, Oracle tries to be helpful in converting text to dates, but I
assume it's only when the string passsed looks like a data and isn't in
any way ambiguous. It may barf on indeterminate dates like '01/03/2001'
- is that first March (yes it is!) or Third January (no it's not!) -
people in the US may have a different opinion. Hence, Oracle can't tell?
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:
