SQL to find the last Saturday of the year

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.

oracle, dates, database, sql, dba, dbms, database development, database programming

9 thoughts on “SQL to find the last Saturday of the year”

  1. Nice exercise Jon. To simplify the SQL, we can also use the following:

    SQL> select next_day(round(sysdate,'YEAR') - 8, 'SAT') from dual;

    NEXT_DAY(
    ---------
    30-DEC-06

  2. Ebrian,

    That doesn’t quite work. The round function does exactly what it sounds like it should, it rounds to the nearest year. It works for the end of this year now because we’re closer to the end of the year, but look at what we would have gotten in March:

    SQL> select next_day(round(to_date('03/01/06','MM/DD/YY'),'YEAR')-8,'SAT') from dual;

    NEXT_DAY(
    ---------
    31-DEC-05

    It still pulls the last Saturday but now it’s from last year.

    Thanks for the comment! I didn’t even know you could use the round function on dates. I guess I’ve never had the need.

  3. Yes indeed…good point Jon. A slight mod is needed to accommodate for that.

    SQL> select next_day(round(add_months(sysdate,6),'rr') - 8, 'SAT') from dual;

    NEXT_DAY(
    ---------
    30-DEC-06

    SQL> select next_day(round(add_months(to_date('03/01/06','MM/DD/YY'),6),'rr') - 8, 'SAT') from dual;

    NEXT_DAY(
    ---------
    30-DEC-06

    Keep up the good work !

  4. Yup, that should work. Of course it’s now slightly more complicated than my original but it’s always nice to have more than one way to do something!

  5. Would very appreciate the sql code. What i try on the command below :
    select sysdate+numtoyminterval(1, ‘YEAR’) from dual;

    It will only fail on 29-Feb-2004 …. It there any possible to avoid it?

  6. Nasrullah..for that u need to have a column called created date
    ….if you already have that u can use this query

    select *
    from
    where (sysdate-created_date)

Leave a Reply

Your email address will not be published. Required fields are marked *