Recipe9.6.Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month

Recipe 9.6. Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month

Problem

You want to find, for example, the first and last Mondays of the current month.

Solution

The choice to use Monday and the current month is arbitrary; you can use the solutions presented in this recipe for any weekday and any month. Because each weekday is seven days apart from itself, once you have the first instance of a weekday, you can add 7 days to get the second and 14 days to get the third. Likewise, if you have the last instance of a weekday in a month, you can subtract 7 days to get the third and subtract 14 days to get the second.

DB2

Use the recursive WITH clause to generate each day in the current month and use a CASE expression to flag all Mondays. The first and last Mondays will be the earliest and latest of the flagged dates:

  1   with x (dy,mth,is_monday)  2     as (  3 select dy,month(dy),  4        case when dayname(dy)='Monday'  5              then 1 else 0  6        end  7   from (  8 select (current_date-day(current_date) day +1 day) dy  9   from t1 10        ) tmp1 11  union all 12 select (dy +1 day), mth, 13        case when dayname(dy +1 day)='Monday' 14             then 1 else 0 15        end 16   from x 17  where month(dy +1 day) = mth 18 ) 19 select min(dy) first_monday, max(dy) last_monday 20   from x 21  where is_monday = 1

Oracle

Use the functions NEXT_DAY and LAST_DAY, together with a bit of clever date arithmetic, to find the first and last Mondays of the current month:

 select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,        next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday   from dual

PostgreSQL

Use the function DATE_TRUNC to find the first day of the month. Once you have the first day of the month, you can use simple arithmetic involving the numeric values of weekdays (SunSat is 17) to find the first and last Mondays of the current month:

  1 select first_monday,  2        case to_char(first_monday+28,'mm')  3             when mth then first_monday+28  4                      else first_monday+21  5        end as last_monday  6   from (  7 select case sign(cast(to_char(dy,'d') as integer)-2)  8             when 0  9             then dy 10             when -1 11             then dy+abs(cast(to_char(dy,'d') as integer)-2) 12             when 1 13             then (7-(cast(to_char(dy,'d') as integer)-2))+dy 14        end as first_monday, 15        mth 16   from ( 17 select cast(date_trunc('month',current_date) as date) as dy, 18        to_char(current_date,'mm') as mth 19   from t1 20        ) x 21        ) y

MySQL

Use the ADDDATE function to find the first day of the month. Once you have the first day of the month, you can use simple arithmetic on the numeric values of weekdays (SunSat is 06) to find the first and last Mondays of the current month:

  1 select first_monday,  2        case month(adddate(first_monday,28))  3             when mth then adddate(first_monday,28)  4                      else adddate(first_monday,21)  5        end last_monday  6  from (  7 select case sign(dayofweek(dy)-2)  8             when 0 then dy  9             when -1 then adddate(dy,abs(dayofweek(dy)-2)) 10             when 1 then adddate(dy,(7-(dayofweek(dy)-2))) 11        end first_monday, 12        mth 13   from ( 14 select adddate(adddate(current_date,-day(current_date)),1) dy, 15        month(current_date) mth 16   from t1 17        ) x 18        ) y

SQL Server

Use the recursive WITH clause to generate each day in the current month, and then use a CASE expression to flag all Mondays. The first and last Mondays will be the earliest and latest of the flagged dates:

  1   with x (dy,mth,is_monday)  2     as (  3 select dy,mth,  4        case when datepart(dw,dy) = 2  5             then 1 else 0  6        end  7   from (  8 select dateadd(day,1,dateadd(day,-day(getdate( )),getdate( ))) dy,  9        month(getdate( )) mth 10   from t1 11        ) tmp1 12  union all 13 select dateadd(day,1,dy), 14        mth, 15        case when datepart(dw,dateadd(day,1,dy)) = 2 16             then 1 else 0 17        end 18   from x 19  where month(dateadd(day,1,dy)) = mth 20 ) 21 select min(dy) first_monday, 22        max(dy) last_monday 23   from x 24  where is_monday = 1

