Getting first day of the week in MySql using Week No

How do I ge开发者_开发百科t 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.

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

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.

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.

And finally, using your original example:

SELECT STR_TO_DATE('201029 Sunday', '%X%V %W');

This gives 2010-07-18.

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):

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:

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'

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'

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!

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

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.

An addition to dcp's answer:

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.

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

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

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

Tested with above solution. I ended up to used this:

STR_TO_DATE( concat( YEARWEEK("2012-12-31", 3) , ' Monday' ) , '%x%v %W' )

Some points:

• Use '%x%v %W' for ISO year and week
• Use YEARWEEK('your date',3) with 3 on second argument for ISO mode (start on Monday)

Tested with below dates to find first Monday:

• 2012-12-31 --> 2012-12-31
• 2014-01-07 --> 2014-01-06