MySQL本月的最后一个星期五 [英] last friday of the month in MySQL

问题描述

你好.如何使用mysql获取本月的最后一个星期五的日期?

Hallo. How can I get the date of last friday of current month with mysql?

提前谢谢.

编辑.您好Stefan.这就是我所做的

edit. Hi Stefan. This is what I've done

set @ldom = dayofweek(last_day(curdate()));
select 
case
when @ldom = 7 then last_day(curdate()) - interval 1 day
when @ldom = 6 then last_day(curdate())
when @ldom = 5 then last_day(curdate()) - interval 6 day 
when @ldom = 4 then last_day(curdate()) - interval 5 day 
when @ldom = 3 then last_day(curdate()) - interval 4 day
when @ldom = 2 then last_day(curdate()) - interval 3 day
else last_day(curdate()) - interval 2 day
end as last_friday

但是我想知道是否有更聪明的方法.

but I'd like to know if there is a smarter way.

编辑.我根据samplebias答案做了一些测试，以查找特定月份的上周一，周二等.

EDIT. I made some test bases on samplebias answer to find last monday,tuesday and so on of a specific month.

这些是正确的查询.

-- last sunday of month
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 6) % 7),"%Y%m%d") -- 2011-04-24

-- last saturday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 5) % 7),"%Y%m%d") -- 2011-04-30

-- last friday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 4) % 7),"%Y%m%d") -- 2011-04-29

-- last thursday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 3) % 7),"%Y%m%d") -- 2011-04-28

-- last wednesday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 2) % 7),"%Y%m%d") -- 2011-04-27

-- last tuesday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data)) - 1) % 7),"%Y%m%d") -- 2011-04-26

-- last monday
set @data = '2011-04-01'; 
select str_to_date(last_day(@data) - ((7 + weekday(last_day(@data))) % 7),"%Y%m%d") -- 2011-04-25

希望它对其他人有帮助. 再次感谢samplebias. ;)

Hope that it helps someone else. Thanks again to samplebias. ;)

推荐答案

以下是仅使用日期数学的简化版本:

Here is a simplified version using just date math:

SELECT LAST_DAY(NOW()) - ((7 + WEEKDAY(LAST_DAY(NOW())) - 4) % 7);

取决于对NOW()的求值方式(每个语句一次或两次)，您可能仍希望将其包装在函数中并将NOW()的结果存储到变量中，然后将该变量用于通话，以避免出现竞争状况，即月份在两次拨打NOW()的通话之间结转.

Depending on how NOW() gets evaluated (once or twice per statement), you might want to still wrap this in a function and store the result of NOW() into a variable, and then use the variable for the LAST_DAY(var) call, to avoid a race condition where the month rolls over between calls to NOW().

这篇关于MySQL本月的最后一个星期五的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！