DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00975 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00975: date + date not allowed — Cause & Fix

ORA-00975 is thrown by Oracle when you attempt to add two DATE values together using the + operator. Unlike subtraction (which returns the number of days between two dates), adding two dates together has no logical meaning, so Oracle explicitly prohibits it. This error is especially common when migrating SQL from other databases or when implementing date midpoint calculations incorrectly.

Top 3 Causes

1. Directly Adding Two DATE Columns

The most common cause is attempting to use + between two DATE-type columns or literals.

-- WRONG: Triggers ORA-00975
SELECT order_date + ship_date
FROM orders;

-- CORRECT: Add a number (days) to a DATE
SELECT order_date + 7 AS estimated_delivery
FROM orders;

-- CORRECT: Subtract two dates to get number of days (this IS allowed)
SELECT ship_date - order_date AS days_to_ship
FROM orders;
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Midpoint / Average Date Calculation

Developers sometimes calculate a midpoint between two dates using (date1 + date2) / 2, which is mathematically intuitive but invalid in Oracle SQL.

-- WRONG: Triggers ORA-00975
SELECT (start_date + end_date) / 2 AS midpoint
FROM project_schedule;

-- CORRECT: Use date subtraction to get the interval, then add to start_date
SELECT start_date + (end_date - start_date) / 2 AS midpoint
FROM project_schedule;

-- Runnable example
SELECT
    TO_DATE('2024-01-01','YYYY-MM-DD') +
    (TO_DATE('2024-12-31','YYYY-MM-DD') - TO_DATE('2024-01-01','YYYY-MM-DD')) / 2
        AS midpoint_date
FROM dual;
Enter fullscreen mode Exit fullscreen mode

3. Implicit Type Conversion Resulting in DATE + DATE

When a value is silently converted to a DATE by Oracle's implicit conversion rules, and then added to another DATE, ORA-00975 can appear in unexpected places.

-- WRONG: Both sides resolve to DATE, causing ORA-00975
SELECT TO_DATE('2024-01-01','YYYY-MM-DD') + TO_DATE('2024-06-01','YYYY-MM-DD')
FROM dual;

-- CORRECT: Use INTERVAL for clean, readable date arithmetic
SELECT TO_DATE('2024-01-01','YYYY-MM-DD') + INTERVAL '6' MONTH AS result
FROM dual;

-- CORRECT: Use ADD_MONTHS for month-based calculations
SELECT ADD_MONTHS(TO_DATE('2024-01-01','YYYY-MM-DD'), 6) AS result
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Remember Oracle's core date arithmetic rules:

-- DATE + NUMBER = DATE  (OK)
SELECT SYSDATE + 30 AS future_date FROM dual;

-- DATE - NUMBER = DATE  (OK)
SELECT SYSDATE - 10 AS past_date FROM dual;

-- DATE - DATE = NUMBER  (OK — returns days between)
SELECT TO_DATE('2024-12-31','YYYY-MM-DD') - SYSDATE AS days_left FROM dual;

-- DATE + DATE = ERROR   (NOT OK — ORA-00975)
-- Never do this!
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enforce explicit type handling in code reviews. Add a rule to your SQL coding standards that forbids DATE + DATE patterns. Integrate static analysis tools into your CI/CD pipeline to catch this automatically before deployment.

  2. Prefer INTERVAL, ADD_MONTHS, and NUMTODSINTERVAL for all date arithmetic. These functions are safer, more readable, and handle edge cases like leap years and month-end dates automatically.

-- Preferred patterns for production code
SELECT SYSDATE + INTERVAL '1' MONTH    AS next_month   FROM dual;
SELECT SYSDATE + INTERVAL '1' YEAR     AS next_year    FROM dual;
SELECT ADD_MONTHS(SYSDATE, 3)          AS q_later      FROM dual;
SELECT SYSDATE + NUMTODSINTERVAL(4,'HOUR') AS four_hrs FROM dual;
Enter fullscreen mode Exit fullscreen mode

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