Satya commented on my post about finding the first or second Monday in a month asking how to find the last Saturday of a year using SQL. This is a good question as I think it is typical of the battles people fight with dates in Oracle.
So here we go… For this example we’ll use sysdate
but you can use a date column or a to_date
instead.
SQL> select sysdate from dual;
SYSDATE
---------
13-SEP-06
Now we’ll jump forward a year and start working backwards:
SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;
SYSDATE+N
---------
13-SEP-07
Now that we’re safely into next year we’ll reel it back to January 1st of next year using the trunc
function to truncate the date down to the year.
SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') from dual;
TRUNC(SYS
---------
01-JAN-07
To work with the last week of the year we’ll go back 8 days. We need to go back 8 instead of 7 because we’re going to use the next_day
function later which only looks after the date it is passed.
SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8 from dual;
TRUNC(SYS
---------
24-DEC-06
Now we use the next_day
function to look for the next Saturday after the date we’ve got.
SQL> select next_day(trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8, 'SATURDAY') from dual;
NEXT_DAY(
---------
30-DEC-06
So we figured out the last Saturday of this year by taking today’s date, adding one year, going back to January 1 of that year, stepping back 8 days from then and looking for the next Saturday.
Hope this helps Satya! Thanks for the great question.