DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01847 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01847: day of month must be between 1 and last day of month

ORA-01847 is an Oracle error that occurs when you supply an invalid day value during a date conversion or date operation — specifically when the day falls outside the valid range for the given month. For example, entering February 30th or providing a day value of 0 or 32 will trigger this error. It most commonly surfaces in TO_DATE conversions, batch ETL loads, and dynamic SQL date-building logic.

Top 3 Causes

1. Invalid Day Value in TO_DATE Conversion

The most frequent cause is passing a date string with a day that does not exist in the specified month.

-- Triggers ORA-01847
SELECT TO_DATE('2024-02-30', 'YYYY-MM-DD') FROM DUAL;

-- Also triggers ORA-01847
SELECT TO_DATE('2024-11-31', 'YYYY-MM-DD') FROM DUAL;

-- Valid examples
SELECT TO_DATE('2024-02-29', 'YYYY-MM-DD') FROM DUAL; -- 2024 is a leap year
SELECT TO_DATE('2024-11-30', 'YYYY-MM-DD') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. Bad Date Data from External Sources (ETL / Batch Jobs)

When loading data from legacy systems, CSV files, or third-party sources, the source data may contain invalid date strings that Oracle cannot convert.

-- Identify invalid date records in a staging table before loading
CREATE OR REPLACE FUNCTION is_valid_date(p_str VARCHAR2, p_fmt VARCHAR2)
RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_str, p_fmt);
    RETURN 'Y';
EXCEPTION
    WHEN OTHERS THEN RETURN 'N';
END;
/

-- Filter out bad records before INSERT
INSERT INTO target_table (id, event_date)
SELECT id, TO_DATE(date_str, 'YYYY-MM-DD')
FROM   staging_table
WHERE  is_valid_date(date_str, 'YYYY-MM-DD') = 'Y';

-- Log bad records separately
INSERT INTO error_log (id, bad_value, logged_at)
SELECT id, date_str, SYSDATE
FROM   staging_table
WHERE  is_valid_date(date_str, 'YYYY-MM-DD') = 'N';

COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Hardcoded or Arithmetic Date Logic Producing Invalid Dates

Dynamic date calculations — such as adding months manually or hardcoding month-end days — can silently generate invalid dates, especially around leap years and month boundaries.

-- Risky approach: hardcoding month-end days
-- This will fail for February in non-leap years
SELECT TO_DATE('2023-02-' || '31', 'YYYY-MM-DD') FROM DUAL; -- ORA-01847

-- Safe approach: use ADD_MONTHS (handles month-end automatically)
SELECT ADD_MONTHS(TO_DATE('2024-01-31', 'YYYY-MM-DD'), 1) AS next_month FROM DUAL;
-- Returns: 2024-02-29 (automatically adjusted to last day of February)

-- Use LAST_DAY to get the correct end-of-month date
SELECT LAST_DAY(TO_DATE('2023-02-01', 'YYYY-MM-DD')) AS last_day FROM DUAL;
-- Returns: 2023-02-28

-- Safe next-quarter calculation
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) AS next_quarter_start FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Wrap TO_DATE in exception handling to gracefully manage bad input instead of letting it crash your application.
  2. Always use Oracle built-in date functions (ADD_MONTHS, LAST_DAY, TRUNC) instead of manual arithmetic when manipulating dates.
  3. Pre-validate date strings with a utility function (like is_valid_date above) before any bulk load or conversion. 
-- Safe date conversion with fallback to NULL
SELECT CASE
           WHEN is_valid_date(date_str, 'YYYY-MM-DD') = 'Y'
           THEN TO_DATE(date_str, 'YYYY-MM-DD')
           ELSE NULL
       END AS safe_date
FROM   staging_table;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Store dates in DATE or TIMESTAMP columns, never as VARCHAR2. Oracle enforces date validity at insert time when the column type is DATE, eliminating this class of error at the database level.
  • Standardize date handling by building a shared PL/SQL utility package with validated date conversion functions, and enforce its use across all development teams through coding standards.

Related Errors

Error Code Description
ORA-01843 Not a valid month — month value is out of range (1–12)
ORA-01839 Date not valid for month specified — similar to ORA-01847
ORA-01858 Non-numeric character found where numeric expected — format mask mismatch
ORA-01841 Year must be between -4713 and +9999 — invalid year value

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