Working with dates in Oracle

Date arithmetic is one of the most commonly needed skills in Oracle. Dates can be added, subtracted, truncated, and formatted, but there are some Oracle-specific quirks that trip up even experienced developers.

Recipe #1 - Basic date arithmetic

In Oracle, adding a number to a date adds days. Fractions of a day work for hours, minutes, and seconds:

SELECT SYSDATE                    AS now,
       SYSDATE + 1                AS tomorrow,
       SYSDATE - 7                AS one_week_ago,
       SYSDATE + 1/24             AS one_hour_later,
       SYSDATE + 30/1440          AS thirty_minutes_later,
       SYSDATE + 45/86400         AS forty_five_seconds_later
FROM DUAL;

Recipe #2 - Difference between two dates

Subtracting two dates gives the difference in days as a number:

SELECT hiredate,
       SYSDATE - hiredate AS days_employed,
       ROUND((SYSDATE - hiredate) / 365.25, 1) AS years_employed
FROM emp
WHERE empno = 7839;

Recipe #3 - TO_CHAR for date formatting

Oracle's TO_CHAR is extremely flexible for formatting dates:

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY')          AS european,
       TO_CHAR(SYSDATE, 'MM/DD/YYYY')          AS american,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD')          AS iso,
       TO_CHAR(SYSDATE, 'Day, DD Month YYYY')  AS full_text,
       TO_CHAR(SYSDATE, 'HH24:MI:SS')          AS time_only,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp_like
FROM DUAL;

Recipe #4 - TRUNC and ROUND for dates

TRUNC removes the time component; ROUND rounds to the nearest unit:

SELECT SYSDATE                               AS current_time,
       TRUNC(SYSDATE)                        AS start_of_day,
       TRUNC(SYSDATE, 'MM')                  AS start_of_month,
       TRUNC(SYSDATE, 'YYYY')                AS start_of_year,
       TRUNC(SYSDATE, 'IW')                  AS start_of_week,
       LAST_DAY(SYSDATE)                     AS end_of_month,
       ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1 AS last_day_alt
FROM DUAL;

Recipe #5 - ADD_MONTHS and MONTHS_BETWEEN

For month-based arithmetic, never multiply by 30 — use ADD_MONTHS instead:

SELECT ADD_MONTHS(SYSDATE, 3)    AS three_months_later,
       ADD_MONTHS(SYSDATE, -6)   AS six_months_ago,
       MONTHS_BETWEEN(
         TO_DATE('2024-12-31', 'YYYY-MM-DD'),
         TO_DATE('2024-01-01', 'YYYY-MM-DD')
       ) AS months_diff
FROM DUAL;

Recipe #6 - Generate a list of dates

Need every day of the current month? Combine CONNECT BY with date functions:

SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day_of_month,
       TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'Day') AS day_name
FROM DUAL
CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(SYSDATE));