Re: TO_DATE ignores

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Thu, 21 Feb 2013 13:40:28 +0000
Message-ID: <CAOuMUT7nrJJMDjGuXT1BPjdXUiguisefKNF0Vmv=0Lczg8=6yQ_at_mail.gmail.com>

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

Received on Thu Feb 21 2013 - 14:40:28 CET