How to convert number of week into date?

Question

Given a year and calendar week, how can I get the tuesday of that week as a date?

10

What flavor of 'sql?' – Sean Bright Aug 16 '11 at 13:12

score 68 · Answer 1 · edited Sep 26 '18 at 11:51

68

In MySQL the STR_TO_DATE() function can do the trick in just one line!

Example: We want to get the date of the Tuesday of the 32^th week of the year 2013.

SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W');

would output:

'2013-08-13'

I think this is the best and shortest solution to your problem.

edited Sep 26 '18 at 11:51

Nae

10,363
4
30
67

answered Aug 09 '13 at 09:19

indago

1,865
3
26
42

2

This was great @indago - exactly what I was looking for. And much more elegant than the other solutions. – Voodoo Sep 17 '13 at 01:30
1

Very elegant! When constructing the YYYYCW string for `STR_TO_DATE()` then you can use this `... STR_TO_DATE(CONCAT(YEAR(field), LPAD(WEEKOFYEAR(field, 2, '0'))..` – Peter VARGA Apr 16 '15 at 09:09
@AlBundy you can use this to extract from the current date or a particular field as you have shown above, just replace `CURDATE()` with your `date_field` like this `SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()),LPAD(WEEKOFYEAR(CURDATE()), 2, '0'),' ',DAYNAME(CURDATE())), '%X%V %W');` – indago Apr 16 '15 at 10:29
4

I find this useful to me: `STR_TO_DATE(CONCAT(YEARWEEK(field, 2), ' Sunday'), '%X%V %W')` – mtrbean May 21 '15 at 22:44
1

One may have off-by-one error with this, in order to check ensure that `STR_TO_DATE('2013 1 Monday', '%X %V %W')` is indeed first week's monday in 2013. – Nae Sep 26 '18 at 14:26
Great solution! Check [the docs](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format) for `%x%v` (ISO 8601 week number as used in Europe) and `%w` (day number instead of name). – Michel de Ruiter Feb 13 '19 at 13:20
It doesn't seem to always give the correct result. Tried `SELECT STR_TO_DATE('2020 1 Monday', '%X %V %W');` and it gives `2020-01-06` while it should give `2019-12-30`, tried `SELECT STR_TO_DATE('2020 4 Monday', '%X %V %W');` and it gives `2020-01-27` which is correct. – Christos Karapapas Aug 19 '20 at 16:10
@ChristosKarapapas , let's not just argue without facts..., the first `Monday` in the year `2020` was on `the 6th of January 2020` aka `2020-01-06` ? How can you dispute that?? – indago Aug 21 '20 at 04:38
1

@indago I am not arguing about the first Monday of a year. This of course is a fact and I am not arguing about it. But I do argue about the Monday of the first Week of a year. This, since there are weeks that do not start with a Monday, could be parameterized, to give the option, to get either a Monday, obviously the one that falls on the previous year, or null. I believe there are many methods related to time that have that logic, take for example the results of these `select week('2020-01-01', 5);` gives as a result 1 and `select week('2020-01-01', 7);` gives as a result 52. – Christos Karapapas Aug 21 '20 at 16:22

nre · Accepted Answer · 2011-08-16T13:24:26.277

24

Given you have year and cw (calender week) as variables (e.g. from a SELECT statement) you can get the DATE as following:

DATE_SUB(
  DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK),
  INTERVAL WEEKDAY(
    DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)
  ) -1 DAY),

The phrase DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK) is duplicated; did not want to store a variable. The SQL-Statement worked nicely for me on MySQL.

UPDATE: Just for clarification: WEEKDAY(DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)) will yield the first day of the week. Substracting a number from it (-1 for Tuesday; -2 for Wednesday and so forth will select a specific day in the week for you). See here.

edited Aug 16 '11 at 13:24

answered Aug 16 '11 at 13:18

nre

1,279
11
24

1

Do note that there are different standards of week numbers! The US system (week with Jan1 = week1) may be different from the ISO system (week with first Thursday of the year = week1). So make sure to test this against your intended definition of a week. – Eljakim Aug 16 '11 at 14:37
@Eljakim that is correct and actually one of the reasons why `week of year` arithmetics are somehow poorly supported by MySQL. Best is not to use them at all. In fact migration AWAY FROM `week of year` is the reason why I wrote that query once. – nre Aug 16 '11 at 15:04
1

Don't use `MAKEDATE` for this, it will construct wrong dates, especially when you use commercial dates and a calendar week number. For example `MAKEDATE(2017, 1)` will give you `Sun, 01 Jan 2017`, *while this is actually the 52nd week of 2016* and the first week of 2017 starts at Jan 2nd. Instead use this `STR_TO_DATE('2017 1 Monday', '%x %v %W')` to get the `Monday` of the correct week yielding `Mon, 02 Jan 2017` – Alexander S. Jan 09 '18 at 10:39

score 9 · Answer 3 · answered Jul 11 '12 at 00:08

The definitions of calendar week I found all said "a period of seven consecutive days starting on Sunday".

The following is MySQL specific... your mileage may vary...

DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK) adds the weeks from the 1st of the year which is not correct...

mysql> select DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK);
+----------------------------------------------+
| DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK) |
+----------------------------------------------+
| 2011-01-08                                   |
+----------------------------------------------+

By this definition, it is only meaningful to have the calendar week range from 1-53, and have this represent the Sunday of that week. As such, we would add 2 days to the nth Sunday of the year to get Tuesday.

