Hi all 1

[aspiringprog]

I have dates stored in this format

Jan 1 2003 12:00:00:000AM

I need to convert this into mm/dd/yyyy format so that I can compare with dates from another table. When I try using the
following function

TO_DATE(SUBSTR(DT,1,10),'MON DY YYYY')

I get the following error

ORA-01835: day of week conflicts with Julian date

Can you please tell me how to get rid of this error?

Thanks

 

[lewisp]

Use DD instead of DY.

 

[aspiringprog]

hI..
I used dd and now i get this message

ORA-01858: a non-numeric character was found where a numeric was expected

Any suggestions?

 

[lewisp]

It looks like some of the 'dates' in the DT column are not of the same format.

 

[aspiringprog]

This 'DATE' column is stored as a CHAR datatype.

There are more than 500K records...how to find the few or maybe one that is causing the problem?

 

[lewisp]

Run this code to identify your invalid dates:

set serveroutput on size 1000000

DECLARE
  l_chdate  your_table.DT%TYPE;
  l_actdate DATE;
--
  CURSOR cur_dates IS
    SELECT dt
    FROM   your_table;
BEGIN
  FOR c_test IN cur_dates
  LOOP
    BEGIN
      l_chdate := c_test.DT;
      l_actdate := TO_DATE(SUBSTR(c_test.DT,1,10),'MON DD YYYY');
    EXCEPTION
      WHEN OTHERS
      THEN
        Dbms_Output.Put_Line(l_chdate || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

 

[aspiringprog]

Hi...

Thanks for the help..but unfortunately the function does not return any values..so maybe there is no row in their with improper format.

I am wondering if I should try using a decode statement to replace the spaces with '/' and then convert using to_date

 

[aspiringprog]

Hi there,

I think I found the problem...

A set of dates have feb 29th listed in them.

for some reason Oracle is treating this as invalid date for given month even though the year is a leap yr.

any suggestions?

thanks a lot

 

[lewisp]

Can you show the data contained in the rows?

 

[lewisp]

BJCooperIT has spotted in another thread your problem: You need to Substr(DT,1,11) to get the right date format.