CODEWITHSUNDEEP
mysqlsqldatetime
Cephalopod
Cephalopod

Reputation: 15145

How to convert number of week into date?

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

Upvotes: 29

Views: 50631

Answers (10)

Nae
Nae

Reputation: 15335

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

Upvotes: 1

Warren Landis
Warren Landis

Reputation: 81

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.

Upvotes: 3

indago
indago

Reputation: 2101

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 32th 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.

Upvotes: 73

user1007717
user1007717

Reputation: 11

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

Upvotes: 0

CrazyMPh
CrazyMPh

Reputation: 580

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

Upvotes: 0

Date-Shark
Date-Shark

Reputation: 9

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 |
+------------+------------+------------+

Upvotes: 0

Ethan Joffe
Ethan Joffe

Reputation: 121

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

Upvotes: 9

obesechicken13
obesechicken13

Reputation: 833

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.

Upvotes: 0

nre
nre

Reputation: 1299

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.

Upvotes: 24

David
David

Reputation: 1611

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

Upvotes: 0

Related Questions