ORA-01821: date format not recognized [TO_DATE]

[Guest]

Hi folks

I wish to have all records where the “time_created” is lesser than
current date minus one hour.

SELECT *
FROM trans
WHERE status = ‘0’
AND TO_DATE (time_created, ‘YYYY/MM/DD-HH24:MI:SS.FF’)

time_created is a “VARCHAR2(23 Byte)” with the following contents:

2010/10/27-07:33:04.229
2010/10/27-07:33:03.135
2010/10/21-12:43:18.371

error: ORA-01821: date format not recognized

cheers Sven

[Anonymous]

The message is exactly what it says. The format ‘YYYY/MM/DD-HH24:MI:SS.FF’ is not recognized. That is because you are using TO_DATE and a date does not have fractions of seconds. Try TO_TIMESTAMP or SUBSTR the TIME_CREATED so as to ignore the fractional seconds.