Date Manipulation in Oracle SQL

Introduction

In any development project using SQL or PL/SQL, there is inevitably a DATE variable or column and you will have to do some type of manipulation to that date at some point. Some of the requirements are fairly straight forward, but others may require a little bit more thought to get the formula right.

Over my years in IT I have done my fair share of date manipulation. This includes SQL queries for reporting, Oracle jobs and many procedures and functions. In my most recent project at work, I have been working on a report scheduling where the schedules are recurring. As such, I have had to work out some simple and some more complex date manipulation formulas.

I thought I should share these because I know just how useful these tips can be when working out solutions.

In all my examples, my date input will be SYSDATE. With any formula given, just replace SYSDATE with any date that you need. I am using the TRUNC() function to remove the time portion from the date. Remove it if you wish to keep the time portion on the date.
To get an instant result when testing the formulas in the examples, I have used SELECT ... FROM DUAL. It may be better in your environment, especially for more complex formulas, to put the logic into functions or into a package if you end up with quite a few functions.

Some Simple Solutions

Tomorrow

SELECT TRUNC(SYSDATE + 1) FROM DUAL
                      

Select all Open in new window

In 7 days

SELECT TRUNC(SYSDATE + 7) FROM DUAL
                      

Select all Open in new window

First day of next month

SELECT TRUNC(LAST_DAY(SYSDATE) + 1) FROM DUAL
                      

Select all Open in new window

Last day of next month

SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, 1))) FROM DUAL
                      

Select all Open in new window

Next Tuesday

SELECT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') FROM DUAL
                      

Select all Open in new window

15th day of next month

SELECT TRUNC(LAST_DAY(SYSDATE) + 15) FROM DUAL
                      

Select all Open in new window

Next Weekday at 2:30 PM

SELECT CASE WHEN TRIM(TO_CHAR(TRUNC(SYSDATE), 'DAY')) = 'SATURDAY' THEN TRUNC(TRUNC(SYSDATE) + 2) 
                             ELSE TRUNC(SYSDATE) + 1
                             END + 14.5/24
                      FROM DUAL
                      

Select all Open in new window

Next Weekday with Date and Time Substitution Variables
:pi_date : has to be in the format 'DD-MON-YYYY'
:pi_time : any number between 0 and 24

SELECT CASE WHEN TRIM(TO_CHAR(to_date(:pi_date, 'DD-MON-YYYY'), 'DAY')) = 'SATURDAY' THEN TRUNC(to_date(:pi_date, 'DD-MON-YYYY') + 2) 
                             ELSE TRUNC(to_date(:pi_date, 'DD-MON-YYYY') + 1) 
                             END + (:pi_time/24)
                      FROM DUAL
                      

Select all Open in new window

Next Non Weekday

SELECT CASE WHEN TRIM(TO_CHAR(TRUNC(SYSDATE), 'DAY')) = 'SATURDAY' THEN TRUNC(SYSDATE) + 1 
                             ELSE NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')
                             END
                      FROM DUAL
                      

Select all Open in new window

A More Complex Solution

Nth Day of the Week in a Month
How to dynamically calculate the Nth Day of the week within a month

Do you need to dynamically calculate the 3rd Tuesday of next month or something similar? Oracle has a simple way of doing this and can be expressed with the following formula.

NEXT_DAY(LAST_DAY( date), ' DAY') + (7 * ( instance - 1)) + time

date - is the start date the calculation needs to start at
instance - which occurance of the day in the month
DAY - day of the week
time - time of day to add to the date (optional)

The LAST_DAY Oracle function returns the last day of the month after the date given.
The NEXT_DAY Oracle function will give you the 1st occurance of the DAY in the next month.
From there, you need to add 7 days for each recurring instance of the DAY in the next month.


Example 1 - 3rd Tuesday of the month at 10 am

date - TRUNC(SYSDATE)
instance - 3
DAY - TUESDAY
time -10 am

NEXT_DAY(LAST_DAY(trunc(SY SDATE)), 'TUESDAY') + (7 * (3 - 1)) + 10/24

i.e. NEXT_DAY(LAST_DAY(trunc(SY SDATE)), 'TUESDAY') + 14 + 10/24

SELECT NEXT_DAY(LAST_DAY(trunc(SYSDATE)), 'TUESDAY') + (7 * (3 - 1)) + 10/24
                      FROM DUAL
                      
                      SELECT NEXT_DAY(LAST_DAY(trunc(SYSDATE)), 'TUESDAY') + 14 + 10/24
                      FROM DUAL
                      

Select all Open in new window

Example 2 - 1st Monday of the month at 2 pm


date - TRUNC(SYSDATE)
instance - 1
DAY - MONDAY
time -2 pm

NEXT_DAY(LAST_DAY(trunc(SY SDATE)), 'MONDAY') + (7 * (1 - 1)) + 14/24

i.e. NEXT_DAY(LAST_DAY(trunc(SY SDATE)), 'MONDAY') + 14/24

SELECT NEXT_DAY(LAST_DAY(trunc(SYSDATE)), 'MONDAY') + (7 * (1 - 1)) + 14/24
                      FROM DUAL
                      
                      SELECT NEXT_DAY(LAST_DAY(trunc(SYSDATE)), 'MONDAY') + 14/24
                      FROM DUAL
                      

Select all Open in new window