<

Date Manipulation in Oracle SQL

Published on
12,237 Points
5,537 Views
2 Endorsements
Last Modified:
Approved

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

Open in new window

In 7 days
SELECT TRUNC(SYSDATE + 7) FROM DUAL

Open in new window

First day of next month
SELECT TRUNC(LAST_DAY(SYSDATE) + 1) FROM DUAL

Open in new window

Last day of next month
SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, 1))) FROM DUAL

Open in new window

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

Open in new window

Replace TUESDAY with any required day of the week.
15th day of next month
SELECT TRUNC(LAST_DAY(SYSDATE) + 15) FROM DUAL

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

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

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

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(SYSDATE)), 'TUESDAY') + (7 * (3 - 1)) + 10/24

i.e. NEXT_DAY(LAST_DAY(trunc(SYSDATE)), '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

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(SYSDATE)), 'MONDAY') + (7 * (1 - 1)) + 14/24

i.e. NEXT_DAY(LAST_DAY(trunc(SYSDATE)), '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

Open in new window

2
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free