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.
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