得到周数在SQL Server的一周开始日期和星期结束日期(Get the week start d

2019-06-18 10:22发布

站内文章 / 前沿技术

40 0

叛逆

女 | 书童

私信

我有一个统计成员的婚礼日期在数据库中查询...

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

我如何工作，出来的时候，开始和每个星期结束时的结果表示在什么位置？

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

Answer 1:

你可以找到一周中的一天，做一个日期上的天添加到拿到开始和结束日期..

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

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

你可能也想看看从日期剥离的时间也不过。

Answer 2:

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

Answer 3:

你也可以使用这样的：

  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

Answer 4:

这里是另一个版本。如果您的方案需要一个星期六为周和周五的第一天是星期的最后一天，下面的代码将处理如下：

  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

Answer 5:

下面的查询将到周六给予从周日开始和本周起动端之间的数据

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

Answer 6:

扩大对@托默勒格的答案。该公式适用于比周日和周一其他日子，但你需要为其中5是使用不同的值。在您需要的值到达的方法是

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

但你不必记住表，只是公式，实际上你可以使用一个稍微不同的一个也主要需要的是使用的值，这将使剩余正确的天数。

这是一个工作示例：

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设置这正是我需要的，因为我增建日期维度多星期的方法包括在内。

Answer 7:

让我们打破了问题降低到两个部分组成：

1）确定星期几

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

最后一列包含了周一理想中某一天的周值至周日周*。通过看我们拿出下面的公式图表：

(@@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的地方。}

Answer 8:

我只是碰到了类似的情况与这一个，但这里的解决方案似乎没有我的帮助。所以，我试图通过自己看着办吧。我工作了仅一周的开始日期，星期结束日期应该是相似的逻辑。

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

Answer 9:

最投票的答案，除了工作了一年的第一周和最后一周的罚款。例如，如果WeddingDate的值是“2016年1月1日”，其结果将是2015年12月27日和2016年1月2日 ，但正确的答案是2016年1月1日和2016年1月2日 。

试试这个：

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;

其结果是这样的：

它适用于所有周，1日或他人。

Answer 10:

这不来自我只是把工作做好：

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

我发现它在这里。

Answer 11:

本周开始与结束日期从日期双向电力DAX公式

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

Answer 12:

这是我的解决办法


    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]

Answer 13:

不知道这是多么有用的，但我从寻找在Netezza SQL的解决方案在这里结束了，找不到一个堆栈溢出。

对于IBM Netezza公司，你会使用的东西（一周开始星期一，周末太阳），如：

选择NEXT_DAY（WeddingDate， '太阳'）-6作为WeekStart，

NEXT_DAY（WeddingDate， '太阳'）作为周末

Answer 14:

对于Access查询，你可以在下面的格式使用作为一个字段

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

直接计算允许..

文章来源: Get the week start date and week end date from week number in SQL Server

标签： sql sql-server date sql-server-2005 week-number

叛逆

女 | 书童

私信

收藏的人(0)

Ta的文章更多文章

0条评论

还没有人评论过~