Get the first and last working day date of some month in MySQL

Question

It is very similar to Get the first and last date of next month in MySQL, but I want to Get the first and last working day date of some month in MySQL. Here, working day means simply Monday ~ Friday, or DAYOFWEEK(date) >= 2 and <= 6. Let's not think about holiday.

For example (I chose Feb as it should be most complicated due to leap)

2014-02 -> 2014-02-03 (Mon) ~ 2014-02-28 (Fri)
2015-02 -> 2015-02-02 (Mon) ~ 2015-02-27 (Fri)
2016-02 -> 2016-02-01 (Mon) ~ 2016-02-29 (Mon)

The most obvious question is: What constitutes a working day to you? They are not the same the world over — RiggsFolly, Jan 28 '16 at 00:13

score 1 · Accepted Answer · answered Jan 28 '16 at 01:32

1

E.g.:

SELECT MIN(dt),MAX(dt) FROM calendar WHERE DAYOFWEEK (dt) NOT IN(1,7) AND dt BETWEEN '2016-01-01' AND '2016-01-31';

answered Jan 28 '16 at 01:32

Strawberry

33,750
13
40
57

1

This is also a clever way - however make sure no day missing in table calendar – SIDU Jan 28 '16 at 01:58
Yes. Calendar is a utility table with all dates. In practice, MySQL might not be the best tool for this. Rather solve this one in your application code, without the database at all. – Strawberry Jan 28 '16 at 07:36

shawnt00 · Answer 2 · 2016-01-29T16:03:54.140

Here's some pseudo-code to start thinking about the problem. For the start (end) of month check for a weekday and advance (back up) one day if not. Repeat the test and possibly jump forward (backward) again. You'll never have to move more than twice.

/* <som> = start of month */
case
    when not (dayofweek(<som>) between 2 and 6)
    then
        case
            when not (dayofweek(<som> + 1) between 2 and 6)
            then <som> + 2
            else <som> + 1
        end
    else <som>
end

/* <eom> = end of month */
case
    when not (dayofweek(<eom>) between 2 and 6)
    then
        case
            when not (dayofweek(<eom> - 1) between 2 and 6)
            then <eom> - 2
            else <eom> - 1
        end
    else <eom>
end

I'm not a MySQL authority but you can use these substitutions I believe:

<som> -> date_sub(<dt>, interval dayofmonth(<dt>) - 1 day)
<eom> => last_day(<dt>)

There are different ways to rewrite this that are equivalent. The approach above was probably the way many would think about the problem initially but it's actually simpler than that. Since once I know which weekend day a date falls on I also know how many days until the next (previous) weekday. This can be handled as three easy cases:

case dayofweek(<som>)
    when 7 then <som> + 2
    when 1 then <som> + 1
    else <som>
end

case dayofweek(<eom>)
    when 7 then <eom> - 1
    when 1 then <eom> - 2
    else <eom>
end

score -1 · Answer 3 · answered Jan 28 '16 at 00:37

Feed day1 with first day of any month:

SELECT
if (
    (@w1 := dayofweek(@day1 := '2016-05-01')) = 1,
    adddate(@day1, interval 1 day), 
    if(@w1 = 7, adddate(@day1, interval 2 day), @day1)
) day_one,
if (
    (@w2 := dayofweek(@day2 := last_day(@day1))) = 1,
    adddate(@day2, interval -2 day), 
    if(@w2 = 7, adddate(@day2,interval -1 day), @day2)
) day_last

Brief explanation:

If first day is Sunday, add one day
If first day is Saturday, add two days

Give any day of a month, and get last day of the month,

If last day is Sunday, minus two days
If last day is Saturday, minus one day

Get the first and last working day date of some month in MySQL

3 Answers3