每周的第一天使用MySql。

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 July using this WEEKNO 29 as the only available parameter.

例如，在我写这篇文章的时候，我们在第29周。我想写一个MySQL查询，它将于7月18日星期日返回，使用这个星期29作为唯一可用的参数。

8 个解决方案

#1

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年的周一日期，也就是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。

#2

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

#3

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.

这是一个很好的开始，但是当你开始把它用在一个星期()函数中，除非你添加一些额外的逻辑时，它会被分解。

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!

这种方法似乎在所有的约会中都没有混乱!

#4

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' );

#5

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上测试过。

#6

Untested (I don't have MySQL at hand):

未经测试(我手头没有MySQL):

date_add(
   date_sub(curdate(), interval weekday(curdate()) day),
   interval ((NUM-weekofyear(curdate()))*7) day)

#7

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';

#8

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.

星期一是星期一，星期一是星期一。格式说明符只需要写得很小。不需要数学。

#1