<

Date Manipulation in Oracle SQL

Published on
11,785 Points
5,085 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
Comment
1 Comment
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
nice article..!!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month