RE: TO_DATE ignores

From: Joel Slowik <jslowik_at_cps92.com>
Date: Thu, 21 Feb 2013 13:50:48 +0000
Message-ID: <18c4923017a447f1a282d740d4a00993_at_BLUPR07MB051.namprd07.prod.outlook.com>

select to_date('01-JAN-01','DD/MM/YYYY') from dual;

select to_date('01-JAN-01','FXDD/MM/YYYY') from dual;

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fergal Taheny Sent: Thursday, February 21, 2013 8:40 AM To: oracle_at_dunbar-it.co.uk
Cc: oracle-l-freelists
Subject: Re: TO_DATE ignores

Hi Norman,
Yeah oracle is trying to be helpful alright but it's not being helpful at all.

I want it to fail.

Couldn't you classify this as an automagical feature. The type of thing Tom Kyte complains about.

Regards,
Fergal

On 21 February 2013 13:29, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:

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

--
Fergal Taheny
Pentec IT Limited
2 knightsbrook court, Dublin Road, Trim, Co. Meath.
+353 (0) 87 9823137
ftaheny_at_gmail.com

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify us immediately.  It is possible for data transmitted by email to be deliberately or accidentally corrupted or intercepted. For this reason, where the communication is by email, Pentec IT does not accept any responsibility for any breach of confidence which may arise through the use of this medium.
Pentec IT Limited is Registered in Ireland: No 443280 with a registered office at 2 Knightsbrook Court, Dublin Road, Trim, Co. Meath. Company
Directors: Fergal Taheny, Caitriona Ni Riain.


--
http://www.freelists.org/webpage/oracle-l



________________________________
 Confidentiality Note: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact Continuum Performance Systems at {203.245.5000} and delete and destroy the original message and all copies.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 21 2013 - 14:50:48 CET