mysql获取周日期范围_MySql在特定周内返回日期范围

我不是MySQL人，所以我不得不尝试从SQL Server函数转换。但是，基本的数学就在那里。我会告诉你如何传递或计算@YEARSTART和@YEAREND。

DECLARE @YEARSTART datetime, @YEAREND datatime

SET @YEARSTART='1/1/2012'

SET @YEAREND = '12/31/2012'

SELECT A.week, CASE WHEN (@YEARSTART + INTERVAL (8-DAYOFWEEK(@STARTYEAR)+(week-2)*7) DAY) < @YEARSTART

THEN @YEARSTART

ELSE (@YEARSTART + INTERVAL (8-DAYOFWEEK(@STARTYEAR)+(week-2)*7) DAY)

END AS WeekStart,

CASE WHEN (@YEARSTART + INTERVAL (7-DAYOFWEEK(@STARTYEAR)+(week-1)*7) DAY) > @YEAREND

THEN @YEAREND

ELSE (@YEARSTART + INTERVAL (7-DAYOFWEEK(@STARTYEAR)+(week-1)*7) DAY)

END AS WeekEnd,

A.itemcode, A.SumQty

FROM

(

select week(real_pur_date) as week, itemcode, sum(quantity) AS SumQty

from sales

where week(real_pur_date) = week(20120620)

group by itemcode

) AS A