ORA-01843: not a valid month / an invalid month was specified

ORA-01843: An invalid month was specified.

The part of the date string that should be the month could not be read as a month.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-12

ORA-01843 appears when Oracle converts a string to a DATE and the month part does not match the format. Older releases say not a valid month; 23ai, used on this page, says An invalid month was specified.

The message does not show the value or the format, so look at three things: which format is used (explicit or the session's NLS_DATE_FORMAT), the order of day and month, and the session's NLS_DATE_LANGUAGE when month names are involved.

Causes and fixes

  1. A string is put into a DATE column without TO_DATE
  2. Day and month are swapped between the data and the format
  3. English month names while NLS_DATE_LANGUAGE is another language

1A string is put into a DATE column without TO_DATE

INSERT INTO events VALUES (1, '12-11-2026') passes a plain string. Oracle converts it with the session's NLS_DATE_FORMAT, which is DD-MON-RR here. MON expects a month name like NOV, so 11 fails.

With an explicit format, TO_DATE('12-11-2026', 'DD-MM-YYYY'), the row is stored as 2026-11-12 no matter what the session setting is.

Setup
Setuprun as lab_a
CREATE TABLE events (event_id NUMBER PRIMARY KEY, event_date DATE);
OUTPUT
Table created.
Reproducerun as lab_a
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
INSERT INTO events VALUES (1, '12-11-2026');
OUTPUT
Session altered.

INSERT INTO events VALUES (1, '12-11-2026')
                              *
ERROR at line 1:
ORA-01843: An invalid month was specified.
Help: https://docs.oracle.com/error-help/db/ora-01843/
Checkrun as lab_a
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
OUTPUT
VALUE
----------------------------------------------------------------
DD-MON-RR

1 row selected.

The same code can work on one client and fail on another, because each session may have a different NLS_DATE_FORMAT. Always give TO_DATE a format.

After the fixrun as lab_a
INSERT INTO events VALUES (1, TO_DATE('12-11-2026', 'DD-MM-YYYY'));
SELECT event_id, TO_CHAR(event_date, 'YYYY-MM-DD') AS event_date FROM events;
OUTPUT
1 row created.


  EVENT_ID EVENT_DATE
---------- ----------
         1 2026-11-12

1 row selected.

2Day and month are swapped between the data and the format

The data is US style 12/25/2026 (month/day/year), but the format is DD/MM/YYYY. Oracle reads 25 as the month and fails.

VALIDATE_CONVERSION checks both formats without an error: DD/MM/YYYY returns 0, MM/DD/YYYY returns 1. With MM/DD/YYYY the date is stored as 2026-12-25.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE);
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO orders VALUES (1, TO_DATE('12/25/2026', 'DD/MM/YYYY'));
OUTPUT
INSERT INTO orders VALUES (1, TO_DATE('12/25/2026', 'DD/MM/YYYY'))
                                      *
ERROR at line 1:
ORA-01843: An invalid month was specified.
Help: https://docs.oracle.com/error-help/db/ora-01843/
Checkrun as lab_a
SELECT VALIDATE_CONVERSION('12/25/2026' AS DATE, 'DD/MM/YYYY') AS dd_mm, VALIDATE_CONVERSION('12/25/2026' AS DATE, 'MM/DD/YYYY') AS mm_dd FROM dual;
OUTPUT
     DD_MM      MM_DD
---------- ----------
         0          1

1 row selected.
After the fixrun as lab_a
INSERT INTO orders VALUES (1, TO_DATE('12/25/2026', 'MM/DD/YYYY'));
SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD') AS order_date FROM orders;
OUTPUT
1 row created.


  ORDER_ID ORDER_DATE
---------- ----------
         1 2026-12-25

1 row selected.

3English month names while NLS_DATE_LANGUAGE is another language

In a session with NLS_DATE_LANGUAGE = KOREAN, MON means Korean month names. TO_CHAR in that session prints 12-11월-2026, so NOV is not recognized.

The third argument of TO_DATE fixes the language for that one call: TO_DATE('12-NOV-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'). It works even though the session is still KOREAN.

Setup
Setuprun as lab_a
CREATE TABLE logs (log_id NUMBER PRIMARY KEY, log_date DATE);
OUTPUT
Table created.
Reproducerun as lab_a
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
INSERT INTO logs VALUES (1, TO_DATE('12-NOV-2026', 'DD-MON-YYYY'));
OUTPUT
Session altered.

INSERT INTO logs VALUES (1, TO_DATE('12-NOV-2026', 'DD-MON-YYYY'))
                                    *
ERROR at line 1:
ORA-01843: An invalid month was specified.
Help: https://docs.oracle.com/error-help/db/ora-01843/
Checkrun as lab_a
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
SELECT TO_CHAR(DATE '2026-11-12', 'DD-MON-YYYY') AS korean_mon FROM dual;
OUTPUT
Session altered.


KOREAN_MON
-------------
12-11월-2026

1 row selected.

Client tools and Korean or Japanese OS settings often change NLS_DATE_LANGUAGE. Code that parses English month names should always pass the language.

After the fixrun as lab_a
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
INSERT INTO logs VALUES (1, TO_DATE('12-NOV-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'));
SELECT log_id, TO_CHAR(log_date, 'YYYY-MM-DD') AS log_date FROM logs;
OUTPUT
Session altered.


1 row created.


    LOG_ID LOG_DATE
---------- ----------
         1 2026-11-12

1 row selected.

FAQ

TO_DATE works in SQL Developer but fails with ORA-01843 in my application. Why?

Without a format, or with month names, the result depends on the session's NLS_DATE_FORMAT and NLS_DATE_LANGUAGE, which can differ between tools. Check them in NLS_SESSION_PARAMETERS and give TO_DATE an explicit format.

How do I find rows with bad dates without getting ORA-01843?

Use VALIDATE_CONVERSION(col AS DATE, 'format'). It returns 1 if the value converts and 0 if it does not.

Why does 'NOV' fail with ORA-01843?

MON follows NLS_DATE_LANGUAGE. If it is not an English setting, pass 'NLS_DATE_LANGUAGE = AMERICAN' as the third argument of TO_DATE.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors