如何将周数转换为日期?

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

给定一年和日历周，我怎样才能把那个星期的星期二作为一个日期呢?

9 个解决方案

#1

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.

重复短语DATE_ADD(MAKEDATE, year, 1)， INTERVAL cw WEEK;不想存储变量。sql语句在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.

更新:澄清一下:工作日(DATE_ADD(MAKEDATE，年，1)，间隔cw周)将产生一周的第一天。从它减去一个数字(周二为-1;-2周三和其他时间，将为你选择一个特定的一天。在这里看到的。

#2

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.

在MySQL中，STR_TO_DATE()函数可以在一行中执行这个技巧!我们想知道2013年第32周的星期二的日期。

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

would output:

将输出:

'2013-08-13'

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

我认为这是解决你问题的最好、最短的办法。

#3

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

下面是MySQL特有的…你的情况可能不同……

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

DATE_ADD(MAKEDATE(year, 1)， INTERVAL cw WEEK)从一年的第1周添加了不正确的周。

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.

根据这个定义，公历周从1到53是有意义的，并且这个公历周代表那个星期的星期天。因此，我们会在每年的第n个星期天增加2天，以得到星期二。

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

所以这将得到第10个星期天的日期(注意间隔9周，因为我们已经是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
);

#4

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

理论上，你可以使用带有dw参数的DATEPART找到本月的第一个星期二，然后添加7*[CalenderWeek]来得到合适的日期

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

#5

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

我认为使用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).

如果您使用php脚本，您可以将所有日期以类似于“day-month-year”的格式放置，并使用循环来完成每天的工作(从1980年到2038年或从mysql dates列)。

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.

这里列出了可以在日期格式中使用的东西。http://www.php.net/manual/en/function.date.php D nlw都可以帮助你度过一周中的一天。

#6

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:

如果不考虑解决方案，一年的第一周可能从12月底开始。所以我们必须检查，1月1日是否属于旧年或新年的日历周:

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

#7

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

这是今天(星期二)。

#8

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

在查看indago的答案并做了一系列测试之后，我得到了接下来一周的结果。

I made a minor adjustment, and the dates then matched.

我做了一个小的调整，然后日期就匹配了。

STR_TO_DATE('#{year_week} Monday', '%x%v %W') - beginning of week

STR_TO_DATE('#{year_week}周一'，'%x% v% W') -开始一周。

STR_TO_DATE('#{year_week} Sunday', '%x%v %W') - end of week

STR_TO_DATE('#{year_week} Sunday'， '%x%v %W') -周末

You can compare the results to here http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php?year=2015

您可以将结果与http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php?year=2015进行比较

#9

-1

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.

在2014年和2015年，这个向上投票的解决方案对我起了作用，但在2016年对我不起作用(可能是因为年初是周一，而不是周日)。

I used the following function to correct this:

我用下面的函数来修正这个问题:

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

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

In my data : day_nr = 0 -> Monday,

在我的数据中:day_nr = 0 ->周一，

day_nr = 6 -> Sunday

day_nr = 6 ->周日

So I had to fix that with a mod function

所以我需要用一个模函数来修正它

#1