In one of my frequent trips through Google on the search for how to do something (I forget what it was I was looking for on this particular occasion), I found myself, as I often do, being directing to various contributions from posts on life after coffee.com
Whilst the entry it turned out wasn't relevant to what I was searching for, it did however interest me, particularly a question someone had posted asking how to retrieve the last weekday (i.e. Monday to Friday) from a month. From reading the responses and following the link to a response specifically to this question I could see that the question had been well addressed, but it occurred to me that I actually knew of another way to answer this question, and after playing around a little to refresh my memory, here is my contribution:
1 select (case when ((next_day(last_day(to_date('&date', 'MM YYYY')),'Monday')-3) > last_day(to_date('&date', 'MM YYYY')))
2 then last_day(to_date('&date', 'MM YYYY'))
3 else next_day(last_day(to_date('&date', 'MM YYYY')),'Monday')-3
4 end) Last_WeekDay
5 from dual
What this query does first is to take the date variable, entered as a month/year and convert it to a date, which by default will be the first of the month. Next it uses the last_day command to find the end of the month. The next_day command is then used to find the first Monday after the end of the month, and subtracting 3 from this returns the previous Friday. Finally, a case statement is used to evaluate the date of this Friday to determine if it is greater than the end of the month or not.
Logic tells us that for the last day of the month not to be a weekday, it must be a Saturday and Sunday, therefore the Friday we have identified must be the last weekday if this is the case. Otherwise, the last day of the month is one of the five weekdays, Monday to Friday.
To see the steps in this query I have included the full sequence below:
1 select to_date('&date', 'MM YYYY') First_of_Month
2 , last_day(to_date('&date', 'MM YYYY')) Last_of_Month
3 , next_day(last_day(to_date('&date', 'MM YYYY')),'Monday') Next_Monday
4 , next_day(last_day(to_date('&date', 'MM YYYY')),'Monday')-3 Last_Friday
5 , (case when ((next_day(last_day(to_date('&date', 'MM YYYY')),'Monday')-3) > last_day(to_date('&date', 'MM YYYY')))
6 then last_day(to_date('&date', 'MM YYYY'))
7 else next_day(last_day(to_date('&date', 'MM YYYY')),'Monday')-3
8 end) Last_WeekDay
9 from dual
As I stated above, I often find entries posted to life after coffee useful, so here's another link to some more date related calculations you might find interesting.
James