- Error code
- ORA-01843
- Product
- Oracle Database
- Versions
- Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)
- Category
- Data Types & Conversion
- Last reviewed
- 24 October 2026
On this page
What it means
ORA-01843 means Oracle was converting text to a date and the part of the text that the format model reads as the month was not a valid month: a number outside 1 to 12, or a month name or abbreviation it does not recognise in the date language in use.
A conversion from text to DATE reads the string piece by piece against a format model: the one you pass to TO_DATE, or the session's NLS_DATE_FORMAT when the conversion is implicit. Oracle's 19c reference lists the valid months as January to December for the format element MONTH and Jan to Dec for MON; for MM it must be 1 to 12. Anything else in the month position raises ORA-01843. The message never shows the value or the format, so you have to compare them yourself.
Month names are read in the date language (NLS_DATE_LANGUAGE), so 24-OCT-2026 converts in an English session but not in one whose date language names the month differently. Day and month swapped (24/10/2026 read as MM/DD/YYYY) is the other everyday cause. When the text does not line up with the format in another way, Oracle raises ORA-01861 or a related format error instead.
Common causes
- Day and month are swapped: a UK or European style date such as 24/10/2026 is converted with an MM/DD/YYYY format.
- A month name or abbreviation (OCT, October) is converted in a session whose NLS_DATE_LANGUAGE uses different month names.
- The format model has MON or MONTH where the text has a month number, or MM where the text has a month name.
- An implicit conversion uses the session NLS_DATE_FORMAT, which differs between the developer's tool and the application server.
- Imported data contains a bad month (00, 13) or a value in a different layout from the rest of the file.
Diagnose it
Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.
Check the session date format and language
Implicit conversions use these settings, and they depend on the client, so check them from the same session or tool that raises the error.
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE', 'NLS_TERRITORY');For example NLS_DATE_FORMAT = DD-MON-RR and NLS_DATE_LANGUAGE = AMERICAN. A string must match that format and language to convert implicitly.
Find the values that do not convert with your format
VALIDATE_CONVERSION returns 0 for each value that fails with the given format model, without raising an error.
SELECT import_id, order_date_text
FROM shop.order_imports
WHERE order_date_text IS NOT NULL
AND VALIDATE_CONVERSION(order_date_text AS DATE, 'DD/MM/YYYY') = 0;The rows whose text cannot be read as DD/MM/YYYY. If most rows appear, the format is wrong; if a few do, the data is.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Pass the format model that matches the text
Read-onlyAlways convert with an explicit format model that describes the text exactly, with DD and MM in the right order.
SELECT TO_DATE('24/10/2026', 'DD/MM/YYYY') AS order_date
FROM dual;Fix the date language for month names
Read-onlyTO_DATE accepts an nlsparam argument that sets the language of the text for that call only, so month names convert whatever the session language is.
SELECT TO_DATE('24-OCT-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = ENGLISH') AS order_date
FROM dual;Use a DATE literal for constants
Read-onlyAn ANSI date literal is always written YYYY-MM-DD and does not depend on NLS settings.
SELECT order_id
FROM shop.orders
WHERE order_date >= DATE '2026-10-01';Load messy text with DEFAULT ... ON CONVERSION ERROR
Read-onlyTO_DATE can return a default (here NULL) instead of failing, so you can load the good rows and review the rest separately.
SELECT import_id,
TO_DATE(order_date_text DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/YYYY') AS order_date
FROM shop.order_imports;Examples
Day and month in the wrong order
Not reproduced here: no Oracle Database is installed for this site. The result shows the message as worded in Oracle's current error reference; Oracle Database 19c and 21c print "ORA-01843: not a valid month". MM reads 24, which is not a month.
SELECT TO_DATE('24/10/2026', 'MM/DD/YYYY') AS order_date
FROM dual;ORA-01843: An invalid month was specified.
An English month abbreviation in a session with another date language
Not reproduced here: no Oracle Database is installed for this site. The message format is taken from Oracle's error reference. Month abbreviations are read in the session's date language, and in German the abbreviation for October is not OCT; passing 'NLS_DATE_LANGUAGE = ENGLISH' to TO_DATE fixes it.
ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';
SELECT TO_DATE('24-OCT-2026', 'DD-MON-YYYY') AS order_date
FROM dual;ORA-01843: An invalid month was specified.
How to prevent it
Never rely on implicit date conversion. Use DATE 'YYYY-MM-DD' literals for constants, TO_DATE with an explicit format model (and an nlsparam when the text has month names) for text, and real date bind variables from the application instead of strings.
Exchange dates in files and APIs in an unambiguous form such as YYYY-MM-DD, and validate imported dates with VALIDATE_CONVERSION before converting them.
Common questions
How do I fix ORA-01843 not a valid month?
Compare the text with the format model: make sure MM, MON or MONTH sits where the month is in the text and that day and month are not swapped. Pass the format to TO_DATE explicitly, and add NLS_DATE_LANGUAGE in the call when the text contains month names.
Why does it work in SQL Developer but fail in my application?
Implicit conversions use the session's NLS_DATE_FORMAT and NLS_DATE_LANGUAGE, which each client sets on its own. Query NLS_SESSION_PARAMETERS from both and convert with an explicit format so the session settings no longer matter.
What is the difference between ORA-01843 and ORA-01861?
ORA-01843 means the month part of the text was read but is not a valid month. ORA-01861 means the text as a whole does not match the format string, for example a literal character in the text that the format does not have.
Learn more on CodeWithSQL
Official documentation
- ORA-01843 (Oracle Database Error Messages)
- TO_DATE (Oracle SQL Language Reference 19c)
- Format Models (Oracle SQL Language Reference 19c)
- NLS_DATE_LANGUAGE (Oracle Database Reference 19c)
Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.