如何在存储过程中将日期时间转换为周范围以进行分组

Question

例如

Date | Name
1/1/13 | John
1/3/13 | Mary
1/5/13 | Joe
1/8/13 | Mary
1/9/13 | Bob

使用存储过程在生成的记录集中创建其他列"周范围"，如下所示：

Date | Name | Week Range
1/1/13 | John |  12/31/2012 to 01/06/13
1/3/13 | Mary |  12/31/2012 to 01/06/13
1/5/13 | Joev |  12/31/2012 to 01/06/13
1/8/13 | Mary |  01/07/2013 to 01/13/13
1/9/13 | Bob  |  01/07/2013 to 01/13/13

我需要这样做才能按周分组并在具有正确系列标签的图表中显示。

Answer 1

SELECT [Date],
       [Name],
       CONVERT(VARCHAR(10),DATEADD(day,1-DATEPART(weekday,[Date]),[Date]),3)
       + ' to '
       + CONVERT(VARCHAR(10),DATEADD(day,7,DATEADD(day,1-DATEPART(weekday,[Date]),[Date])),3)
FROM   Table

Answer 2

请尝试以下查询。我使用"出生日期"作为"日期"列。

SELECT   BirthDate,Name,
    CONVERT(VARCHAR(10), DATEADD(dd, -(DATEPART(dw, BirthDate)-1), BirthDate))  + ' to ' + 
    CONVERT(VARCHAR(10),DATEADD(dd, 7-(DATEPART(dw, BirthDate)), BirthDate)) WeekRange    
FROM Employee
group BY BirthDate, DATEADD(dd, -(DATEPART(dw, BirthDate)-1), BirthDate),DATEADD(dd, 7-(DATEPART(dw, BirthDate)), BirthDate),FirstName
ORDER BY BirthDate