泛舟湖上清波郎朗
我发现日历周的定义都说“从星期日开始连续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 |+----------------------------------------------+根据此定义,只有日历周的范围为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 |+------------------------------------------------------------------------+因此,它将获得第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 |+-----------------------------------------------------------------------------------------------------+再增加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 |+--------------------------------------------------------------------------------------------------------------------------------+或更一般而言:select date_add( date_add( date_add('-01-01', interval (8 - dayofweek('-01-01')) % 7 DAY) , interval week) , interval day);