从周数获取周开始日期和周结束日期

2021-12-02 00:00:00 sql date week-number sql-server-2005 sql-server

我有一个查询数据库中成员的结婚日期.

I have a query that counts member's wedding dates in the database.

SELECT 
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART( wk, WeddingDate) AS [Week Number]
  , DATEPART( year, WeddingDate) AS [Year]
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC

如何计算结果集中表示每周的开始和结束时间?

How do I work out when the start and end of each week represented in the result set?

SELECT
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART(wk, WeddingDate) AS [Week Number]
  , DATEPART(year, WeddingDate) AS [Year]
  , ??? AS WeekStart
  , ??? AS WeekEnd
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC

推荐答案

您可以找到星期几并在天上添加日期以获取开始日期和结束日期..

You can find the day of week and do a date add on days to get the start and end dates..

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]

DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd]

不过，您可能还想考虑从日期中去除时间.

You probably also want to look at stripping off the time from the date as well though.

相关文章