4.3 Date Arithmetic & Date Manipulation Functions

4.3 Date Arithmetic & Date Manipulation Functions

Quick Answer: Oracle's DATE datatype stores century, year, month, day, hour, minute, and second in a fixed 7-byte structure. Date arithmetic is expressed in days (date + 1 adds 24 hours; date1 - date2 returns the difference in days). Key date functions: MONTHS_BETWEEN(d1, d2) returns exact integers when days match or both dates are month-end; ADD_MONTHS preserves month-end dates; NEXT_DAY returns the next occurrence of a named weekday; and ROUND/TRUNC round or truncate dates using format models ('MONTH', 'YEAR'). Note: Date + Date is invalid and raises ORA-00975.

---

Oracle Internal DATE Representation

In Oracle Database, the DATE datatype is stored internally as a 7-byte fixed-length binary structure. It always contains both date and time components, even if the client display format hides the time.

7-Byte Internal Structure Breakdown:

• Byte 1: Century + 100
• Byte 2: Year in century + 100
• Byte 3: Month (1–12)
• Byte 4: Day of month (1–31)
• Byte 5: Hour + 1 (1–24, representing 0–23 hours)
• Byte 6: Minute + 1 (1–60, representing 0–59 minutes)
• Byte 7: Second + 1 (1–60, representing 0–59 seconds)

System Date Functions

• SYSDATE: Returns the current date and time of the database server operating system.
• CURRENT_DATE: Returns the current date and time in the session's time zone.

-- Display full date and time components
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS current_server_time
FROM dual;

---

Date Arithmetic Operations

Date arithmetic is performed using integer and fractional values of days (where 1.0 represents 24 hours).

SELECT
  SYSDATE + 1          AS tomorrow,
  SYSDATE - 7          AS one_week_ago,
  SYSDATE + (2 / 24)   AS two_hours_later,
  SYSDATE + (15 / 1440) AS fifteen_minutes_later
FROM dual;

---

Date Manipulation Functions

Oracle provides six dedicated single-row functions for date calculations:

1. MONTHS_BETWEEN(date1, date2)

Calculates the number of months between date1 and date2.

• If date1 > date2, returns a positive number.
• If date1 < date2, returns a negative number.
• The Exact Integer Rule: If date1 and date2 have the same day of the month OR if both dates are the last day of their respective months, MONTHS_BETWEEN returns an exact integer.
• If the days differ and are not both month-end, Oracle computes a fractional result based on a 31-day month divisor.

SELECT
  MONTHS_BETWEEN('01-SEP-2026', '01-JUN-2026') AS m1, -- 3.0    (Exact day match: 1st to 1st)
  MONTHS_BETWEEN('30-JUN-2026', '28-FEB-2026') AS m2, -- 4.0    (Both are last day of month!)
  MONTHS_BETWEEN('31-MAY-2026', '28-FEB-2026') AS m3, -- 3.0    (Both are last day of month!)
  MONTHS_BETWEEN('01-JUN-2026', '01-SEP-2026') AS m4, -- -3.0   (date1 < date2 yields negative)
  MONTHS_BETWEEN('15-JUL-2026', '01-JUN-2026') AS m5  -- 1.4516 (Fractional calculation)
FROM dual;

2. ADD_MONTHS(date, n)

Adds n calendar months to date (n can be positive or negative).

The End-of-Month Preservation Rule:

1. If date is the last day of the month, ADD_MONTHS always returns the last day of the resulting target month, even if the months have different lengths (e.g., 28, 30, or 31 days).
2. If the target month has fewer days than the day of date, the result automatically rolls back to the last day of the target month.

