ORA-01839: Date Not Valid for Month - Fix Date Errors

ORA-01839: Date Not Valid for Month Specified

Error Overview

Error Text: ORA-01839: date not valid for month specified

The ORA-01839 error occurs when a date construction or conversion produces a day number that does not exist in the specified month. Common examples are February 30, April 31, or September 31. Oracle raises this error during TO_DATE conversions, date literal evaluations, and date arithmetic that overflows a month boundary. Unlike some date errors, ORA-01839 always indicates that the raw date value is logically impossible — the month simply does not have that many days.

Common Causes

1. Invalid Day for Month in TO_DATE

• User input providing February 29 in a non-leap year or February 30 in any year
• Hard-coded date strings with incorrect day values
• Bulk data loads containing malformed date fields from source systems

2. Date Arithmetic Producing Invalid Dates

• Adding months to a date where the resulting month has fewer days
• ADD_MONTHS called on March 31 adding 1 month yields April 31 — Oracle actually handles this gracefully, but custom arithmetic does not
• Storing calculated end-of-month dates incorrectly as character strings

3. NLS Format Mask Mismatch

• Data formatted as DD/MM/YYYY but parsed with MM/DD/YYYY mask
• Two-digit year ambiguity causing wrong century assignment
• Missing RR or YYYY in the format causing year misinterpretation

4. Leap Year Handling Errors

• Application treating all years as having Feb 29
• Date range logic that adds one day to Feb 28 assuming Feb 29 always exists
• Legacy code written without leap year validation

5. End-of-Month Boundary Conditions

• Reports parameterized to always end on the 31st regardless of month
• ETL processes generating synthetic last-day values without using LAST_DAY()
• Scheduler jobs setting next-run-date to the 30th or 31st of every month

Diagnostic Queries

Identify Invalid Dates in a Column

-- Find rows where a character column contains invalid dates
SELECT rowid, date_col_raw
FROM staging_table
WHERE date_col_raw IS NOT NULL
  AND (
    -- Day exceeds month maximum
    TO_NUMBER(SUBSTR(date_col_raw, 1, 2)) >
    TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('01' || SUBSTR(date_col_raw, 3), 'DDMMYYYY')), 'DD'))
  );

-- Alternative: use a validation function
CREATE OR REPLACE FUNCTION is_valid_date(
  p_date_str VARCHAR2,
  p_format   VARCHAR2 DEFAULT 'DD-MON-YYYY'
) RETURN VARCHAR2 AS
  v_date DATE;
BEGIN
  v_date := TO_DATE(p_date_str, p_format);
  RETURN 'VALID';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'INVALID: ' || SQLERRM;
END;
/

-- Apply validation across a staging table
SELECT date_col_raw, is_valid_date(date_col_raw, 'DD/MM/YYYY') AS validity
FROM staging_table
WHERE is_valid_date(date_col_raw, 'DD/MM/YYYY') != 'VALID';

Check Leap Year Status

-- Determine if a specific year is a leap year
SELECT
  year_val,
  CASE
    WHEN MOD(year_val, 400) = 0 THEN 'Leap Year'
    WHEN MOD(year_val, 100) = 0 THEN 'Not Leap Year'
    WHEN MOD(year_val, 4)   = 0 THEN 'Leap Year'
    ELSE                              'Not Leap Year'
  END AS leap_status
FROM (SELECT 2024 AS year_val FROM dual
      UNION ALL SELECT 2100 FROM dual
      UNION ALL SELECT 2000 FROM dual);

-- Find all Feb 29 dates in a range
SELECT TO_DATE('29-02-' || TO_CHAR(yr), 'DD-MM-YYYY') AS feb29
FROM (
  SELECT LEVEL + 1999 AS yr
  FROM dual
  CONNECT BY LEVEL <= 100
)
WHERE MOD(yr, 400) = 0
   OR (MOD(yr, 4) = 0 AND MOD(yr, 100) != 0);

Find Sessions Hitting the Error

-- Review recent date conversion errors in ASH
SELECT
  sample_time,
  session_id,
  sql_id,
  program,
  module
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 1
ORDER BY sample_time DESC
FETCH FIRST 30 ROWS ONLY;

Step-by-Step Resolution

1. Use LAST_DAY to Avoid Month Overflow

When calculating end-of-month dates, always use the built-in LAST_DAY() function:

-- BAD: hard-coding the 31st as month end
SELECT TO_DATE('31' || TO_CHAR(SYSDATE, '/MM/YYYY'), 'DD/MM/YYYY')
FROM dual;  -- Fails for April, June, September, November

-- GOOD: use LAST_DAY
SELECT LAST_DAY(TRUNC(SYSDATE, 'MM')) AS month_end FROM dual;

-- Get the last day of any given month
SELECT LAST_DAY(TO_DATE('01/06/2024', 'DD/MM/YYYY')) AS june_end FROM dual;

-- Date range for an entire month
SELECT
  TRUNC(SYSDATE, 'MM')          AS month_start,
  LAST_DAY(TRUNC(SYSDATE, 'MM')) AS month_end
FROM dual;

2. Validate User Input Before TO_DATE

