How to Resolve ORA-01821: date format not recognized

ORA-01821

Tried to display a datetime into a readable format by TO_CHAR function, but it failed with ORA-01821.

SQL> set heading off;
SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M. TZ') from dual;
select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M. TZ') from dual
                        *
ERROR at line 1:
ORA-01821: date format not recognized

The position that error points out cannot provide any clue to know which datetime format element is invalid. So we have to inspect elements one by one.

OK, we saw TZ which is not a legal datetime format element, but TZR is, which represents time zone region information.

So we change it and issue the statement again.

SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M. TZR') from dual;
select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M. TZR') from dual
                        *
ERROR at line 1:
ORA-01821: date format not recognized

Now what? We saw the same error.

ORA-01821 means that at least one illegal element is found in the format string, or the datetime value does not contain specific information you want to know in the format string.

Solution

In this case, SYSDATE does not contain time zone region (TZR) information that you want to know. SYSTIMESTAMP does.

Now, you have 2 choices to solve it. One is to remove any time zone element from the format string, the other is to use a TIMESTAMP, not a DATE column.

In this case, we have 2 way to solve it.

1. Use SYSTIMESTAMP
2. Add SESSIONTIMEZONE

Use SYSTIMESTAMP

We use SYSTIMESTAMP instead of SYSDATE.

SQL> select TO_CHAR(systimestamp, 'Month dd, YYYY, HH:MI A.M. TZR') from dual;

April     19, 2023, 12:52 A.M. -04:00

In the above example, we derive the timestamp from SYSTIMESTAMP, an Oracle built-in function for getting the current timestamp.

Add SESSIONTIMEZONE

If you insist to use a DATE column, you can add SESSIONTIMEZONE to it, which is a function displays the time zone information of this session.

SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M. ') || sessiontimezone from dual;

April     19, 2023, 12:58 A.M. -04:00

The bottom line is, a DATE column does not have time zone information.