Getting first day of the week in MySql using Week No

Question

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.

score 49 · Answer 1 · answered May 15 '12 at 22:59

49

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

answered May 15 '12 at 22:59

David Merrill

499
4
2

9

I think this solution might be affected by the system locale, I use this instead `adddate(curdate(), INTERVAL -WEEKDAY(curdate()) DAY) WeekStart` – Snow Blind Jul 31 '14 at 07:58
1

@SnowBlind, thank you for your comment. ...and if someone is looking for Friday as last day of week (i.e. last Workday), then try this: adddate(curdate(), INTERVAL 4-WEEKDAY(curdate()) DAY) SHOULD_BE_Friday – Peter Aug 27 '15 at 19:36
This answer is all good, but didn't this question request for a solution when what you were given is: WEEK NUMBER/ YEARWEEK? This answer really only applies to a case where you have a `date`, or are working with `current_date`. – Ifedi Okonkwo Apr 21 '18 at 08:51

dcp · Accepted Answer · 2010-07-23T12:44:33.507

47

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.

edited Jul 23 '10 at 12:44

answered Jul 23 '10 at 12:36

dcp

54,410
22
144
164

4

When using week mode 3 (ISO standard, qv http://stackoverflow.com/questions/11788885/), then SELECT STR_TO_DATE('201003 Monday', '%X%V %W') will give Monday of the following week! – ChrisV Feb 27 '13 at 13:24
1

I think it is not correct. Try to run SELECT STR_TO_DATE( '201402 Sunday', '%X%V %W' ) , STR_TO_DATE( '201402 Monday', '%X%V %W' ) - you'll see that it is not sunday of 2nd week and monday of 2nd week - it is second sunday and 2nd monday of YEAR – 1nstinct Dec 29 '14 at 14:29
1

@ChrisV: My solution for that is to substract 1 week after conversion: ```DATE_SUB(STR_TO_DATE(CONCAT(YEARWEEK('201402 Sunday', 3), ' Monday'), '%X%V %W'), INTERVAL 1 WEEK)``` – chrpes Jan 12 '15 at 11:18
1

@chrpes: I think that STR_TO_DATE( CONCAT(YEAR(CURDATE()), WEEK(CURDATE()) + 1, ' Sunday'), '%X%V %W') week_end is a little bit readeable – Vasiliy Toporov Apr 13 '15 at 10:18
2

@chrpes You may need to use, %x%v instead of %X%V to get your desired result. Because `%X%V` use Sunday as the start day of the month, `%x%v` use Monday as start day of the month.. – Stranger Feb 02 '18 at 10:47

Jonathan · Answer 3 · 2012-08-06T15:27:58.347

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!

Couldn't you make this much simpler by using a mode of 2? YEARWEEK('2011-01-01', 2) reports the 52nd week of 2010, as opposed to mode 0, which returns the 0th week of 2011. — user1618143, Feb 14 '13 at 17:20

score 4 · Answer 4 · edited Feb 15 '16 at 06:44

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

edited Feb 15 '16 at 06:44

Spidy

1,137
3
28
48

answered Feb 15 '16 at 06:04

Sachin K

41
5

score 2 · Answer 5 · answered Feb 12 '13 at 05:43

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.

score 1 · Answer 6 · answered Jan 04 '16 at 12:59

1

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.

answered Jan 04 '16 at 12:59

Dave Pløger

11
1

score 0 · Answer 7 · edited Mar 13 '13 at 10:05

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

score 0 · Answer 8 · answered Jul 23 '10 at 12:32

0

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

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

answered Jul 23 '10 at 12:32

pascal

3,287
1
17
35

score 0 · Answer 9 · answered Jun 03 '20 at 10:11

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

Getting first day of the week in MySql using Week No

9 Answers9

Linked