sql - 获取指定日期的上一个星期二(或一周中的任何给定日期)

Question

我想获取指定日期的上一个星期二(或一周中的任何给定日期)。这是星期二的样本输入和预期输出:

CREATE TABLE #temp(testdate DATETIME);
INSERT INTO  #temp(testdate) VALUES
    ('2015-10-06 01:15'), -- Tue -> Tue 2015-10-06 00:00
    ('2015-10-07 04:30'), -- Wed -> Tue 2015-10-06 00:00
    ('2015-10-08 00:30'), -- Thu -> Tue 2015-10-06 00:00
    ('2015-10-09 21:00'), -- Fri -> Tue 2015-10-06 00:00
    ('2015-10-10 19:00'), -- Sat -> Tue 2015-10-06 00:00
    ('2015-10-11 01:15'), -- Sun -> Tue 2015-10-06 00:00
    ('2015-10-12 13:00'), -- Mon -> Tue 2015-10-06 00:00

    ('2015-10-13 18:45'), -- Tue -> Tue 2015-10-13 00:00
    ('2015-10-14 12:15'), -- Wed -> Tue 2015-10-13 00:00
    ('2015-10-15 10:45'), -- Thu -> Tue 2015-10-13 00:00
    ('2015-10-16 04:30'), -- Fri -> Tue 2015-10-13 00:00
    ('2015-10-17 12:15'), -- Sat -> Tue 2015-10-13 00:00
    ('2015-10-18 00:30'), -- Sun -> Tue 2015-10-13 00:00
    ('2015-10-19 10:45'), -- Mon -> Tue 2015-10-13 00:00

    ('2015-10-20 01:15'), -- Tue -> Tue 2015-10-20 00:00
    ('2015-10-21 23:45'), -- Wed -> Tue 2015-10-20 00:00
    ('2015-10-22 21:00'), -- Thu -> Tue 2015-10-20 00:00
    ('2015-10-23 18:45'), -- Fri -> Tue 2015-10-20 00:00
    ('2015-10-24 06:45'), -- Sat -> Tue 2015-10-20 00:00
    ('2015-10-25 06:45'), -- Sun -> Tue 2015-10-20 00:00
    ('2015-10-26 04:30'); -- Mon -> Tue 2015-10-20 00:00

DECLARE @clampday AS INT = 3; -- Tuesday
SELECT -- DATEADD/DATEPART/@clampday/???

使用T-SQL获取上一个星期二(或一周中的任何一天)的最合适方法是什么？

Answer 1

我希望这能帮到您

SELECT DATEADD(day,- (DATEPART(dw, testdate) + @@DATEFIRST - 3) % 7,testdate) AS Saturday  
from #temp

或者

SELECT DATENAME(weekday,DATEADD(day,- (DATEPART(dw, testdate) + @@DATEFIRST - 3) % 7,testdate)) +'  '+ 
        CONVERT(nvarchar,DATEADD(day,- (DATEPART(dw, testdate) + @@DATEFIRST - 3) % 7,testdate),101) AS Saturday
from @temp

输出将在下面

Saturday
Tuesday  10/06/2015

备注:整个查询只是 DATEFIRST， DATEPART和 DATEADD的组合和计算以操纵时间