ExamFinch.com My Study

Study guide

4.3 Date Arithmetic & Date Manipulation Functions

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

4.3 Date Arithmetic & Date Manipulation Functions

Key Takeaways Explain This Topic AI

Oracle stores DATE values in an internal 7-byte binary format containing century, year, month, day, hours, minutes, and seconds.

Date arithmetic operates in units of days: Date + Number adds days, Date - Number subtracts days, Date - Date returns the fractional difference in days, and Date + Date is illegal (ORA-00975).

MONTHS_BETWEEN(d1, d2) returns an exact integer if both dates have the same day of the month or if both dates represent the final day of their respective months.

ADD_MONTHS preserves end-of-month status: adding months to the last day of a month always returns the last day of the resulting target month.

ROUND(date, 'MONTH') rounds up to the 1st of the next month if the day is 16 or greater, while ROUND(date, 'YEAR') rounds up to Jan 1 of the next year if the month is July 1 or later.

Last updated: August 2026

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

  • Operation Expression Result Datatype Meaning
  • Date + Number hire_date + 7 DATE Adds 7 days to hire_date
  • Date - Number hire_date - 30 DATE Subtracts 30 days from hire_date
  • Date + Fractional SYSDATE + (5/24) DATE Adds 5 hours to current time
  • Date + Fractional SYSDATE + (10/1440) DATE Adds 10 minutes to current time
  • Date + Fractional SYSDATE + (30/86400) DATE Adds 30 seconds to current time

Date - Date end_date - start_date NUMBER Difference between two dates in number of days (including fractional decimals)

  • Date + Date date1 + date2 ERROR ORA-00975: date + date not allowed 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:

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

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

Function / Operation Common Exam Trap Correct Rule

Date + Date Believing two dates can be added Date + Date is invalid ( ORA-00975 ). Only Date - Date is valid.

Date - Date Expecting an interval or integer Returns a NUMBER representing days (with decimal fractions).

MONTHS_BETWEEN Expecting decimals when comparing month-ends Returns exact integer if both dates are the last day of their month (e.g. 28-FEB and 31-MAY).

ADD_MONTHS Adding 1 month to 28-FEB gives 28-MAR Adding to the last day of a month returns the last day of the target month ( 31-MAR ).

NEXT_DAY Querying next day on the current day returns today Returns the next occurrence in the strict future (+7 days).

ROUND(d, 'MONTH') Cutoff is the 15th Days 1–15 round down to current month; days 16–31 round up to next month.

ROUND(d, 'YEAR') Cutoff is June 1st Months 1–6 (Jan–Jun) round down to Jan 1; Months 7–12 (Jul–Dec) round up to Jan 1 next year.