Get previous Tuesday (or any given day of week) for specified date

Question

I want to get previous Tuesday (or any given day of week) for specified date. Here is the sample input and expected output for Tuesday:

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/???

What is the most appropriate way to get the previous Tuesday (or any day of week) using T-SQL?

Possible duplicate of [How do you get the "week start date" and "week end date" from week number in SQL Server?](https://stackoverflow.com/questions/1267126/how-do-you-get-the-week-start-date-and-week-end-date-from-week-number-in-sql) — Salman A, Nov 17 '17 at 14:51

wiretext · Accepted Answer · 2015-10-12T12:31:19.223

8

i hope this will help you

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

OR

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

OUTPUT would be in below formate

Saturday
Tuesday  10/06/2015

Remark: the whole query is just combination and calculation of DATEFIRST, DATEPART and DATEADD to manipulate a time

edited Oct 12 '15 at 12:31

answered Oct 12 '15 at 11:39

wiretext

3,302
14
19

@SalmanA `DATEFIRST` specifies the first day of the week. The U.S. English default is 7, Sunday. `DATEPART` Returns an integer that represents the specified datepart of the specified date. `DATEADD` Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. The whole query just combination and calculation of time – wiretext Oct 12 '15 at 12:28
1

This is one of the most elegant answers I've seen on SO – e_i_pi Aug 01 '18 at 01:59

Stanislovas Kalašnikovas · Answer 2 · 2015-10-12T11:45:34.083

You can use get number of week by using DATEPART and then use CASE statement in following:

SELECT
    testdate,
    CASE DATEPART(dw,testdate)  WHEN 1 THEN DATEADD(dd,-5,testdate) 
                                WHEN 2 THEN DATEADD(dd,-6,testdate) 
                                WHEN 3 THEN DATEADD(dd, 0,testdate) 
                                WHEN 4 THEN DATEADD(dd,-1,testdate) 
                                WHEN 5 THEN DATEADD(dd,-2,testdate) 
                                WHEN 6 THEN DATEADD(dd,-3,testdate) 
                                WHEN 7 THEN DATEADD(dd,-4,testdate)                                     
    END
FROM #temp

Accoring to @jpw comment, you have to set DATEFIRST to 7 (default) in following:

SET DATEFIRST 7

score 0 · Answer 3 · edited Oct 12 '15 at 13:36

CREATE PROC FIND_TUESDAY_DATE
(
 @MYDATE DATE
)
AS 
BEGIN
    SELECT CASE 
            WHEN DATENAME(DW,CAST(DATEADD(DAY,0,@MYDATE) AS DATE)) = 'Tuesday' OR DATENAME(DW,CAST(DATEADD(DAY,-7,@MYDATE) AS DATE))  = 'Tuesday' 
            THEN CAST(DATEADD(DAY,-7,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-1,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-1,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-2,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-2,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-3,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-3,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-4,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-4,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-5,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-5,@MYDATE) AS DATE)
            WHEN DATENAME(DW,CAST(DATEADD(DAY,-6,@MYDATE) AS DATE) ) = 'Tuesday' THEN CAST(DATEADD(DAY,-6,@MYDATE) AS DATE)
        END
END
GO

score 0 · Answer 4 · answered Oct 12 '15 at 13:04

0

The most effective way is using DATEADD and DATEDIFF:

This is all the code you need, it also works for other weekdays

DECLARE @daystoadd int = 1 -- tuesday


SELECT DATEADD(week, datediff(d, @daystoadd, testdate) / 7, @daystoadd)
FROM #temp

answered Oct 12 '15 at 13:04

t-clausen.dk

43,517
12
59
92

score 0 · Answer 5 · answered May 06 '20 at 21:51

Answering with some details if still helps:

declare @given_date datetime = '2015-10-15 00:30'
declare @required_weekday int = 1 -- tuesday

select dateadd(week, datediff(week, 0, @given_date), @required_weekday)

Explanation:

datediff(week, 0, @given_date): returns the number_of_weeks between 1900-01-01 and the given_date
dateadd(week, number_of_weeks, @required_weekday): adds the computed number_of_weeks and the given required_weekday to the initial calendar date 1900-01-01

Get previous Tuesday (or any given day of week) for specified date

5 Answers5

Linked

Related