Re: TO_DATE ignores

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
--
http://www.freelists.org/webpage/oracle-l