The following gets the date of the first sunday of the year...

mysql> select date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY);
+------------------------------------------------------------------------+
| date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY) |
+------------------------------------------------------------------------+
| 2012-01-02                                                             |
+------------------------------------------------------------------------+

so this will get the date of the 10th sunday (note interval 9 week since we are already at 1)...

mysql> select date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week);
+-----------------------------------------------------------------------------------------------------+
| date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week) |
+-----------------------------------------------------------------------------------------------------+
| 2010-03-07                                                                                          |
+-----------------------------------------------------------------------------------------------------+

add 2 more days to get to tuesday...

mysql> select date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day);
+--------------------------------------------------------------------------------------------------------------------------------+
| date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day) |
+--------------------------------------------------------------------------------------------------------------------------------+
| 2010-03-09                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------+

or more generally:

select 
date_add( 
    date_add( 
        date_add('<year>-01-01', interval (8 - dayofweek('<year>-01-01')) % 7 DAY) 
        , interval <week-1> week)
    , interval <dayOfWeek> day
);

score 2 · Answer 4 · edited Sep 26 '18 at 14:36

2

In looking at indago's answer and then doing a bunch of tests, I was getting the following week as the results.

I've made a minor adjustment, and the dates then matched:

SELECT STR_TO_DATE('2019 1 Monday', '%x %v %W') -- beginning of week

SELECT STR_TO_DATE('2019 1 Sunday', '%x %v %W') -- end of week

You can compare the results with here.

edited Sep 26 '18 at 14:36

Nae

10,363
4
30
67

answered Nov 17 '15 at 16:46

Warren Landis

71
3

score 1 · Answer 5 · answered Jun 19 '19 at 07:47

DELIMITER $$
CREATE FUNCTION fn_yearweek_to_date(
    var_yearweek INTEGER UNSIGNED,
    var_weekday ENUM(
        'Monday',
        'Tuesday',
        'Wednesday',
        'Thursday',
        'Friday',
        'Saturday',
        'Sunday'
        )
    )
RETURNS DATE DETERMINISTIC
BEGIN
    RETURN STR_TO_DATE(CONCAT(var_yearweek, var_weekday), '%x%v%W');
END;
DELIMITER ;

SELECT
    fn_yearweek_to_date(YEARWEEK(NOW(), 1), 'Sunday'),
    fn_yearweek_to_date(YEARWEEK(NOW(), 1), 7)
;

score 0 · Answer 6 · answered Aug 19 '13 at 11:58

Given solutions doesn't consider, that the first week of a year may start at the end of december. So we must check, if January 1st belongs to calendarweek of old or new year:

SET @week=1;
SET @year=2014;
SET @x_weeks_after_new_year=DATE_ADD(MAKEDATE(@year, 1), INTERVAL (SELECT IF(WEEKOFYEAR(MAKEDATE(@year, 1))>50 , 0 , -1))+@week WEEK);
SELECT
  CONCAT(@year, '-', @week) WeekOfYear,
  @weekStart:=DATE_SUB(@x_weeks_after_new_year, INTERVAL WEEKDAY(@x_weeks_after_new_year) DAY) Monday,
  DATE_ADD(@weekStart, INTERVAL 6 DAY) Sunday

This will result in:

+------------+------------+------------+
| WeekOfYear |   Monday   |   Sunday   |
+------------+------------+------------+
|   2014-1   | 2013-12-30 | 2014-01-05 |
+------------+------------+------------+

score 0 · Answer 7 · answered Aug 16 '11 at 13:15

0

Well theoretically you could use DATEPART with the dw parameter to get to find the first tuesday of the month and then add 7*[CalenderWeek] to get the appropriate date

http://msdn.microsoft.com/en-us/library/ms174420.aspx

answered Aug 16 '11 at 13:15

David

1,581
1
10
22

score 0 · Answer 8 · edited Jul 01 '15 at 22:32

0

Here is a sample that might help:

SET DATEFIRST 1
declare @wk int  set @wk = 33
declare @yr int  set @yr = 2011

select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 2 -
     datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) as date

and the result is:

2011-08-16 00:00:00.000

which is today (Tuesday).

edited Jul 01 '15 at 22:32

JYelton

32,870
25
119
184

answered Aug 16 '11 at 13:32

CrazyMPh

560
1
5
7

score 0 · Answer 9 · answered Aug 16 '11 at 14:41

I think it'd be easier to write the logic of the function using php.

If you use a php script, you can put all dates in a format similar to "day-month-year" and use a loop to go through every day (from 1980s to 2038 or from your mysql dates column).

http://www.php.net/manual/en/function.date-format.php

Then use date format on the dates in that loop to convert them to the days of the week.

Here is a listing of things that can be used in date formats. http://www.php.net/manual/en/function.date.php D N l w all help you with day of the week.

score -1 · Answer 10 · answered Mar 28 '16 at 10:21

The upvoted solution worked for me in 2014 and 2015 but did not work for me in 2016 (possibly because the start of the Year is on Monday and not on Sunday.

I used the following function to correct this:

STR_TO_DATE( CONCAT(mod(day_nr + 1 ,7) , '/', week_nr, '/', year), '%w/%u/%Y')

In my data : day_nr = 0 -> Monday,

day_nr = 6 -> Sunday

So I had to fix that with a mod function

How to convert number of week into date?

10 Answers10

Linked

Related