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