mysql获取下一个工作日_mysql – 有没有办法获得一个月的第一个和最后一个工作日？...

如何定义函数FIRST_WDOM()(周/工作日)和LAST_WDOM()的样式为

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)

这仅仅是ic3b3rg’s answer的改编版,并假设周六和周日是你的周末日.我使用DATETIME而不是DATE作为输入类型,以避免在传入NOW()时发生截断警告.