SELECT
  ADD_MONTHS('31-JAN-2026', 1)  AS a1, -- 28-FEB-2026 (Last day of Feb)
  ADD_MONTHS('28-FEB-2026', 1)  AS a2, -- 31-MAR-2026 (28-FEB was month-end -> returns month-end!)
  ADD_MONTHS('31-MAR-2026', 1)  AS a3, -- 30-APR-2026 (Apr has only 30 days)
  ADD_MONTHS('15-JAN-2026', 2)  AS a4, -- 15-MAR-2026 (Normal day matching)
  ADD_MONTHS('15-MAR-2026', -2) AS a5  -- 15-JAN-2026 (Negative n subtracts months)
FROM dual;

3. NEXT_DAY(date, 'day_name')

Returns the date of the next specified day of the week strictly after date.

• 'day_name' can be the full name ('FRIDAY') or standard abbreviation ('FRI'), governed by session NLS settings.
• Strict Future Rule: If date is already Friday, NEXT_DAY(date, 'FRIDAY') returns the date of the following Friday (7 days later).

-- Assuming today is Monday, 15-JUN-2026
SELECT NEXT_DAY('15-JUN-2026', 'FRIDAY') FROM dual; -- Returns 19-JUN-2026
SELECT NEXT_DAY('19-JUN-2026', 'FRIDAY') FROM dual; -- Returns 26-JUN-2026 (Strictly future)

4. LAST_DAY(date)

Returns the date of the final day of the month that contains date.

SELECT
  LAST_DAY('14-FEB-2024') AS leap_year_feb,   -- 29-FEB-2024 (Leap year)
  LAST_DAY('14-FEB-2025') AS non_leap_feb,    -- 28-FEB-2025
  LAST_DAY('05-JUN-2026') AS june_end         -- 30-JUN-2026
FROM dual;

---

Rounding & Truncating Dates: ROUND and TRUNC

ROUND(date [, format]) and TRUNC(date [, format]) adjust dates based on format models.

Format Models for Date Rounding and Truncation:

• 'MONTH' or 'MON':
  • TRUNC(d, 'MONTH'): Returns the 1st day of the current month (01-MON-YYYY 00:00:00).
  • ROUND(d, 'MONTH'): If day is 1 to 15, rounds down to 1st of current month. If day is 16 to 31, rounds up to 1st of next month.
• 'YEAR', 'YYYY', or 'YY':
  • TRUNC(d, 'YEAR'): Returns January 1st of the current year (01-JAN-YYYY 00:00:00).
  • ROUND(d, 'YEAR'): If month is January through June (1–6), rounds down to Jan 1 of current year. If month is July through December (7–12), rounds up to Jan 1 of next year.
• 'DAY' or 'DY':
  • TRUNC(d, 'DAY'): Returns the Sunday of the current week (assuming Sunday start).
  • ROUND(d, 'DAY'): Rounds to nearest Sunday (Wednesday noon is the midpoint cutoff).
• Omitted Format Model: Truncates or rounds to the nearest day, effectively resetting the time component to 00:00:00.

SELECT
  TRUNC(TO_DATE('18-JUN-2026', 'DD-MON-YYYY'), 'MONTH') AS t_mon, -- 01-JUN-2026
  ROUND(TO_DATE('18-JUN-2026', 'DD-MON-YYYY'), 'MONTH') AS r_mon, -- 01-JUL-2026 (18 >= 16 rounds up)
  ROUND(TO_DATE('15-JUN-2026', 'DD-MON-YYYY'), 'MONTH') AS r_mon2,-- 01-JUN-2026 (15 <= 15 rounds down)
  TRUNC(TO_DATE('18-JUN-2026', 'DD-MON-YYYY'), 'YEAR')  AS t_yr,  -- 01-JAN-2026
  ROUND(TO_DATE('18-JUN-2026', 'DD-MON-YYYY'), 'YEAR')  AS r_yr,  -- 01-JAN-2026 (June <= 6 rounds down)
  ROUND(TO_DATE('01-JUL-2026', 'DD-MON-YYYY'), 'YEAR')  AS r_yr2  -- 01-JAN-2027 (July >= 7 rounds up)
FROM dual;

---

Summary of 1Z0-071 Date Function Traps