如何将周数转换为日期？

Question

给定一年和一个日历周，我如何获得该周的星期二作为日期？

Answer 1

给定您year和cw（日历周）作为变量（例如，从SELECT语句中获取），您可以通过以下方式获取DATE：DATE_SUB(  DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK),  INTERVAL WEEKDAY(    DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)  ) -1 DAY),该短语DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)重复；不想存储变量。SQL语句在MySQL上对我来说效果很好。更新：只是为了澄清：WEEKDAY(DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK))将产生一周的第一天。从中减去一个数字（星期二为-1；星期三为-2，依此类推，将为您选择一周中的特定日期）。

Answer 2

在MySQL中，该STR_TO_DATE()函数仅需一行即可完成操作！例如：我们希望得到的日期Tuesday的中32日年度的一周2013。SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W');将输出：'2013-08-13'

Answer 3

我发现日历周的定义都说“从星期日开始连续7天。”以下是MySQL特有的...您的里程可能会有所不同...DATE_ADD（MAKEDATE（year，1），INTERVAL cw WEEK）添加了从一年的第一天开始的几周，这是不正确的...mysql> select DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK);+----------------------------------------------+| DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK) |+----------------------------------------------+| 2011-01-08&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|+----------------------------------------------+根据此定义，只有日历周的范围为1到53，并代表该周的星期日才有意义。因此，我们将在今年的第n个星期日增加2天以得到星期二。以下获取一年中第一个星期日的日期...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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|+------------------------------------------------------------------------+因此，它将获得第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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |+-----------------------------------------------------------------------------------------------------+再增加2天才能到达星期二...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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|+--------------------------------------------------------------------------------------------------------------------------------+或更一般而言：select&nbsp;date_add(&nbsp;&nbsp; &nbsp; date_add(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; date_add('<year>-01-01', interval (8 - dayofweek('<year>-01-01')) % 7 DAY)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; , interval <week-1> week)&nbsp; &nbsp; , interval <dayOfWeek> day);