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
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
CREATE TABLE events (event_id NUMBER PRIMARY KEY, event_date DATE);Table created.ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
INSERT INTO events VALUES (1, '12-11-2026');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/SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';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.
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;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
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE);Table created.INSERT INTO orders VALUES (1, TO_DATE('12/25/2026', 'DD/MM/YYYY'));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/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; DD_MM MM_DD
---------- ----------
0 1
1 row selected.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;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
CREATE TABLE logs (log_id NUMBER PRIMARY KEY, log_date DATE);Table created.ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
INSERT INTO logs VALUES (1, TO_DATE('12-NOV-2026', 'DD-MON-YYYY'));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/ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
SELECT TO_CHAR(DATE '2026-11-12', 'DD-MON-YYYY') AS korean_mon FROM dual;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.
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;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