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?

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.

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.

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

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.

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

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

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.

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

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

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