ORA-01821

ORA-01821

Why does this SQL fail?

SELECT TO_DATE ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL

The example is from the 19c SQL Language Reference, with a slight change. The format is valid, but the thing is that the DATE data type does not support fractions of seconds. Therefore the last part of the format string is unknown to the TO_DATE-function.

One day I lost a few minutes because I stared at the format string too long before I noticed that I was using TO_DATE instead of TO_TIMESTAMP.

The data type for the column I was inserting data into was indeed DATE, but the string I had to convert had these fractions of seconds, which sidetracked me. I was too focused on transforming the entire string and getting the format right that I forgot to look at the bigger picture.