Is there a way to get the First and Last weekday of a month?

Question

I would like to ask if there is a smart or easy way to get the first and last weekday of a month in MySQL. In other way, I want to avoid Weekends and shift first or last day properly.

For example:

For the period: 2011-05-01 till 2011-05-31
First weekday should be: 2011-05-02 and not 2011-05-01 as 2011-05-01 is Sunday.
Last weekday should be: 2011-05-31 as it is Tuesday.

For the period: 2011-04-01 till 2011-04-30,
First weekday: 2011-04-01
Last weekday: 2011-04-29

Answer 1

How about defining functions FIRST_WDOM() (week/work day of month) and LAST_WDOM() in the style of LAST_DAY()?

DELIMITER //
CREATE FUNCTION first_wdom (d DATETIME) -- First work/week day of month
RETURNS DATE DETERMINISTIC
BEGIN
  DECLARE first_dom DATE;
  SET first_dom = DATE_FORMAT(d, '%Y-%m-01');

  RETURN first_dom + INTERVAL (CASE DAYOFWEEK(first_dom)
                               WHEN 1 THEN 1
                               WHEN 7 THEN 2
                               ELSE 0
                               END) DAY;
END //
CREATE FUNCTION last_wdom (d DATETIME) -- Last work/week day of month
RETURNS DATE DETERMINISTIC
BEGIN
  DECLARE last_dom DATE;
  SET last_dom = LAST_DAY(d);

  RETURN last_dom - INTERVAL (CASE DAYOFWEEK(last_dom)
                              WHEN 1 THEN 2
                              WHEN 7 THEN 1
                              ELSE 0
                              END) DAY;
END //
DELIMITER ;

mysql> SELECT FIRST_WDOM('2011-05-10'), LAST_WDOM('2011-05-10');
+--------------------------+-------------------------+
| FIRST_WDOM('2011-05-10') | LAST_WDOM('2011-05-10') |
+--------------------------+-------------------------+
| 2011-05-02               | 2011-05-31              | 
+--------------------------+-------------------------+
1 row in set (0.01 sec)

This is merely an adaptation of ic3b3rg's answer, and assumes that Saturday and Sunday are your weekend days. I use DATETIME, rather than DATE, as the input type to avoid truncation warnings when passing in, say, NOW().

Answer 2

I think this will do what you ask

SELECT
  CASE DAYOFWEEK(date1)
    WHEN 1 THEN DATE_ADD(date1, INTERVAL 1 DAY)
    WHEN 7 THEN DATE_ADD(date1, INTERVAL 2 DAY)
    ELSE date1 END AS dateStart,
  CASE DAYOFWEEK(date2)
    WHEN 1 THEN DATE_ADD(date2, INTERVAL -2 DAY)
    WHEN 7 THEN DATE_ADD(date2, INTERVAL -1 DAY)
    ELSE date2 END AS dateEnd
FROM myTable

Answer 3

Create a table called tblOffset

tblOffset
dayofweek offset
1         1
2         0
3         0
4         0
5         0
6         0
7         2

Then, to get the first working day after a given date (in my example 2011-01-01), use:

SELECT DATE_ADD('2011-01-01', INTERVAL `offset` DAY), from tblOffset where `dayofweek` = DAYOFWEEK('2011-01-01')

EDIT: This does not take into consideration holidays. But therefore you would need to have a seperate table with all the holidays for your customers locales, religions, etc.