sql - 给定周数，在 T-SQL 中返回一周的第一天

Question

这将给我一个基于日期的周数:

SELECT DATEPART(wk, '7/27/2016') AS [Week]

例如，返回 31。

现在，我需要做的是找到那个星期的第一天，并以短日期格式返回它。例如:

Given Week: 31
Return First Day of Week: July 24

或者

Given Week: 52
Return First Day of Week: Dec 25

我相信一周的默认第一天是星期日，这就是我需要的日期。

我在这里看到了几篇接近的帖子，但没有一篇让我一路走下去。

谢谢!

Answer 1

我帮助从内到外阅读这篇文章。我添加了带编号的评论以提供帮助。

declare @weekNum int;set @weeknum = 52;
select 
-- 3.  Add number of weeks
dateadd(wk, @weekNum, 
    --2.  first day of week 0 for that year (may belong to previous year)
    dateadd(ww, datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (date 0) was a Monday, and adding weeks to a Monday results in a Monday.
)

我们可以结合第二步和第三步，因为它们都会增加周数:

declare @weekNum int;set @weeknum = 52;
select 
    --2.  first day of week 0 for that year (may belong to previous year) + number of weeks
    dateadd(ww, @weekNum + datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (day 0) was a Monday. Adding weeks to a Monday results in a Monday

另外，我认为你的第 31 周的例子比现在晚了一周。您可以像这样查看当年的完整集合:

with weeks as 
(
    select top 52 row_number() over (order by  object_id) as wk  from sys.objects
)
select wk,
    --2.  first day of week 0 for that year (may belong to previous year) + number of weeks
    dateadd(ww, wk + datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (day 0) was a Monday. Adding weeks to a Monday results in a Monday
from weeks