Discussion

DB2 and SQL Server

DB2 and SQL Server use different functions to solve this problem, but the technique is exactly the same. If you eyeball both solutions you'll see the only difference between the two is the way dates are added. This discussion will cover both solutions, using the DB2 solution's code to show the results of intermediate steps.

If you do not have access to the recursive WITH clause in the version of SQL Server or DB2 that you are running, you can use the PostgreSQL technique instead.

The first step in finding the first and last Mondays of the current month is to return the first day of the month. Inline view TMP1 in recursive view X finds the first day of the current month by first finding the current date, specifically, the day of the month for the current date. The day of the month for the current date represents how many days into the month you are (e.g., April 10th is the 10th day of the April). If you subtract this day of the month value from the current date, you end up at the last day of the previous month (e.g., subtracting 10 from April 10th puts you at the last day of March). After this subtraction, simply add one day to arrive at the first day of the current month:

  select (current_date-day(current_date) day +1 day) dy   from t1 DY ----------- 01-JUN-2005

Next, find the month for the current date using the MONTH function and a simple CASE expression to determine whether or not the first day of the month is a Monday:

  select dy, month(dy) mth,         case when dayname(dy)='Monday'              then 1 else 0         end is_monday   from  ( select  (current_date-day(current_date) day +1 day) dy   from  t1         ) tmp1 DY          MTH  IS_MONDAY ----------- --- ---------- 01-JUN-2005   6          0

Then use the recursive capabilities of the WITH clause to repeatedly add one day to the first day of the month until you're no longer in the current month. Along the way, you will use a CASE expression to determine which days in the month are Mondays (Mondays will be flagged with "1"). A portion of the output from recursive view X is shown below:

  with x (dy,mth,is_monday)      as (  select dy,month(dy) mth,         case when dayname(dy)='Monday'              then 1 else 0         end is_monday    from (  select (current_date-day(current_date) day +1 day) dy    from t1         ) tmp1   union all  select (dy +1 day), mth,         case when dayname(dy +1 day)='Monday'              then 1 else 0         end    from x   where month(dy +1 day) = mth  )  select *    from x DY          MTH  IS_MONDAY ----------- --- ---------- 01-JUN-2005   6          0 02-JUN-2005   6          0 03-JUN-2005   6          0 04-JUN-2005   6          0 05-JUN-2005   6          0 06-JUN-2005   6          1 07-JUN-2005   6          0 08-JUN-2005   6          0 …

Only Mondays will have a value of 1 for IS_MONDAY, so the final step is to use the aggregate functions MIN and MAX on rows where IS_MONDAY is 1 to find the first and last Mondays of the month.

Oracle

The function NEXT_DAY makes this problem easy to solve. To find the first Monday of the current month, first return the last day of the prior month via some date arithmetic involving the TRUNC function:

  select trunc(sysdate,'mm')-1 dy   from dual DY ----------- 31-MAY-2005

Then use the NEXT_DAY function to find the first Monday that comes after the last day of the previous month (i.e., the first Monday of the current month):

  select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday   from dual FIRST_MONDAY ------------ 06-JUN-2005

To find the last Monday of the current month, start by returning the first day of the current month by using the TRUNC function:

  select trunc(sysdate,'mm') dy   from dual DY ----------- 01-JUN-2005

The next step is to find the last week (the last seven days) of the month. Use the LAST_DAY function to find the last day of the month, and then subtract seven days:

  select last_day(trunc(sysdate,'mm'))-7 dy   from dual DY ----------- 23-JUN-2005

If it isn't immediately obvious, you go back seven days from the last day of the month to ensure that you will have at least one of any weekday left in the month. The last step is to use the function NEXT_DAY to find the next (and last) Monday of the month:

  select next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday   from dual LAST_MONDAY ----------- 27-JUN-2005