-- Validate a day/month combination before converting
CREATE OR REPLACE FUNCTION safe_to_date(
  p_day    NUMBER,
  p_month  NUMBER,
  p_year   NUMBER
) RETURN DATE AS
  v_last_day NUMBER;
  v_date     DATE;
BEGIN
  -- Get the last valid day for the given month/year
  v_date     := TO_DATE('01/' || LPAD(p_month, 2, '0') || '/' || p_year, 'DD/MM/YYYY');
  v_last_day := TO_NUMBER(TO_CHAR(LAST_DAY(v_date), 'DD'));

  IF p_day < 1 OR p_day > v_last_day THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Day ' || p_day || ' is not valid for month ' || p_month ||
      ' in year ' || p_year || '. Maximum day is ' || v_last_day || '.');
  END IF;

  RETURN TO_DATE(
    LPAD(p_day, 2, '0') || '/' || LPAD(p_month, 2, '0') || '/' || p_year,
    'DD/MM/YYYY'
  );
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 'Invalid date: ' || SQLERRM);
END;
/

-- Test
SELECT safe_to_date(29, 2, 2024) AS valid_date FROM dual;   -- 2024 is a leap year
SELECT safe_to_date(29, 2, 2023) AS invalid_date FROM dual; -- Should raise error

3. Fix Bulk Data with Invalid Dates

When cleaning staging data with bad dates:

-- Option 1: Set invalid dates to NULL
UPDATE staging_table
SET event_date = NULL
WHERE event_date_raw IS NOT NULL
  AND is_valid_date(event_date_raw, 'DD/MM/YYYY') != 'VALID';

-- Option 2: Clamp to last valid day of month
UPDATE staging_table
SET event_date_raw =
  LPAD(
    TO_CHAR(
      LAST_DAY(TO_DATE('01/' || SUBSTR(event_date_raw, 4, 7), 'MM/YYYY')),
      'DD'
    ), 2, '0'
  ) || SUBSTR(event_date_raw, 3)
WHERE is_valid_date(event_date_raw, 'DD/MM/YYYY') != 'VALID';
COMMIT;

4. Handle ADD_MONTHS and Month-End Arithmetic

-- Oracle's ADD_MONTHS handles month-end correctly
SELECT ADD_MONTHS(DATE '2024-01-31', 1) FROM dual;  -- Returns 2024-02-29 (leap year)
SELECT ADD_MONTHS(DATE '2023-01-31', 1) FROM dual;  -- Returns 2023-02-28

-- BAD: manual month arithmetic
SELECT DATE '2024-01-31' + 31 FROM dual;  -- Returns March 2, not Feb end

-- GOOD: use ADD_MONTHS for monthly increments
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), n.lvl) AS month_start
FROM (SELECT LEVEL - 1 AS lvl FROM dual CONNECT BY LEVEL <= 12) n;

5. Fix NLS Format Mask Issues

-- Always specify the full 4-digit year format
-- BAD: ambiguous 2-digit year
SELECT TO_DATE('29/02/24', 'DD/MM/YY') FROM dual;

-- GOOD: explicit 4-digit year
SELECT TO_DATE('29/02/2024', 'DD/MM/YYYY') FROM dual;

-- Verify NLS date format in use
SHOW PARAMETER nls_date_format;

-- Set explicit format to avoid NLS dependency
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY';

Prevention Strategies

1. Always Use Oracle Date Functions

-- Use LAST_DAY, ADD_MONTHS, TRUNC, and NEXT_DAY for all date arithmetic
-- These functions respect month boundaries automatically

-- Month range generation
SELECT
  ADD_MONTHS(DATE '2024-01-01', LEVEL - 1) AS month_start,
  LAST_DAY(ADD_MONTHS(DATE '2024-01-01', LEVEL - 1)) AS month_end
FROM dual
CONNECT BY LEVEL <= 12;

2. Add Check Constraints on Date Columns

-- Enforce valid date ranges at the database level
ALTER TABLE my_table
ADD CONSTRAINT chk_event_date_range
CHECK (event_date BETWEEN DATE '2000-01-01' AND DATE '2099-12-31');

-- Store dates as DATE type, never as VARCHAR2
-- This eliminates ORA-01839 entirely for persisted values
CREATE TABLE events (
  event_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  event_name VARCHAR2(200) NOT NULL,
  event_date DATE NOT NULL,
  CONSTRAINT pk_events PRIMARY KEY (event_id)
);

3. Validate at the Application Layer

• Parse and validate all date inputs at the API or UI layer before sending to Oracle
• Use ISO 8601 format (YYYY-MM-DD) for all date interchange to reduce format ambiguity
• Implement leap year checks in application code when constructing date parameters

4. Test with Month-Boundary Dates

• Include test cases for: Feb 28, Feb 29 (leap/non-leap), Apr 30, Jun 30, Sep 30, Nov 30, Dec 31
• Test with year boundaries: Dec 31 + 1 day, Jan 1 - 1 day
• Test ADD_MONTHS from Jan 31, Mar 31 across February

Related Errors

• ORA-01843 - Not a valid month
• ORA-01830 - Date format picture ends before converting
• ORA-01861 - Literal does not match format string
• ORA-01722 - Invalid number