时间戳

首页 > 解决方案 > 获取从星期四开始的周开始/结束日期和周数

sql - 获取从星期四开始的周开始/结束日期和周数

问题描述

我有这个现有的项目，它是每周报告。它的参数之一是周范围。它工作得很好，但他们需要重新编写报告，而不是在一年的第一天开始一周，他们需要在第一个星期四开始一周并在星期三结束。我有这个查询

SELECT   MIN([DATE]) as StartWeek
            ,MAX([DATE]) as EndWeek
            ,DATENAME(WEEK,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101))) as [WEEK]

    FROM [somedb]
    WHERE DATENAME(Year,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101))) = 2018
    GROUP BY DATENAME(WEEK,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101)))

这个查询的输出是

Start Week    End Week    WEEK
2018-01-01    2018-01-07  1

预期的输出应该是

Start Week                                         End Week    Week
2018-1-4(since its the first thursday of the week) 2018-1-10    1

是的，我可以DATEADD，但周数仍将 2018-1-8 确认为WEEK 2

标签： sqlsql-servertsqldatetime

解决方案

DECLARE @WeekDay Varchar(500),
        @Add     Int,
        @DATE    Date;

        SET @DATE = '2018-01-01';

        SELECT @WeekDay = DATENAME(DW,@DATE);

        SELECT @Add = CASE
                      WHEN  @WeekDay    =   'Thursday'  THEN 
                      0
                      WHEN  @WeekDay    =   'Friday'    THEN 
                      6
                      WHEN  @WeekDay    =   'Saturday'  THEN 
                      5
                      WHEN  @WeekDay    =   'Sunday'    THEN 
                      4
                      WHEN  @WeekDay    =   'Monday'    THEN 
                      3
                      WHEN  @WeekDay    =   'Tuesday'   THEN 
                      2
                      WHEN  @WeekDay    =   'Wednesday' THEN 
                      1
                  END

SELECT  DATEADD(DD,@Add, CONVERT(DATE,@DATE))   AS WeekStart,
        DATEADD(DD,@Add+6, CONVERT(DATE,@DATE)) AS WeekEnd,
        1                                       AS Week

推荐阅读