<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Date Manipulation in Oracle SQL

Published on
12,032 Points
5,332 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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month