如何在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

11 个解决方案

#1

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.

你可能也想从日期中去掉时间。

#2

Here is a DATEFIRST agnostic solution:

这里有一个DATEFIRST不可知论的解决方案:

SET DATEFIRST 4     /* or use any other weird value to test it */
DECLARE @d DATETIME

SET @d = GETDATE()

SELECT
  @d ThatDate,
  DATEADD(dd, (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,
  DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday

#3

you can also use this:

你也可以用这个:

  SELECT DATEADD(day, DATEDIFF(day, 0, WeddingDate) /7*7, 0) AS weekstart,
         DATEADD(day, DATEDIFF(day, 6, WeddingDate-1) /7*7 + 7, 6) AS WeekEnd

#4

Here is another version. If your Scenario requires Saturday to be 1st day of Week and Friday to be last day of Week, the below code will handle that:

这是另一个版本。如果你的剧本要求周六是一周的第一天，周五是最后一天，下面的代码将会处理:

  DECLARE @myDate DATE = GETDATE()
  SELECT    @myDate,
    DATENAME(WEEKDAY,@myDate),
    DATEADD(DD,-(CHOOSE(DATEPART(dw, @myDate), 1,2,3,4,5,6,0)),@myDate) AS WeekStartDate,
    DATEADD(DD,7-CHOOSE(DATEPART(dw, @myDate), 2,3,4,5,6,7,1),@myDate) AS WeekEndDate

#5

Below query will give data between start and end of current week starting from sunday to saturday

下面的查询将在从周日开始到周六的这周开始和结束之间提供数据。

SELECT DOB FROM PROFILE_INFO WHERE DAY(DOB) BETWEEN
DAY( CURRENT_DATE() - (SELECT DAYOFWEEK(CURRENT_DATE())-1))
AND
DAY((CURRENT_DATE()+(7 - (SELECT DAYOFWEEK(CURRENT_DATE())) ) ))
AND
MONTH(DOB)=MONTH(CURRENT_DATE())

#6

Expanding on @Tomalak's answer. The formula works for days other than Sunday and Monday but you need to use different values for where the 5 is. A way to arrive at the value you need is

扩大@Tomalak的答案。除了星期天和星期一之外，公式还可以用几天，但是你需要用不同的值来表示5的位置。达到你需要的价值的方法是

Value Needed = 7 - (Value From Date First Documentation for Desired Day Of Week) - 1

here is a link to the document: https://msdn.microsoft.com/en-us/library/ms181598.aspx

下面是文档的链接:https://msdn.microsoft.com/en-us/library/ms181598.aspx

And here is a table that lays it out for you.

这是一张桌子，给你们展示。

          | DATEFIRST VALUE |   Formula Value   |   7 - DATEFIRSTVALUE - 1
Monday    | 1               |          5        |   7 - 1- 1 = 5
Tuesday   | 2               |          4        |   7 - 2 - 1 = 4
Wednesday | 3               |          3        |   7 - 3 - 1 = 3
Thursday  | 4               |          2        |   7 - 4 - 1 = 2
Friday    | 5               |          1        |   7 - 5 - 1 = 1
Saturday  | 6               |          0        |   7 - 6 - 1 = 0
Sunday    | 7               |         -1        |   7 - 7 - 1 = -1

But you don't have to remember that table and just the formula, and actually you could use a slightly different one too the main need is to use a value that will make the remainder the correct number of days.

但是你不需要记住这个表格和公式，实际上你可以用一个稍微不同的方法，主要的需要是使用一个值，它会使剩余的天数变成正确的天数。

Here is a working example:

这里有一个工作示例:

DECLARE @MondayDateFirstValue INT = 1
DECLARE @FridayDateFirstValue INT = 5
DECLARE @TestDate DATE = GETDATE()

SET @MondayDateFirstValue = 7 - @MondayDateFirstValue - 1
SET @FridayDateFirstValue = 7 - @FridayDateFirstValue - 1

SET DATEFIRST 6 -- notice this is saturday

SELECT 
    DATEADD(DAY, 0 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate)  as MondayStartOfWeek
    ,DATEADD(DAY, 6 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayEndOfWeek
   ,DATEADD(DAY, 0 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate)  as FridayStartOfWeek
    ,DATEADD(DAY, 6 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayEndOfWeek


SET DATEFIRST 2 --notice this is tuesday

SELECT 
    DATEADD(DAY, 0 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate)  as MondayStartOfWeek
    ,DATEADD(DAY, 6 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayEndOfWeek
   ,DATEADD(DAY, 0 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate)  as FridayStartOfWeek
    ,DATEADD(DAY, 6 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayEndOfWeek

This method would be agnostic of the DATEFIRST Setting which is what I needed as I am building out a date dimension with multiple week methods included.

这个方法与DATEFIRST设置无关，这正是我在构建包含多个星期方法的日期维度时所需要的。

#7

Let us break the problem down to two parts:

让我们把问题分成两部分:

1) Determine the day of week

1)确定一周的日期

The DATEPART(dw, ...) returns a number, 1...7, relative to DATEFIRST setting (docs). The following table summarizes the possible values:

DATEPART(dw，…)返回一个数字，1…相对于DATEFIRST设置(文档)。下表总结了可能的值:

                                                   @@DATEFIRST
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
|                                    |  1  |  2  |  3  |  4  |  5  |  6  |  7  | DOW |
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
|  DATEPART(dw, /*Mon*/ '20010101')  |  1  |  7  |  6  |  5  |  4  |  3  |  2  |  1  |
|  DATEPART(dw, /*Tue*/ '20010102')  |  2  |  1  |  7  |  6  |  5  |  4  |  3  |  2  |
|  DATEPART(dw, /*Wed*/ '20010103')  |  3  |  2  |  1  |  7  |  6  |  5  |  4  |  3  |
|  DATEPART(dw, /*Thu*/ '20010104')  |  4  |  3  |  2  |  1  |  7  |  6  |  5  |  4  |
|  DATEPART(dw, /*Fri*/ '20010105')  |  5  |  4  |  3  |  2  |  1  |  7  |  6  |  5  |
|  DATEPART(dw, /*Sat*/ '20010106')  |  6  |  5  |  4  |  3  |  2  |  1  |  7  |  6  |
|  DATEPART(dw, /*Sun*/ '20010107')  |  7  |  6  |  5  |  4  |  3  |  2  |  1  |  7  |
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+

The last column contains the ideal day-of-week value for Monday to Sunday weeks*. By just looking at the chart we come up with the following equation:

最后一列包含周一到周日的理想周值*。通过看图表，我们得到了以下等式:

(@@DATEFIRST + DATEPART(dw, SomeDate) - 1 - 1) % 7 + 1

2) Calculate the Monday and Sunday for given date

计算给定日期的星期一和星期天

This is trivial thanks to the day-of-week value. Here is an example:

由于周日值，这是微不足道的。这是一个例子:

WITH TestData(SomeDate) AS (
    SELECT CAST('20001225' AS DATETIME) UNION ALL
    SELECT CAST('20001226' AS DATETIME) UNION ALL
    SELECT CAST('20001227' AS DATETIME) UNION ALL
    SELECT CAST('20001228' AS DATETIME) UNION ALL
    SELECT CAST('20001229' AS DATETIME) UNION ALL
    SELECT CAST('20001230' AS DATETIME) UNION ALL
    SELECT CAST('20001231' AS DATETIME) UNION ALL
    SELECT CAST('20010101' AS DATETIME) UNION ALL
    SELECT CAST('20010102' AS DATETIME) UNION ALL
    SELECT CAST('20010103' AS DATETIME) UNION ALL
    SELECT CAST('20010104' AS DATETIME) UNION ALL
    SELECT CAST('20010105' AS DATETIME) UNION ALL
    SELECT CAST('20010106' AS DATETIME) UNION ALL
    SELECT CAST('20010107' AS DATETIME) UNION ALL
    SELECT CAST('20010108' AS DATETIME) UNION ALL
    SELECT CAST('20010109' AS DATETIME) UNION ALL
    SELECT CAST('20010110' AS DATETIME) UNION ALL
    SELECT CAST('20010111' AS DATETIME) UNION ALL
    SELECT CAST('20010112' AS DATETIME) UNION ALL
    SELECT CAST('20010113' AS DATETIME) UNION ALL
    SELECT CAST('20010114' AS DATETIME)
), TestDataPlusDOW AS (
    SELECT SomeDate, (@@DATEFIRST + DATEPART(dw, SomeDate) - 1 - 1) % 7 + 1 AS DOW
    FROM TestData
)
SELECT
    FORMAT(SomeDate,                            'ddd yyyy-MM-dd') AS SomeDate,
    FORMAT(DATEADD(dd, -DOW + 1, SomeDate),     'ddd yyyy-MM-dd') AS [Monday],
    FORMAT(DATEADD(dd, -DOW + 1 + 6, SomeDate), 'ddd yyyy-MM-dd') AS [Sunday]
FROM TestDataPlusDOW

Output:

输出:

+------------------+------------------+------------------+
|  SomeDate        |  Monday          |    Sunday        |
+------------------+------------------+------------------+
|  Mon 2000-12-25  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Tue 2000-12-26  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Wed 2000-12-27  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Thu 2000-12-28  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Fri 2000-12-29  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Sat 2000-12-30  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Sun 2000-12-31  |  Mon 2000-12-25  |  Sun 2000-12-31  |
|  Mon 2001-01-01  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Tue 2001-01-02  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Wed 2001-01-03  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Thu 2001-01-04  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Fri 2001-01-05  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Sat 2001-01-06  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Sun 2001-01-07  |  Mon 2001-01-01  |  Sun 2001-01-07  |
|  Mon 2001-01-08  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Tue 2001-01-09  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Wed 2001-01-10  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Thu 2001-01-11  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Fri 2001-01-12  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Sat 2001-01-13  |  Mon 2001-01-08  |  Sun 2001-01-14  |
|  Sun 2001-01-14  |  Mon 2001-01-08  |  Sun 2001-01-14  |
+------------------+------------------+------------------+

_{* For Sunday to Saturday weeks you need to adjust the equation just a little, like add 1 somewhere.}

*从周日到周六，你需要稍微调整一下方程，比如在某个地方加1。

#8

I just encounter a similar case with this one, but the solution here seems not helping me. So I try to figure it out by myself. I work out the week start date only, week end date should be of similar logic.

我只是遇到了类似的情况，但这里的解决方案似乎对我没有帮助。所以我试着自己算出来。我只算出周初日期，周终日期应该是类似的逻辑。

Select 
      Sum(NumberOfBrides) As [Wedding Count], 
      DATEPART( wk, WeddingDate) as [Week Number],
      DATEPART( year, WeddingDate) as [Year],
      DATEADD(DAY, 1 - DATEPART(WEEKDAY, dateadd(wk, DATEPART( wk, WeddingDate)-1,  DATEADD(yy,DATEPART( year, WeddingDate)-1900,0))), dateadd(wk, DATEPART( wk, WeddingDate)-1, DATEADD(yy,DATEPART( year, WeddingDate)-1900,0))) as [Week Start]

FROM  MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc

#9

The most voted answer works fine except for the 1st week and last week of a year. For example, if the value of WeddingDate is '2016-01-01', the result will be 2015-12-27 and 2016-01-02, but the right answer is 2016-01-01 and 2016-01-02.

除了第一周和最后一周，大多数投票结果都很好。例如，如果WeddingDate的值为“2016-01-01”，则结果分别为2015-12-27和2016-01-02，但正确的答案是2016-01-01和2016-01-02。

Try this:

试试这个:

Select 
  Sum(NumberOfBrides) As [Wedding Count], 
  DATEPART( wk, WeddingDate) as [Week Number],
  DATEPART( year, WeddingDate) as [Year],
  MAX(CASE WHEN DATEPART(WEEK, WeddingDate) = 1 THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0) AS date) ELSE DATEADD(DAY, 7 * DATEPART(WEEK, WeddingDate), DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0)) + 6), DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0))) END) as WeekStart,
  MAX(CASE WHEN DATEPART(WEEK, WeddingDate) = DATEPART(WEEK, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate) + 1, 0))) THEN DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate) + 1, 0)) ELSE DATEADD(DAY, 7 * DATEPART(WEEK, WeddingDate) + 6, DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0)) + 6), DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0))) END) as WeekEnd
FROM  MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc;

The result looks like:

结果看起来像:

It works for all weeks, 1st or others.

它适用于所有的星期，第一或其他。

#10

Not sure how useful this is, but I ended up here from looking for a solution on Netezza SQL and couldn't find one on stack overflow.

我不知道这有多有用，但我在Netezza SQL上寻找解决方案，但在stack overflow上找不到解决方案。

For IBM netezza you would use something (for week start mon, week end sun) like:

对于IBM netezza，您可以使用如下内容:

select next_day (WeddingDate, 'SUN') -6 as WeekStart,

选择next_day (WeddingDate， 'SUN') -6作为周初，

next_day (WeddingDate, 'SUN') as WeekEnd

next_day (WeddingDate， 'SUN')作为周末。

#11

for Access Queries, you can use in the below format as a field

对于访问查询，可以使用以下格式作为字段

"FirstDayofWeek:IIf(IsDate([ForwardedForActionDate]),CDate(Format([ForwardedForActionDate],"dd/mm/yyyy"))-(Weekday([ForwardedForActionDate])-1))"

direct Calculation allowed..

直接计算允许. .