SQL Server - 给定周数，获取一周中的第一个日期？

Question

我有一个查询（用于 bug tracker.net），可以按状态计算每周的 bug 数量。但查询返回周数，我真正想要的是一周的第一个日期

select datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date)))
       as [week], bg_status , st_name as [status], count(*) as [count] 
  from bugs inner join statuses on bg_status = st_id 
 group by datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))),
          bg_status, st_name
 order by [week], bg_status

获取周数的部分是

datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week]

它返回此输出：

week        bg_status   status                                        count
----------- ----------- --------------------------------------------- ------
22          1           new                                           1
22          5           closed                                        32

但最好说每周的第一个日期，例如01-01 -2010，然后 08-01-2010 等

问题不是 如何从 SQL Server 中的周数获取“周开始日期”和“周结束日期”？（答案说明如何从日期而不是周数开始周）

不是 从周数计算日期的重复项 （问题要求使用 c#）

不与 获取第一个重复提供的日期中的星期几（问题要求使用javascript）

我进行了搜索，但找不到针对 SQL Server 的此问题答案（2010 年，如果重要的话）

Answer 1

如果你以正确的方式思考它，答案 SO 1267126 可以应用于您的问题。

组中的每个错误报告日期都映射到同一周。因此，根据定义，每个错误日期也必须映射到同一周的开始。因此，您对错误报告日期运行“从给定日期开始的一周”计算以及周数计算，并按两个（适度可怕的）表达式进行分组，最后得到您寻求的答案。

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date)
       AS [weekstart], bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date),
       bg_status, st_name
 ORDER BY [week], bg_status

---

由于 bg_reported_date 是一个 DATETIME （请参阅注释；它包含时间部分），因此有必要在确定周开始之前将其转换为 DATE （但周数表达式不需要转换） ，并且一周开始表达式的“星期几”部分也不需要强制转换）：

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE)) AS [weekstart],
       bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE),
       bg_status, st_name
 ORDER BY [week], bg_status

注意：未经测试的代码！

Answer 2

我意识到这是一个非常古老的线程，但是“在给定周数的情况下获取一周内的第一次约会”正是我想要做的，而且我没有实际的日期可以使用，所以接受的答案不适用于我。我想我应该为后代发布我的解决方案。请注意，我怀疑不同的文化设置可能会破坏这一点，因此在使用之前进行测试。

我的答案是从这个开始构建的。

假设您知道周数和年份，并且您想要获取该年该周的开始日期和结束日期。这是我所拥有的：

--These 2 "declared" variables would be passed in somehow
declare @WeekNumber int = DATEPART(wk, GETDATE())
declare @ForYear int = YEAR(GETDATE())-1

--Since we don't have a raw date to work with, I figured I could just start with 
--Jan 1 of that year.  I'll store that date in a cte here, but if you are doing this
--in a stored proc or function, it would make much more sense to use another @variable
;with x as
(
    --this method works in SQL 2008:
    SELECT CONVERT(DateTime, ('1/1/' + CONVERT(varchar, @ForYear))) as Jan1ForSelectedYear
    --If you are using 2014 or higher, you can use this instead:
    --DATETIME2FROMPARTS(@ForYear, 1, 1, 0,0,0,0,0)
)
--Now that we have a date to work with, we'll just add the number of weeks to that date
--That will bring us to the right week number of the given year.
--Once we have THAT date, we can get the beginning and ending of that week
--Sorry to make you scroll, but I think this is easier to see what is going on this way
SELECT  CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear) - 6, x.Jan1ForSelectedYear))), 101) as FirstDayOfWeekXForSelectedYear,
        CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear)    , x.Jan1ForSelectedYear))), 101) as LastDayOfWeekXForSelectedYear
FROM x