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
SQL> select sysdate from dual;
Now we’ll jump forward a year and start working backwards:
SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;
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;
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;
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;
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.