Home » SQL & PL/SQL » SQL & PL/SQL » Find the record causing ORA-01847: day of month must be ...
Find the record causing ORA-01847: day of month must be ... [message #337258] Wed, 30 July 2008 05:36 Go to next message
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!
Re: Find the record causing ORA-01847: day of month must be ... [message #337262 is a reply to message #337258] Wed, 30 July 2008 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Create a function that tries to convert a string to a date and return 0 if it can't, then search all rows for which this function returns 0.

Regards
Michel
Re: Find the record causing ORA-01847: day of month must be ... [message #337279 is a reply to message #337258] Wed, 30 July 2008 07:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Just another fine example of what happens when you store DATEs in a VARCHAR2 column. Will people ever learn?
Re: Find the record causing ORA-01847: day of month must be ... [message #337282 is a reply to message #337279] Wed, 30 July 2008 07:42 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Will instructors learn? Wink

Regards
Michel
Previous Topic: How many rows and columns can be designed in a table..
Next Topic: how to delete rows from the table.
Goto Forum:
  


Current Time: Mon Feb 17 14:59:27 CST 2025