SQL – Finding the Last Weekday in a Month

OracleSidney V. commented on my post Performing Math on Oracle Dates that he was looking for a way to calculate the last weekday of a month. After some research I concluded that Oracle does not provide a function for this, so I wrote one.

I started with the last_day Oracle function to find the last day of the month. I’m using December 2005 as an example as the last day of the month was a Saturday.

SELECT last_day(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_DAY(
---------
31-DEC-05

This gives us the last day of the month regardless of the day of the week. Now we rewind from that 7 days by subtracting 7 from the date so we can look at the last week in detail.

SELECT last_day(to_date('12/2005','MM/YYYY')) - 7 FROM dual;

LAST_DAY(
---------
24-DEC-05

This lets us start 7 days before the end of the month. Now we can look at this last week in detail. To see the last Monday of the month we use the next_day function to go forward from the last_day() - 7.

SELECT
next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day
FROM dual;

DAY
---------
26-DEC-05

Using this we could find the last Monday, Tuesday, etc. of the month. In this case we want to find the last weekday (Monday through Friday) of the month. We can accomplish this with a UNION of several of these statements.

SELECT day
FROM (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual);

DAY
---------
26-DEC-05
27-DEC-05
28-DEC-05
29-DEC-05
30-DEC-05

So we know when the last Monday, Tuesday, Wednesday, Thursday, and Friday of this month was, but which was latest? For that we’ll apply the SQL MAX function to the results of the above union. To do this we put the union above in the FROM part of the query.

SELECT MAX (day)
FROM ((SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual));

MAX(DAY)
---------
30-DEC-05

Now we have the date of the last weekday of the month. The Oracle to_char function can be applied to this result to get the date in a different format.

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM ((SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual))
;

TO_CHAR(MAX(DAY)
----------------
FRIDAY , 12/30

Now let’s try this logic on another date to make sure it’s sound. We’ll try today (Jan, 6 2006).

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM (
(SELECT next_day((last_day(sysdate) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Friday') AS day FROM dual)
)
;

TO_CHAR(MAX(DAY)
----------------
TUESDAY , 01/31

So the logic works, but this would be a lot of typing if you wanted to use it, so let’s make it into a function so we can use it anywhere in the database.

CREATE OR REPLACE FUNCTION last_weekday(month_check DATE DEFAULT sysdate)
RETURN date
IS
last_weekday_date DATE;
BEGIN
SELECT MAX(day) INTO last_weekday_date
FROM ((SELECT next_day((last_day(month_check) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Friday') AS day FROM dual));
RETURN last_weekday_date;
END;
/

Note: If you want other users to be able to use this function you have to grant them execute on it. Creating a synonym for it may also be usefull.

Now let's test drive the function we created. We assigned a default of sysdate so if we call it without any parameters it should return the last weekday of the current month.

SELECT last_weekday FROM dual;

LAST_WEEKDAY_DATE
---------
31-JAN-06

If we pass this function a date as a parameter it will return the last weekday of that month.

SELECT last_weekday(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_WEEK
---------
30-DEC-05

Thanks Sidney for the challenge!

UPDATE: Oraboy writes with this algorithm. His code is much shorter than mine and likely more efficient. Thanks Oraboy!

With date_v as
(select last_day(to_date('&MM-&DD-&yyyyy','MM-DD-YYYY')) mydate from dual)
select max(date_v.mydate-(i-1)) last_weekday
from date_v,(select level i from dual connect by level < =3) where to_char(date_v.mydate-(i-1),'Dy') not in ('Sat','Sun')

oracle, sql, dba, database administration, database development

10 thoughts on “SQL – Finding the Last Weekday in a Month”

  1. very handy and useful code.
    do you have the similar code for access database query for the following, get the last friday of current month?

    next_day((last_day(to_date((sysdate),’DD/MM/YYYY’)) – 7), ‘Friday’)

  2. Veronica, unfortunately I don’t have this for Access. I’m not too familiar with Access and I’m sure the date functionality works differently. Sorry.

  3. How we find in the data base all the “dates” that belong to the last month compared to the current date (sysdate)????????

  4. Short and simple:

    select
    decode(
    to_char(last_day(sysdate),’DY’) –FET DAY OF WEEK FOR LAST DAY OF MONTH.
    ,’SAT’,last_day(sysdate)-1 –RETURN FRIDAY
    ,’SUN’,last_day(sysdate)-2 –RETURN FRIDAY
    ,last_day(sysdate) — ELSE RETURN LAST DAY OF MONTH
    ) last_day_of_current_month from dual

  5. Unfortunately both solutions depend on the user session to use US day names. In an international environment you should either add NLS_DATE_LANGUAGE parameters, or instead of using DY, use D (which uses numeric values) to test for a given day of the week.

  6. You could also try this one (Although this gets for the last month’s last weekday):

    with t as (
    select ’01-jul-08′ toDay from dual )
    select
    case
    when to_char( last_day( add_months(today, -1) ), ‘D’ ) in (7, 1) then
    next_day( today, 6 ) – 7
    else last_day( add_months( today, -1 ) )
    end last_weekday
    from t

  7. Sorry for that here’s the correct one.

    with t as (
    select ’30-sep-08′ toDay from dual )
    select
    case
    when to_char( last_day( add_months(today, -1) ), ‘D’ ) in (7, 1) then
    next_day( last_day( add_months(today, -1) ), 6 ) – 7
    else last_day( add_months( today, -1 ) )
    end last_weekday
    from t

  8. You could also use the oracle greatest function, thus eliminating all the unions:

    SELECT
    greatest
    (
    next_day((last_day(sysdate) - 7), 'Monday'),
    next_day((last_day(sysdate) - 7), 'Tuesday'),
    next_day((last_day(sysdate) - 7), 'Wednesday'),
    next_day((last_day(sysdate) - 7), 'Thursday'),
    next_day((last_day(sysdate) - 7), 'Friday')
    ) day
    from dual

Leave a Reply

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