使用 Week No 在 MySql 中获取一周的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3317980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting first day of the week in MySql using Week No
提问by davykiash
How do I get the first day of a given week whose week number is available?
如何获得周数可用的给定周的第一天？
For example as I write this post we are at WEEK 29.I would like to write a MySQL query that will return Sunday 18 Julyusing this WEEKNO 29as the only available parameter.
例如，当我写这篇文章时，我们在WEEK 29。我想编写一个 MySQL 查询，该查询将使用这个WEEKNO 29作为唯一可用参数返回7 月 18 日星期日。
回答by David Merrill
This is an accurate way of getting the first day of the week and the last day of the week based on the current date:
这是根据当前日期获取一周的第一天和一周的最后一天的准确方法：
adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY) WeekStart,
adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY) WeekEnd
回答by dcp
You can use:
您可以使用：
SELECT STR_TO_DATE('201003 Monday', '%X%V %W');
This would give you the Monday date of week 3 of 2010, which would be 2010-01-18.
这将为您提供 2010 年第 3 周的星期一日期，即 2010-01-18。
Another example:
另一个例子：
SELECT STR_TO_DATE('201052 Sunday', '%X%V %W');
Would give you the Sunday date of week 52 of 2010, which would be 2010-12-26.
将为您提供 2010 年第 52 周的星期日日期，即 2010-12-26。
And finally, using your original example:
最后，使用您的原始示例：
SELECT STR_TO_DATE('201029 Sunday', '%X%V %W');
This gives 2010-07-18.
这给出了 2010-07-18。
回答by Jonathan
The answer most liked up to now on this board looks like this in its basic form:
到目前为止，该板上最受欢迎的答案的基本形式如下：
SELECT STR_TO_DATE('201003 Monday', '%X%V %W');
This is a good answer to start with, but it breaks down on some days when you start to put it to use in conjuction with the week() function unless you add some additional logic.
这是一个很好的开始答案，但是当您开始将它与 week() 函数结合使用时，它会在某些日子崩溃，除非您添加一些额外的逻辑。
Here is a long, messy version of the same thing, but which seems to work on all days (BTW the current date is built into this asnwer):
这是同一件事的一个长而凌乱的版本，但似乎在所有日子都有效（顺便说一句，当前日期已内置在此 asnwer 中）：
SELECT STR_TO_DATE(
(IF( CAST(WEEK(NOW(),0) AS UNSIGNED) = 0,
(CONCAT(
CAST((CAST(YEAR(NOW()) AS UNSIGNED) - 1) AS CHAR),
'52 Sunday')),
(CONCAT(
CAST(YEAR(NOW()) AS CHAR),
IF( CAST(WEEK(NOW(),0) AS UNSIGNED) < 10,'0','' ),
CAST(WEEK(NOW(),0) AS CHAR),
' Sunday')))),
'%X%V %W');
This mess handles the problems that arise when the year rolls over on certain days of the week. For instance 2011 started on a Saturday, so the Sunday that started the week was in the prior year. Here's the select with hard coded examples:
这个混乱处理了当一年在一周中的某些日子滚动时出现的问题。例如，2011 年从星期六开始，因此开始这一周的星期日是上一年。这是带有硬编码示例的选择：
SELECT STR_TO_DATE(
(IF( CAST(WEEK('2011-01-01',0) AS UNSIGNED) = 0,
(CONCAT(
CAST((CAST(YEAR('2011-01-01') AS UNSIGNED) - 1) AS CHAR),
'52 Sunday')),
(CONCAT(
CAST(YEAR('2011-01-01') AS CHAR),
IF( CAST(WEEK('2011-01-01',0) AS UNSIGNED) < 10,'0','' ),
CAST(WEEK('2011-01-01',0) AS CHAR),
' Sunday')))),
'%X%V %W');
YEILDS >> '2010-12-26'
产量 >> '2010-12-26'
SELECT STR_TO_DATE(
(IF( CAST(WEEK('2011-01-02',0) AS UNSIGNED) = 0,
(CONCAT(
CAST((CAST(YEAR('2011-01-02') AS UNSIGNED) - 1) AS CHAR),
'52 Sunday')),
(CONCAT(
CAST(YEAR('2011-01-02') AS CHAR),
IF( CAST(WEEK('2011-01-02',0) AS UNSIGNED) < 10,'0','' ),
CAST(WEEK('2011-01-02',0) AS CHAR),
' Sunday')))),
'%X%V %W');
YEILDS >> '2011-01-02'
产量 >> '2011-01-02'
All that said, I like the other asnwer posted that looks like this
综上所述，我喜欢另一个看起来像这样的回答
SELECT
adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY) WeekStart,
adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY) WeekEnd;
This method seems to work just as well on all dates without the mess!
这种方法似乎在所有日期都一样有效，不会造成混乱！
回答by Sachin K
This can be the simplest and dynamic way for it. Use the following code.
这可能是最简单和动态的方式。使用以下代码。
SELECT STR_TO_DATE( concat( concat( date_format( CURDATE( ) , '%Y' ) , WEEKOFYEAR( CURDATE( ) ) ) , ' Monday' ) , '%X%V %W' );
回答by crab
If your week-start is Sunday and week-end is Saturday, use this one:
如果您的一周开始是周日，周末是周六，请使用以下选项：
SELECT
DATE_ADD(CURDATE(), INTERVAL (MOD(DAYOFWEEK(CURDATE())-1, 7)*-1) DAY) AS week_start,
DATE_ADD(CURDATE(), INTERVAL ((MOD(DAYOFWEEK(CURDATE())-1, 7)*-1)+6) DAY) AS week_end
Tested on MySQL.
在 MySQL 上测试。
回答by Dave Pl?ger
An addition to dcp's answer:
dcp 答案的补充：
SELECT STR_TO_DATE('201553 Monday', '%x%v %W')
Will give you the monday when your start of the week is monday. The format specifiers just have to be written small. No math needed.
当您一周的开始是星期一时，会给您星期一。格式说明符只需写得很小。不需要数学。
回答by pascal
Untested (I don't have MySQL at hand):
未经测试（我手头没有 MySQL）：
date_add(
date_sub(curdate(), interval weekday(curdate()) day),
interval ((NUM-weekofyear(curdate()))*7) day)
回答by Pierre
SELECT CONCAT(RIGHT(STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W'),2),'-',
MID(STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W'),6,2),'-',
LEFT(STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W'),4)) AS 'Lundi',
CONCAT(RIGHT(STR_TO_DATE(CONCAT(YEAR(NOW()), week(now(),3),'Sunday'), '%X%V %W'),2),'-',
MID(STR_TO_DATE(CONCAT(YEAR(NOW()), week(now(),3),'Sunday'), '%X%V %W'),6,2),'-',
LEFT(STR_TO_DATE(CONCAT(YEAR(NOW()), week(now(),3),'Sunday'), '%X%V %W'),4)) AS 'Dimanche';