PostgreSQL and MySQL

PostgreSQL and MySQL also share the same solution approach. The difference is in the functions that you invoke. Despite their lengths, the respective queries are extremely simple; little overhead is involved in finding the first and last Mondays of the current month.

The first step is to find the first day of the current month. The next step is to find the first Monday of the month. Since there is no function to find the next date for a given weekday, you need to use a little arithmetic. The CASE expression beginning on line 7 (of either solution) evaluates the difference between the numeric value for the weekday of the first day of the month and the numeric value corresponding to Monday. Given that the function TO_CHAR (PostgresSQL), when called with the 'D' or 'd' format, and the function DAYOFWEEK (MySQL) will return a numeric value from 1 to 7 representing days Sunday to Saturday; Monday is always represented by 2. The first test evaluated by CASE is the SIGN of the numeric value of the first day of the month (whatever it may be) minus the numeric value of Monday (2). If the result is 0, then the first day of the month falls on a Monday and that is the first Monday of the month. If the result is1, then the first day of the month falls on a Sunday and to find the first Monday of the month simply add the difference in days between 2 and 1 (numeric values of Monday and Sunday, respectively) to the first day of the month.

If you are having trouble understanding how this works, forget the weekday names and just do the math. For example, say you happen to be starting on a Tuesday and you are looking for the next Friday. When using TO_CHAR with the 'd' format, or DAYOFWEEK, Friday is 6 and Tuesday is 3. To get to 6 from 3, simply take the difference (63 = 3) and add it to the smaller value ((63) + 3 = 6). So, regardless of the actual dates, if the numeric value of the day you are starting from is less than the numeric value of the day you are searching for, adding the difference between the two dates to the date you are starting from will get you to the date you are searching for.

If the result from SIGN is 1, then the first day of the month falls between Tuesday and Saturday (inclusive). When the first day of the month has a numeric value greater than 2 (Monday), subtract from 7 the difference between the numeric value of the first day of the month and the numeric value of Monday (2), and then add that value to the first day of the month. You will have arrived at the day of the week that you are after, in this case Monday.

Again, if you are having trouble understanding how this works, forget the weekday names and just do the math. For example, suppose you want to find the next Tuesday and you are starting from Friday. Tuesday (3) is less than Friday (6). To get to 3 from 6 subtract the difference between the two values from 7 (7( |36| ) = 4) and add the result (4) to the start day Friday. (The vertical bars in |3-6| generate the absolute value of that difference.) Here, you're not adding 4 to 6 (which will give you 10), you are adding four days to Friday, which will give you the next Tuesday.

The idea behind the CASE expression is to create a sort of a "next day" function for PostgreSQL and MySQL. If you do not start with the first day of the month, the value for DY will be the value returned by CURRENT_DATE and the result of the CASE expression will return the date of the next Monday starting from the current date (unless CURRENT_DATE is a Monday, then that date will be returned).

Now that you have the first Monday of the month, add either 21 or 28 days to find the last Monday of the month. The CASE expression in lines 25 determines whether to add 21 or 28 days by checking to see whether 28 days takes you into the next month. The CASE expression does this through the following process:

It adds 28 to the value of FIRST_MONDAY.
Using either TO_CHAR (PostgreSQL) or MONTH, the CASE expression extracts the name of the current month from result of FIRST_MONDAY + 28.
The result from Step 2 is compared to the value MTH from the inline view. The value MTH is the name of the current month as derived from CURRENT_ DATE. If the two month values match, then the month is large enough for you to need to add 28 days, and the CASE expression returns FIRST_MONDAY + 28. If the two month values do not match, then you do not have room to add 28 days, and the CASE expression returns FIRST_MONDAY + 21 days instead. It is convenient that our months are such that 28 and 21 are the only two possible values you need worry about adding.

You can extend the solution by adding 7 and 14 days to find the second and third Mondays of the month, respectively.