从 SQL Server 中的周数获取周开始日期和周结束日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1267126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the week start date and week end date from week number in SQL Server
提问by digiguru
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
回答by Robin Day
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.
不过，您可能还想考虑从日期中删除时间。
回答by Tomalak
Here is a DATEFIRSTagnostic 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, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,
DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday
回答by hkravitz
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
回答by Ajay Dwivedi
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
回答by Matt
Expanding on @Tomalak'sanswer. 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 DATEFIRSTSetting which is what I needed as I am building out a date dimension with multiple week methods included.
这种方法与DATEFIRST设置无关，这是我需要的，因为我正在构建一个包含多周方法的日期维度。
回答by riya
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())
回答by Salman A
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 DATEFIRSTsetting (docs). The following table summarizes the possible values:
该DATEPART(dw, ...)返回的数，1 ... 7，相对于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 idealday-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
2）计算给定日期的星期一和星期日
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。
回答by Francesco Mantovani
This doesn't came from me but it made the job done:
这不是来自我，但它完成了工作：
SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day previous week
SELECT DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day current week
SELECT DATEADD(wk, 1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day next week
SELECT DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day previous week
SELECT DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day current week
SELECT DATEADD(wk, 2, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day next week
I found it here.
回答by vikram jain
Week Start & End Date From Date For Power BI Dax Formula
Power BI Dax 公式的周开始和结束日期
WeekStartDate = [DateColumn] - (WEEKDAY([DateColumn])-1)
WeekEndDate = [DateColumn] + (7-WEEKDAY([DateColumn]))
回答by Jing Daradal
This is my solution
这是我的解决方案
SET DATEFIRST 1; /* change to use a different datefirst */
DECLARE @date DATETIME
SET @date = CAST('2/6/2019' as date)
SELECT DATEADD(dd,0 - (DATEPART(dw, @date) - 1) ,@date) [dateFrom],
DATEADD(dd,6 - (DATEPART(dw, @date) - 1) ,@date) [dateTo]
