DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01839 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01839: date not valid for month specified

ORA-01839 is an Oracle error that occurs when you try to use a date value that does not exist for the specified month, such as February 30th or April 31st. Oracle's date validation strictly enforces the actual number of days in each month, including leap year rules for February. This error commonly surfaces during TO_DATE conversions, date arithmetic operations, or bulk data load processes.

Top 3 Causes and Solutions

Cause 1: Invalid Date String in TO_DATE

Passing a date string with a day value that exceeds the valid range for the given month is the most common trigger.

-- This will throw ORA-01839
SELECT TO_DATE('2024-02-30', 'YYYY-MM-DD') FROM DUAL;

-- Fix: Create a 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
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_str, p_fmt);
    RETURN v_date;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- Return NULL instead of raising error
END safe_to_date;
/

-- Safe usage
SELECT safe_to_date('2024-02-30') AS result FROM DUAL; -- Returns NULL
SELECT safe_to_date('2024-02-29') AS result FROM DUAL; -- Returns 2024-02-29 (leap year)
Enter fullscreen mode Exit fullscreen mode

Cause 2: Direct Date Arithmetic Producing Invalid Dates

Adding or subtracting days directly to a date can produce a date that falls outside a month's valid range when not handled carefully.

-- Potentially confusing result (not ORA-01839 itself, but wrong intent)
SELECT TO_DATE('2024-01-31', 'YYYY-MM-DD') + 30 AS result FROM DUAL;
-- Result: 2024-03-01 (skips Feb boundary)

-- SAFE: Use ADD_MONTHS — it automatically adjusts to the last valid day
SELECT ADD_MONTHS(TO_DATE('2024-01-31', 'YYYY-MM-DD'), 1) AS result FROM DUAL;
-- Result: 2024-02-29 (auto-adjusted to end of February in leap year)

-- Get the last day of any month safely
SELECT LAST_DAY(TO_DATE('2024-02-01', 'YYYY-MM-DD')) AS last_day FROM DUAL;
-- Result: 2024-02-29

-- Safe multi-month projection
SELECT
    ADD_MONTHS(TO_DATE('2024-01-31','YYYY-MM-DD'), 1) AS feb,
    ADD_MONTHS(TO_DATE('2024-01-31','YYYY-MM-DD'), 2) AS mar,
    ADD_MONTHS(TO_DATE('2024-01-31','YYYY-MM-DD'), 3) AS apr
FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Bad Date Data During ETL / Bulk Load

Source systems may contain logically invalid dates due to poor validation, mismatched date formats (MM/DD vs DD/MM), or dummy placeholder values.

-- Stage raw data as VARCHAR2 first
CREATE TABLE stg_sales_raw (
    sale_id     NUMBER,
    sale_date   VARCHAR2(20), -- Store as string initially
    amount      NUMBER
);

-- Identify invalid dates before loading
SELECT
    sale_id,
    sale_date,
    CASE
        WHEN safe_to_date(sale_date, 'YYYY-MM-DD') IS NULL
        THEN 'INVALID'
        ELSE 'VALID'
    END AS date_check
FROM stg_sales_raw;

-- Load only valid records into the actual table
INSERT INTO sales (sale_id, sale_date, amount)
SELECT sale_id, TO_DATE(sale_date, 'YYYY-MM-DD'), amount
FROM stg_sales_raw
WHERE safe_to_date(sale_date, 'YYYY-MM-DD') IS NOT NULL;

-- Capture invalid records for review
INSERT INTO stg_sales_error (sale_id, sale_date, amount, error_msg)
SELECT sale_id, sale_date, amount, 'ORA-01839: Invalid date for month'
FROM stg_sales_raw
WHERE safe_to_date(sale_date, 'YYYY-MM-DD') IS NULL;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- 1. Validate before converting
SELECT CASE
           WHEN REGEXP_LIKE(date_col, '^\d{4}-\d{2}-\d{2}$')
           THEN safe_to_date(date_col, 'YYYY-MM-DD')
           ELSE NULL
       END AS clean_date
FROM your_table;

-- 2. Use LAST_DAY to cap dates at month end
SELECT LEAST(input_date, LAST_DAY(TRUNC(input_date, 'MM'))) AS safe_date
FROM your_table;

-- 3. Always prefer ADD_MONTHS over manual day addition for monthly intervals
-- BAD:  date_col + 30
-- GOOD: ADD_MONTHS(date_col, 1)
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always use explicit format strings and set NLS_DATE_FORMAT clearly.
Never rely on implicit date conversion. Set the session-level format explicitly and always pass a format mask to TO_DATE.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- Always explicit — never rely on implicit conversion like:
-- SELECT TO_DATE('30-FEB-23') FROM DUAL; -- dangerous
-- Use instead:
SELECT TO_DATE('2023-02-28', 'YYYY-MM-DD') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. Use Oracle's native DATE type for all date columns — avoid storing dates as VARCHAR2.
When columns are defined as DATE, Oracle enforces validity automatically at insert time, preventing bad data from ever entering the database. Reserve VARCHAR2 date storage only when interfacing with external systems, and always validate before conversion.

Related Oracle Errors

  • ORA-01840 – Input value too short for the date format mask
  • ORA-01841 – Year value must be between -4713 and +9999 and cannot be 0
  • ORA-01843 – Invalid month value (must be 01–12 or valid month name)
  • ORA-01847 – Day value out of range (less than 1 or greater than 31)
  • ORA-01861 – Literal does not match the format string (format mask mismatch)

📖 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)