sql – 获取指定日期的上周二(或任何给定的某一天)

发布时间：2020-12-12 07:00:18 所属栏目：MsSql教程来源：网络整理

导读：我希望在指定日期之前的星期二(或任何特定日期)获得.以下是周二的示例输入和预期输出： 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

我希望在指定日期之前的星期二(或任何特定日期)获得.以下是周二的示例输入和预期输出：

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获取上周二(或任何一周)的最合适方法是什么？

解决方法

我希望这能帮到您

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

要么

SELECT DATENAME(weekday,DATEADD(day,testdate)) +'  '+ 
        CONVERT(nvarchar,testdate),101) AS Saturday
from @temp

输出将在下面的甲酸盐中

Saturday
Tuesday  10/06/2015

备注：整个查询只是组合和计算DATEFIRST,DATEPART和DATEADD来操纵时间

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!