Is there any way to get the first and last weekday?

Question

I would like to ask if there is a smart or easy way to get the first and last weekday in MySQL. Otherwise, I want to avoid days off and correctly change the first or last day.

For instance:

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 to define functions FIRST_WDOM()(week / workday of the month) and LAST_WDOM()in style 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 just an adaptation of ic3b3rg answer , and assumes Saturday and Sunday are days off. I use a DATETIME, rather than on DATE, as an input type, to avoid truncation warnings when passing, say NOW().

Answer 2

, ,

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

tblOffset

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

, ( 2011-01-01), :

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

EDIT: . , , , ..

Answer 4

, ,

select min(cal_date) first_weekday
from calendar
where cal_date >= '2011-05-01'
  and day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')

50 . 68 .

select max(cal_date) 
from calendar
where cal_date < '2011-06-01'
  and day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')

, , , , .

, dbms. - SQL : ", ".

Answer 5

( ):

select  
 month(current_day) as the_month,
 case
    when dayofweek(days.first_day) = 7 then date_add(days.first_day, interval 2 day)
    when dayofweek(days.first_day) = 1 then date_add(days.first_day, interval 1 day)
    else days.first_day
  end as first_weekday,
  case
    when dayofweek(days.last_day) = 7 then date_sub(days.last_day, interval 1 day)
    when dayofweek(days.last_day) = 1 then date_sub(days.last_day, interval 2 day)
    else days.last_day
  end as last_weekday
from
  ( select
      curdate() as current_day,
      concat(date_format(LAST_DAY(curdate()),'%Y-%m-'),'01') as first_day,  
      LAST_DAY(curdate()) as last_day 
  ) days

, , (, 1 1900 31 2199 ), lookup table ( ).

Answer 6

- , , , 0 , ​​ , . , 1970 2100 :)

, ddFirstWorkDay ddLastWorkDay, , "1" . , . , , .

You can then create a query along the lines of the following to get the dates you are interested in.

SELECT ddDate from dimDates
WHERE ddDate BETWEEN 'start date' AND 'end date'
   AND ddFirstWorkDay = 1
ORDER BY ddDate
LIMIT 1

SELECT ddDate from dimDates
WHERE ddDate BETWEEN 'start date' AND 'end date'
   AND ddLastWorkDay = 1
ORDER BY ddDate DESC
LIMIT 1

I think this will give you what you need, although you need to execute two queries for each date range being tested, the table is small, so you need to return very quickly.

You may find the dimDates table method interesting for other uses, so I would look .. :)