Having a problem with the TO_DATE function in Oracle.

[hydroraven]

Hello all,

I am trying to use the TO_DATE function to convert a string to a date so that I can perform periodic maintenance on a table based upon the date.

Here is the SQL statement.

 
SELECT * FROM APPROVED
WHERE TO_DATE(CREATION_TIME, 'DY MON DD HH24:MI:SS TZD YYYY') > SYSDATE-30
 
 

Here is the actual string value 'Mon Dec 10 15:20:25 EDT 2007'

When I execute the query I get the following message "ORA-01821: date format not recognized".

I thought since I was specifying the format Oracle would be able to make sense of my date string. Does anyone see any flaws or able to offer some pointers?

Any help is appreciated.

[debasisdas]

What is the data type of CREATION_TIME field.

try to use

 
SELECT * FROM APPROVED
WHERE TO_DATE(CREATION_TIME, 'DD-MON-YY HH12:MI:SS') > SYSDATE-30
 
 

[hydroraven]

What is the data type of CREATION_TIME field.

try to use

 
SELECT * FROM APPROVED
WHERE TO_DATE(CREATION_TIME, 'DD-MON-YY HH12:MI:SS') > SYSDATE-30
 
 

The datatype of the CREATION_TIME is VARCHAR2. I tried the code you provided but it did not work.

The error message I get is ORA-01858: a non-numeric character was found where a numeric was expected.

Hope this helps. Thanks

[amitpatel66]

Hello all,

I am trying to use the TO_DATE function to convert a string to a date so that I can perform periodic maintenance on a table based upon the date.

Here is the SQL statement.

 
SELECT * FROM APPROVED
WHERE TO_DATE(CREATION_TIME, 'DY MON DD HH24:MI:SS TZD YYYY') > SYSDATE-30
 
 

Here is the actual string value 'Mon Dec 10 15:20:25 EDT 2007'

When I execute the query I get the following message "ORA-01821: date format not recognized".

I thought since I was specifying the format Oracle would be able to make sense of my date string. Does anyone see any flaws or able to offer some pointers?

Any help is appreciated.

Could you please provide few more values of creation_date column?
And also please clarify what does that EDT refers to?

I tried this and it works:

SQL> alter session set nls_Date_format='Dy Mon DD HH24:MI:SS RRRR'
  2  /
 
Session altered.
 
SQL> select TO_DATE('Mon Dec 10 15:20:25 2007') from dual
  2  /
 
TO_DATE('MONDEC1015:20:2'
------------------------
Mon Dec 10 15:20:25 2007
 
-- Now again if I alter my date format, it will error out as shown below:
 
SQL> alter session set nls_date_format='DD-MON-RRRR'
  2  /
 
Session altered.
 
SQL> select TO_DATE('Mon Dec 10 15:20:25 2007') from dual
  2  /
select TO_DATE('Mon Dec 10 15:20:25 2007') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
 
 

[amitpatel66]

You need not alter the session also. Try this:

SQL> ed
Wrote file afiedt.buf
 
  1* select TO_DATE('Mon Dec 10 15:20:25 2007','Dy Mon DD HH24:MI:SS RRRR') from dual
SQL> /
 
TO_DATE('MO'
-----------
10-DEC-2007
 
SQL>