Find the record causing ORA-01847: day of month must be ... [message #337258] |
Wed, 30 July 2008 05:36  |
Averell
Messages: 17 Registered: November 2006
|
Junior Member |
|
|
Deal all,
I have a table with 2 fields: ID (Primary key - NUMBER), and Date1 (VARCHAR2). In Date1, I store Date-format value (like '31/12/2008'). There are millions of records in my table, some having Date1 not null, some having Date1=null.
For some reason, there are some records having Date1 in incorrect date format. Thus, when I run the following query:
select sum(to_date(Date1,'dd/mm/yyyy')-trunc(sysdate)) from account where Date1 is not null and to_date(Date1,'dd/mm/yyyy') >= trunc(sysdate);
I got the error: ORA-01847: day of month must be between 1 and last day of month
Could you please tell me some way to find all records having the bad date format?
Thank you very much!
|
|
|
|
|
|