Error ORA-01821 "date format not recognized"

2
votes

I am trying to alter the session date format to "YYYY-MM-DD HH:MI:SS:FF6"

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS:FF6'

is what I'm trying to use and it's giving me the error ORA-01821 "date format not recognized".

What am I doing wrong?

1
oracle

1 Answers

7
votes

The Oracle DATE type only has second-level precision (as opposed to, say, microsecond-level precision), so date formats don't support the FF notation. If you really want microseconds appended, you can write:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS:"000000"';

Alternatively, maybe you actually wanted to set the default timestamp format? If so:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS:FF6';