从周号中获取周开始日期和周结束日期

您可以查找一周中的某一天，然后添加一个日期以获得开始日期和结束日期。.

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


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

你可能也想看看从日期中剥离出来的时间。

这里有一个 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

下面的查询将给出当周开始和结束之间的数据 从周日到周六

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())

I just encounter a similar case with this one, but the solution here seems not helping me. 所以我想自己搞清楚。我只计算周开始日期，周末日期应该是类似的逻辑。

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

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))"

允许直接计算。

你也可以这样做:

  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

不知道这个有多么有用，但是我在 Netezza SQL 上找到了一个解决方案，但是没有找到一个关于堆栈溢出的解决方案。

对于 IBM netezza，您可以使用以下内容(对于周开始的星期一，周结束的太阳) :

选择 Next _ day (WeddingDate，‘ SUN’) -6 as WeekStart,

Next _ day (WeddingDate，‘ SUN’)作为周末

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

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

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

这里有一个文档的链接: https://msdn.microsoft.com/en-us/library/ms181598.aspx

这里有张桌子给你们摆好了。

          | 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

但是你不需要记住那个表和公式，实际上你也可以使用一个稍微不同的表，主要需要的是使用一个值，使得余数成为正确的天数。

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

这个方法将是不可知的 DATEFIRST设置，这是我所需要的，因为我正在建立一个日期维度与多个星期的方法包括在内。

投票最多的答案除了一年中的 第一周和上周以外，其他答案都很好。例如，如果 WeddingDate 的值是“2016-01-01”，那么结果将是 2015-12-27和 2016-01-02，但是正确的答案是 2016-01-01和 2016-01-02。

试试这个:

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;

结果看起来像是:

It works for all weeks, 1st or others.

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

1) Determine the day of week

相对于 DATEFIRST设置(医生) ，DATEPART(dw, ...)返回一个数字1... 7。下表总结了可能的值:

                                                   @@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 很理想 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)按指定日期计算星期一及星期日

由于每周一天的价值，这是微不足道的。下面是一个例子:

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

产出:

+------------------+------------------+------------------+
|  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  |
+------------------+------------------+------------------+

_{星期天到星期六，你需要稍微调整一下方程式，比如在某处加1。}

这不是我说的，但它完成了任务:

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 给你.

功率 BI Dax 公式的开始和结束日期

WeekStartDate = [DateColumn] - (WEEKDAY([DateColumn])-1)
WeekEndDate = [DateColumn] + (7-WEEKDAY([DateColumn]))

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]

按自定义日期获取开始日期和结束日期

DECLARE @Date NVARCHAR(50)='05/19/2019'
SELECT
DATEADD(DAY,CASE WHEN DATEPART(WEEKDAY, @Date)=1 THEN -6 ELSE 2 - DATEPART(WEEKDAY, @Date) END, CAST(@Date AS DATE)) [Week_Start_Date]
,DATEADD(DAY,CASE WHEN DATEPART(WEEKDAY, @Date)=1 THEN 0 ELSE  8 - DATEPART(WEEKDAY, @Date) END, CAST(@Date AS DATE)) [Week_End_Date]

我有另外一种方法，它是选择一天开始和一天结束的一周当前:

DATEADD (d,-(DATEPART (dw，GETDATE ()-2)) ，GETDATE ())是日期时间开始

还有

DATEADD (day，7-(DATEPART (dw，GETDATE ()-1)) ，GETDATE ())是日期时间结束

Another way to do it:

declare @week_number int = 6280 -- 2020-05-07
declare @start_weekday int = 0 -- Monday
declare @end_weekday int = 6 -- next Sunday


select
dateadd(week, @week_number, @start_weekday),
dateadd(week, @week_number, @end_weekday)

说明:

• @week_number is the week number since the initial calendar date '1900-01-01'. It can be computed this way: select datediff(week, 0, @wedding_date) as week_number
• @ start _ week day for the week first day: 星期一是 0，星期日是 -1
• @ end _ week day for the week last day: 下周日是 6，周六是 5
• dateadd(week, @week_number, @end_weekday): adds the given number of weeks and the given number of days into the initial calendar date '1900-01-01'

如果星期天被认为是一周开始的一天，那么这里是代码

Declare @currentdate date = '18 Jun 2020'


select DATEADD(D, -(DATEPART(WEEKDAY, @currentdate) - 1), @currentdate)


select DATEADD(D, (7 - DATEPART(WEEKDAY, @currentdate)), @currentdate)