sql-server - 在没有日历表的情况下查找周结束日期的有效方法

Question

我在 MS SSMS 中使用以下 SQL 代码来查找星期结束日期。我经常不得不在周三和周日之间切换作为一周的结束日。旁注，我的数据集通常在 100,000 个左右。

除了使用日历表之外，还有比我下面的方法更有效的方法吗？

/* Declare Variables */
DECLARE @WeekendingDay VARCHAR(10);
DECLARE @DayNumber INT;
DECLARE @InputDate VARCHAR(10);
DECLARE @conInputDate DATETIME;
DECLARE @outWeekending DATETIME;
DECLARE @CovertToInt VARCHAR(10);

/* --------------------Inputs-------------------- */

/* uncomment the weekending day you want */
--Set @WeekendingDay = 'Monday'
--Set @WeekendingDay = 'Tuesday'
--Set @WeekendingDay = 'Wednesday'
--Set @WeekendingDay = 'Thursday'
--Set @WeekendingDay = 'Friday'
--Set @WeekendingDay = 'Saturday'
SET @WeekendingDay = 'Sunday';

/* Date you want the weekending of */               
SET @InputDate = '29/12/2016';

/* --------------------Process-------------------- */
SET @DayNumber = CASE @WeekendingDay
                   WHEN 'Sunday' THEN 1
                   WHEN 'Monday' THEN 2
                   WHEN 'Tuesday' THEN 3
                   WHEN 'Wednesday' THEN 4
                   WHEN 'Thursday' THEN 5
                   WHEN 'Friday' THEN 6
                   WHEN 'Saturday' THEN 7
                 END;


SET @conInputDate = CONVERT(DATETIME, @InputDate, 103);
SET @outWeekending = DATEADD(dd,
                             CASE WHEN DATEPART(DW, @conInputDate) = @DayNumber
                                  THEN 0
                                  ELSE -1 * DATEPART(DW, @conInputDate) + 7
                                       + @DayNumber
                             END, @conInputDate);

/* --------------------Output-------------------- */
PRINT @outWeekending;

Answer 1

这将为您提供一周中的星期一。根据您希望作为结束日期的日期，您可以根据需要进行调整。

DECLARE @date DATETIME = GETUTCDATE()-5

SELECT
    @date
    ,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0)    --Monday
    ,DATEADD(DD,1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Tuesday
    ,DATEADD(DD,2,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Wednesday
    ,DATEADD(DD,3,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Thursday
    ,DATEADD(DD,4,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Friday
    ,DATEADD(DD,5,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Saturday
    ,DATEADD(DD,6,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Sunday