在 SQL 中显示一周的开始日期

Question

我有一个SQL查询，可以返回每周的项目数。到目前为止，我有一个返回以下内容的查询：

Number of Items  |  Week Number
-------------------------------
100      |     18
80       |     19
120      |     20

并希望返回以下内容：

Number of Items  |  Week Beginning
-------------------------------
100      |     1st May 2017
80       |     8th May 2017
120      |     15th May 2017

到目前为止，我所拥有的是：

SELECT COUNT(*) AS 'Number of Items', DATEPART(WEEK, Date) FROM table
where DATEPART(Year, Date) = '2017' and DATEPART(MONTH, Date) = 5
group by DATEPART(WEEK, Date)

Answer 1

您说的是本周的第一天：

example:    select FORMAT(dateadd(ww,datediff(ww,0,getdate()),0),'dd MMM yyyy')--if you are using SQL 2012+
answer:  
SELECT COUNT(*) AS 'Number of Items', FORMAT(dateadd(ww,datediff(ww,0,date_column),0),'dd MMM yyyy')
FROM table
where DATEPART(Year, Date) = '2017' and DATEPART(MONTH, Date) = 5
group by DATEPART(WEEK, Date)

Answer 2

因为您需要星期一是一周的第一天

select  DATEPART(WEEK, MyDate),DATEADD(DAY,1,(DATEADD(DAY, 1-DATEPART(WEEKDAY, MyDate), MyDate)))
from (
select '5/3/2017' MyDate
union all select '5/10/2017'
union all select '5/14/2017')A

Answer 3

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Date) /7*7, 0) AS StartDateOfWeek

Answer 4

检查它是否解决了

DECLARE @WeekNum INT
, @YearNum char(4);
SELECT @WeekNum = 20 
, @YearNum = 2017
-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

感谢 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185440