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

我在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;

最佳答案

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

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

关于sql-server - 在没有日历表的情况下查找周结束日期的有效方法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41244897/