ORA-01821: date format not recognized [TO_DATE]

Hi folks

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

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:


error: ORA-01821: date format not recognized

cheers Sven

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.