DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01843 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01843: Not a Valid Month — Causes, Fixes, and Prevention

ORA-01843 is one of the most common date-related errors in Oracle databases, occurring when the database cannot interpret a month value during an implicit or explicit date conversion. This typically happens when the month portion of a date string falls outside the valid range (1–12), contains an unrecognizable string, or when the date format mask doesn't match the input string. If left unaddressed, this error can silently break batch jobs, ETL pipelines, and application insert/update operations.

Top 3 Causes

Cause 1: NLS_DATE_FORMAT Mismatch (Implicit Conversion Failure)

When Oracle tries to implicitly convert a string to a DATE using the session's NLS_DATE_FORMAT, any mismatch between the format and the actual string will trigger ORA-01843.

-- Check current session NLS_DATE_FORMAT
SELECT VALUE
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';

-- This fails if NLS_DATE_FORMAT is 'DD-MON-RR' but input is 'YYYY-MM-DD'
-- ORA-01843: not a valid month
INSERT INTO orders (order_date) VALUES ('2024-07-15');

-- Fix: Set NLS explicitly at session level
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
INSERT INTO orders (order_date) VALUES ('2024-07-15');  -- Now works
Enter fullscreen mode Exit fullscreen mode

Cause 2: Invalid Month Value in Input Data

Month values outside the range 1–12 — such as 0, 13, or garbled strings — will always raise ORA-01843. This is especially common during data migrations or integrations with legacy systems.

-- This will raise ORA-01843 (month = 13)
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD') FROM DUAL;

-- Detect bad month values in a staging table
SELECT raw_date_col
FROM staging_table
WHERE NOT REGEXP_LIKE(raw_date_col,
      '^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$');

-- Safe insert using CASE to filter bad data
INSERT INTO target_table (id, reg_date)
SELECT id,
       CASE
           WHEN REGEXP_LIKE(raw_date_col,
                '^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$')
           THEN TO_DATE(raw_date_col, 'YYYY-MM-DD')
           ELSE NULL
       END
FROM staging_table;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Missing or Incorrect Format Mask in TO_DATE()

Calling TO_DATE() without an explicit format mask forces Oracle to rely on NLS_DATE_FORMAT. If the format doesn't align, the parser misreads the month position and throws ORA-01843.

-- BAD: No format mask — depends on NLS_DATE_FORMAT
SELECT TO_DATE('2024-07-15') FROM DUAL;  -- May fail

-- GOOD: Always specify the format mask explicitly
SELECT TO_DATE('2024-07-15', 'YYYY-MM-DD')        FROM DUAL;
SELECT TO_DATE('15/07/2024', 'DD/MM/YYYY')         FROM DUAL;
SELECT TO_DATE('20240715',   'YYYYMMDD')            FROM DUAL;

-- Oracle 12c+: Handle conversion errors gracefully
SELECT TO_DATE('2024-13-01' DEFAULT NULL ON CONVERSION ERROR,
               'YYYY-MM-DD') AS safe_date
FROM DUAL;
-- Result: NULL (no exception raised)
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- 1. Identify the problematic rows before bulk operations
SELECT raw_date_col
FROM staging_table
WHERE REGEXP_LIKE(raw_date_col, '^\d{4}-\d{2}-\d{2}$')
  AND TO_NUMBER(SUBSTR(raw_date_col, 6, 2)) NOT BETWEEN 1 AND 12;

-- 2. Create a reusable safe conversion function
CREATE OR REPLACE FUNCTION safe_to_date(
    p_str IN VARCHAR2,
    p_fmt IN VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN DATE IS
BEGIN
    RETURN TO_DATE(p_str, p_fmt);
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
/

-- Usage
SELECT safe_to_date('2024-13-01') AS bad_date,   -- returns NULL
       safe_to_date('2024-07-15') AS good_date    -- returns 2024-07-15
FROM DUAL;

-- 3. Set consistent NLS on every session via logon trigger (DBA required)
CREATE OR REPLACE TRIGGER set_nls_on_logon
AFTER LOGON ON DATABASE
BEGIN
    EXECUTE IMMEDIATE
        'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always use explicit format masks. Never rely on implicit date conversion or NLS_DATE_FORMAT defaults. Every TO_DATE() and TO_TIMESTAMP() call in your codebase should include a hardcoded format mask. Enforce this through code review checklists or static analysis tools.

Validate date data at the boundary. Before any bulk insert or migration, run a regex-based pre-check to identify rows with invalid month values. If you must store dates as VARCHAR2 in a legacy schema, add a BEFORE INSERT OR UPDATE trigger that calls TO_DATE() and raises a meaningful application error on failure. Preferably, always store date values in proper DATE or TIMESTAMP columns.

Related Oracle Errors

Error Code Description
ORA-01861 Literal does not match format string
ORA-01847 Day of month must be between 1 and 31
ORA-01841 Year must be between -4713 and 9999
ORA-01858 Non-numeric character found where numeric expected
ORA-01840 Input value not long enough for date format

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)