A Guide to Oracle’s Date/Time Functions

This article provides a detailed explanation of Oracle Database’s date/time functions—ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, and SYSDATE—including specific use cases and important considerations. These functions provide essential capabilities for processing data.

1. ADD_MONTHS

ADD_MONTHS(date, n) returns a date n months before or after a specified date. If n is a negative value, it calculates a past date; if n is a positive value, it calculates a future date.

Usage Example

Start date: 01/15/2025

SELECT ADD_MONTHS(TO_DATE('01/15/2025', 'MM/DD/YYYY'), 3) AS result FROM dual;

SELECT ADD_MONTHS(TO_DATE('01/15/2025', 'MM/DD/YYYY'), -2) AS result FROM dual;

SQL> SELECT ADD_MONTHS(TO_DATE('01/15/2025', 'MM/DD/YYYY'), 3) AS result FROM dual;

RESULT
---------
15-APR-25

SQL> SELECT ADD_MONTHS(TO_DATE('01/15/2025', 'MM/DD/YYYY'), -2) AS result FROM dual;

RESULT
---------
15-NOV-24

Points to Note

If the input date is the last day of the month, the resulting date will also be adjusted to the last day of the target month.

SELECT ADD_MONTHS(TO_DATE('02/20/2025', 'MM/DD/YYYY'), 1) AS result FROM dual;

SQL> SELECT ADD_MONTHS(TO_DATE('02/20/2025', 'MM/DD/YYYY'), 1) AS result FROM dual;

RESULT
---------
20-MAR-25

Practical Example

For example, it can be used to calculate a customer’s subscription period.

SELECT customer_id, ADD_MONTHS(subscription_start_date, 12) AS subscription_end_date
FROM customers;

2. MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2) returns the number of months between two dates. The result is returned as a real number, and the fractional part is calculated based on the difference in days.

Usage Example

SELECT MONTHS_BETWEEN(TO_DATE('04/15/2025', 'MM/DD/YYYY'), TO_DATE('01/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

SELECT MONTHS_BETWEEN(TO_DATE('01/15/2025', 'MM/DD/YYYY'), TO_DATE('04/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

SQL> SELECT MONTHS_BETWEEN(TO_DATE('04/15/2025', 'MM/DD/YYYY'), TO_DATE('01/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

    RESULT
----------
         3

SQL> SELECT MONTHS_BETWEEN(TO_DATE('01/15/2025', 'MM/DD/YYYY'), TO_DATE('04/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

    RESULT
----------
        -3

Points to Note

If the year and month are the same but the days are different, the difference in days is reflected in the fractional part.

SELECT MONTHS_BETWEEN(TO_DATE('01/31/2024', 'MM/DD/YYYY'), TO_DATE('01/01/2024', 'MM/DD/YYYY')) AS result FROM dual;
-- Result: Approx 1 (accounts for 30 days)

Applied Example

For example, used when calculating a loan repayment period.

SELECT loan_id, MONTHS_BETWEEN(payment_due_date, loan_start_date) AS repayment_period
FROM loans;

3. NEXT_DAY

NEXT_DAY(date, weekday) returns the date of the next specified day of the week after the specified date. The weekday is specified as a string.

Usage Example

Start date: 01/15/2025 (Wednesday)

SELECT NEXT_DAY(TO_DATE('01/15/2025', 'MM/DD/YYYY'), 'FRIDAY') AS result FROM dual;

SQL> SELECT NEXT_DAY(TO_DATE('01/15/2025', 'MM/DD/YYYY'), 'FRIDAY') AS result FROM dual;

RESULT
---------
17-JAN-25

Points to Note

The weekday specification depends on the database’s language settings. In an English environment, the available days are: ‘SUNDAY’, ‘MONDAY’, ‘TUESDAY’, ‘WEDNESDAY’, ‘THURSDAY’, ‘FRIDAY’, ‘SATURDAY’.

Practical Example

Useful for calculating the next payment date.

SELECT customer_id, NEXT_DAY(payment_date, 'FRIDAY') AS next_payment_date
FROM payments;

4. LAST_DAY

LAST_DAY(date) returns the last day of the month that contains the specified date.

Usage Example

SELECT LAST_DAY(TO_DATE('01/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

SELECT LAST_DAY(TO_DATE('02/10/2025', 'MM/DD/YYYY')) AS result FROM dual;

SQL> SELECT LAST_DAY(TO_DATE('01/15/2025', 'MM/DD/YYYY')) AS result FROM dual;

RESULT
---------
31-JAN-25

SQL> SELECT LAST_DAY(TO_DATE('02/10/2025', 'MM/DD/YYYY')) AS result FROM dual;

RESULT
---------
28-FEB-25

Points to Note

If the input date falls in February of a leap year, it correctly calculates the last day.

Applied Example

For example, used when calculating end-of-month closing processes.

SELECT employee_id, LAST_DAY(payment_date) AS payroll_cutoff_date
FROM payroll;

5. SYSDATE

SYSDATE returns the current date and time of the database server at the time of query execution.

Usage Example

SELECT SYSDATE AS result FROM dual;

SELECT SYSDATE + 7 AS next_week FROM dual;

SQL> SELECT SYSDATE AS result FROM dual;

RESULT
---------
15-NOV-25

SQL> SELECT SYSDATE + 7 AS next_week FROM dual;

NEXT_WEEK
---------
22-NOV-25

Points to Note

The SYSDATE result is based on the database server’s time zone. Be aware that this may differ from the client’s time zone.

Applied Example

For example, setting a promotion period based on today’s date.

SELECT SYSDATE AS today, SYSDATE + 30 AS promotion_end_date
FROM dual;

FAQ

Q: What happens if I specify an invalid day of the week in NEXT_DAY? A: An error will occur. The day of the week depends on the database’s language settings, so check your configuration.

Q: How does ADD_MONTHS behave when a negative value is specified? A: Specifying a negative value calculates a date in the past. End-of-month adjustments are also applied.

Summary

These functions are powerful tools for processing date/time information. Understand the logic of each function and try to apply it to real business scenarios. It is also important to be aware of environmental factors such as time zones and database language settings when using them. Aim for further utilization through visual representations and practical examples.

[reference]
Oracle Database SQL Language Reference, 19c