如何在SQL中计算相对于当前日期即将到来的一月第二个星期一的日期?

Question

如何使用SQL查询计算1月份相对于当前日期即将到来的第二个星期一的日期?

下面是我的一个查询，如果当前日期是同一年，但在即将到来的1月的第二个星期一之前，则该查询不起作用。

declare @secondMondayOfJan date = dateadd(day , ((17 - datepart(dw,current_timestamp)) % 7) + 6, current_timestamp)
IF current_timestamp > @secondMondayOfJan
select @secondMondayOfJan as Deadline
ELSE
declare @firstDayOfNextYear date = datefromparts(year(current_timestamp)+1,1,1) 
set @secondMondayOfJan = dateadd(day, ((17 - datepart(dw,@firstDayOfNextYear)) % 7) + 6, @firstDayOfNextYear)   
select @secondMondayOfJan as Deadline

Answer 1

这看起来相当冗长，但希望使逻辑清晰，并且不依赖于任何特定的设置(例如DATEFIRST或语言设置):

;With Nums(n) as (
    select 0 union all select 1 union all select 2 union all select 3
             union all select 4 union all select 5 union all select 6
), ThisAndNext as (
    select
        DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20010108') as SecondWeek,
        0 as Choose
    union all
    select
        DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20020108'),
        1
), Combined as (
    select DATEADD(day,n,SecondWeek) as Possible,Choose
    from ThisAndNext cross join Nums
)
select top 1 Possible from Combined
where Possible >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
and DATEPART(weekday,Possible) = DATEPART(weekday,'20150504')
order by Choose

Nums只是一个小的数字集-它可以替换为一个select从数字表，如果你已经有一个。

ThisAndNext找到今年和明年的第8号，作为两个单独的行。

Combined在今年和明年的8号上增加0到6天。

最后，我们从Combined中选择第一个日期，即(以下项目符号对应于WHERE子句的行)

• 等于或大于今天的日期(使用DATEADD/DATEDIFF模式的另一个实例从GETDATE()中删除时间元素)
• 在星期一(通过将其与任意的，众所周知的星期一进行比较)
• 优先，从今年开始，而不是明年

总的来说，这意味着我们选择了一个介于1月8日和1月14日之间的未来(或当前)日，即星期一。如果你不想让"today"成为可能的结果，只需将>=的比较更改为>。

Answer 2

这段代码的逻辑是:

• 获取当年的第一天
• 获得下一年的第一天
• 查找当年1月的第二个工作日
• 查找次年1月第二个工作日
• 比较当前日期，如果大于或小于或等于，并基于此将截止日期设置为正确的第二个工作日

设置为检索第二个星期一，但将变量@dw的值更改为1到7之间，您将获得其他工作日(1 =星期日，2 =星期一，3 =星期二，4 =星期三，5 =星期四，6 =星期五，7 =星期六)

你可以在:http://rextester.com/TVKYW75798

/*
THIS STATEMENT WILL RETURN THE 2ND WEEKDAY OF JAN BASED ON CURRENT DATE.
IF CURRENT_DATE = 2ND WEEKDAY OF JAN 
    THEN DEADLINE = CURRENT_DATE
IF CURRENT_DATE < 2ND WEEKDAY OF JAN
    THEN DEADLINE = 2ND WEEKDAY OF JAN (CURRENT YEAR)
IF CURRENT_DATE > 2ND WEEKDAY OF JAN
    THEN DEADLINE = 2ND WEEKDAY OF JAN (NEXT YEAR)

YOU CAN PLAY WITH VARIABLE @date and @dw TO TEST. 
I LEAVE COMMENTED IN THE CODE FOUR SCENARIOS (YEAR 2015) 
WHERE YOU WILL BE ABLE TO CHECK THE CONDITIONS ABOVE.
2015-01-12 WAS THE 2ND MONDAY OF JAN
*/
DECLARE @date DATE = GETDATE() 
-- SET @date = '2015-01-01'
-- SET @date = '2015-01-11'
-- SET @date = '2015-01-12'
-- SET @date = '2015-01-13'
DECLARE @currentYearFirstDay DATE = CAST(datepart(yy, @date) AS VARCHAR) + '-01-01'
DECLARE @nextYearFirstDay DATE = DATEADD(yy, 1, @currentYearFirstDay)
DECLARE @d DATE
DECLARE @secondWeekdayCurrentYear DATE
DECLARE @secondWeekdayNextYear DATE
DECLARE @dw INT = 2 /*USE VALUES FROM 1 TO 7, WHERE 1 = SUNDAY AND 7 = SATURDAY*/
DECLARE @weekOfYear INT = DATEPART(ww,@d)
DECLARE @weekDay INT = DATEPART(dw,@d)
DECLARE @checkNextYear INT = 0
SET @d = @currentYearFirstDay
GOTO GET_2nd_WEEKDAY_FROM_FIRST_DAY_OF_YEAR  

GET_2nd_WEEKDAY_FROM_FIRST_DAY_OF_YEAR:
BEGIN   
    SET @weekDay = DATEPART(dw, @d)
    /*FIRST DAY OF YEAR GREATER THAN WEEKDAY, THEN 2nd WEEKDAY WILL BE THE 3rd WEEK*/
    IF @weekDay > @dw
        BEGIN
            SET @d = DATEADD(ww, 2, @d)
        END
    /*FIRST DAY OF YEAR LESS THAN OR EQUAL WEEKDAY, THEN 2nd WEEKDAY WILL BE THE 2nd WEEK*/    
    ELSE
        BEGIN
            SET @d = DATEADD(ww, 1, @d)
        END
    IF @checkNextYear = 0
        BEGIN
            SET @secondWeekdayCurrentYear = DATEADD(dw, -(@weekDay - @dw), @d)
            SET @d = @nextYearFirstDay
            SET @checkNextYear = 1
            GOTO GET_2nd_WEEKDAY_FROM_FIRST_DAY_OF_YEAR
        END
    ELSE
        BEGIN
            SET @secondWeekdayNextYear = DATEADD(dd, -(@weekDay - @dw), @d)
            GOTO EXIT_BLOCK
        END
END
EXIT_BLOCK:
SELECT CASE 
           WHEN @date > @secondWeekdayCurrentYear 
               THEN @secondWeekdayNextYear
               ELSE
                   CASE 
                       WHEN @date = @secondWeekdayCurrentYear
                           THEN @date
                           ELSE @secondWeekdayCurrentYear
                    END
      END AS DEADLINE