MySQL将YEARWEEK转换为日期 [英] MySQL convert YEARWEEK to date
问题描述
我有一个从MySQL转换为YEARWEEK()的值
I've a YEARWEEK() converted value from MySQL
201439
我想使用MySQL将其转换回该周编号的开始日期吗?
I want to convert it back to the starting date of that week number, using MySQL, possible?
例如
SELECT xxx('201439');
并返回
'2014-09-16'
推荐答案
您需要注意,年份周处于预期的模式". (请参见 https://dev.mysql .com/doc/refman/5.7/en/date-and-time-functions.html#function_week )
You need to be careful that the yearweek is in the expected "mode". (See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week)
STR_TO_DATE中使用的格式应匹配. (请参见 https://dev .mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format )
The formatting used in the STR_TO_DATE should match. (See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)
例如如果使用模式2(1-53,则第1周是其中包含星期日的第一周,几周从星期日开始)
e.g. If using mode 2 (1-53, Week 1 is the first week with a Sunday in it, weeks start on a Sunday)
SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W');
例如如果使用模式3(遵循ISO-8601)(1-53,第1周是具有4天或更多天的第一周,从星期一开始),则需要使用小写版本.
e.g. If using mode 3 (following ISO-8601), (1-53, Week 1 is the first week with 4 or more days, starting on a Monday), you need to use the lower-case version.
SELECT STR_TO_DATE(CONCAT('201439',' Monday'), '%x%v %W');
因此,您将获得以下信息(2014-09-28是星期日):
So, one would get the following (2014-09-28 is a Sunday):
SELECT yearweek('2014-09-28', 2);
201439
SELECT yearweek('2014-09-28', 3);
201439
SELECT yearweek('2014-09-29', 2);
201439
SELECT yearweek('2014-09-29', 3);
201440
然后
SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W'); -- mode 2
2014-09-28
SELECT STR_TO_DATE(CONCAT('201439',' Monday'), '%x%v %W'); -- mode 3
2014-09-22
SELECT STR_TO_DATE(CONCAT('201440',' Monday'), '%x%v %W'); -- mode 3
2014-09-29
这篇关于MySQL将YEARWEEK转换为日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!