以星期，星期数，月和年获取日期

Question

如果您只有MySQL的星期几，星期数，月和年，有某种方法可以获取日期？

例子：

我想知道这一天是这个参数：

• 年时间：2014年
• 月：Setember（09）
• 年的周数：37或周数量在9月：3
• 星期几：星期四

答案是“2014-09-18”

Answer 1

我问了同样的问题，在葡萄牙斯托克溢出，他们找到了一个简单的解决方案。

使用str_to_date，年，星期数和星期几。

%Y Year, numeric, four digits 
%U Week (00..53), where Sunday is the first day of the week 
%W Weekday name (Sunday..Saturday) 

SELECT str_to_date('201437 Thursday', '%Y%U %W'); 

结果：

2014-09-18 00:00:00 

葡萄牙堆栈溢出答案链接：https://pt.stackoverflow.com/questions/33046/obter-data-com-dia-da-semana-n%C3%BAmero-da-semana-m%C3%AAs-e-ano/33063#33063

感谢大家谁帮我

Answer 2

使用Barmars建议你可以建立在运行一年的日历，并检查其对你的约束条件有关：

SET @year := 2014;  -- set the year of the constraints 
SET @week := 37;  --  the week 
SET @day_of_week := 5; --  the day of the week (numerical) 

-- build the first of the wanted year as supposed by Barmar 
SET @first_of_year = STR_TO_DATE(CONCAT(@year, '-01-01'), '%Y-%m-%d'); 

SELECT 
    @first_of_year + INTERVAL t.n DAY the_date 
FROM (
    SELECT 
     a.N + b.N * 10 + c.N * 100 AS n 
    FROM 
     (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a 
     ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b 
     ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c 
    ORDER BY n 
) t 
WHERE 
    t.n < TIMESTAMPDIFF(DAY, @first_of_year, @first_of_year + INTERVAL 1 YEAR) 
AND 
    WEEK(@first_of_year + INTERVAL t.n DAY) = @week 
AND 
    DAYOFWEEK(@first_of_year + INTERVAL t.n DAY) = @day_of_week 
; 

Demo

注意
UNION生成从0到399的数字，所以我们可以生成一年的日历。现在我们可以应用您的其他约束，比如每周和每周的一周。