CODEWITHSUNDEEP
sqlt-sql
DaFunkyAlex
DaFunkyAlex

Reputation: 1969

Get date of every second Tuesday of a month

Is there a way to find out the date of every second Tuesday of a month using T-SQL syntax?

E.g. in March it is the 12th, in April it's the 9th.

Upvotes: 5

Views: 12591

Answers (5)

Rishit Madhav
Rishit Madhav

Reputation: 1

This will give you current month's Patch Tuesday

Declare @Date DATETIME = Getdate()

--Set  @Date = DAtefromParts(2022,01,01) -- for testing

Declare @PT DATETIME
Declare @WeekDay DATETIME
Declare @CM varchar(30)

Declare @FD DATETIME = DAtefromParts(DATEPART(yyyy,@date), DATEPART(mm,@date),01) --FirstDayof the month
print @FD

SET @WeekDay = DATEPART(WEEKDAY,@FD) --No of WeekDay

if  @WeekDay =1
   Begin 
   
   Set @PT = DateAdd(D,9,@FD)
   end 

else if @WeekDay =2
   Begin 
 
   Set @PT = DateAdd(D,8,@FD)
   end 
else if @WeekDay =3
   Begin 
     Set @PT = DateAdd(D,7,@FD)
   end
else if @WeekDay =4
   Begin 
 
   Set @PT = DateAdd(D,13,@FD)
   end 
else if @WeekDay =5
   Begin 

   Set @PT =  DateAdd(D,12,@FD)
   end 
else if  @WeekDay =6
   Begin 

   Set @PT =  DateAdd(D,11,@FD)
   end 
else if @WeekDay =7
   Begin 
    Set @PT =  DateAdd(D,10,@FD)
   End 


Print @PT

Upvotes: 0

Pravin Pandit
Pravin Pandit

Reputation: 21

This code will give you every 1st and 3rd Sunday of the month.

declare @dt datetime
select @dt = '12/01/2014'

select dateadd(mm,datediff(mm,'',@dt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+0)+ 8
select dateadd(mm,datediff(mm,'',@dt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+0)+ 22

Upvotes: 2

Panfiva
Panfiva

Reputation: 11

Previous answer does not work for months starting on Sunday ( it points to the second Sunday instead).

SELECT @dt AS input_date,

    DATEADD(mm, DATEDIFF(mm, 0, @dt), 0) --truncate date to month start

    -- DATEPART(@month_start) returns month start's weekday, with Sunday starting 1;
    -- Since Sunday starts at 1, we need to perform proper adjustment - move date 6 days forward (7 week days - 1 for sunday) forward and find its datepart, which will be 7
    -- Result: month starting sunday, datepart returns 7; month starting Mon we return 1 (datepart of Mon + 6 days = Sunday, which is 1), month starting tue, we return 2
    -- Effectivelly, datepart offset will always point last Sunday of previous month
    - DATEPART(dw, 
            6
            + DATEADD(mm,datediff(mm,0,@dt),0) --truncate date to month start
        ) 
        
    -- Since we found last Sunday of previous month, we need to add 7
    + 7 AS CORRECT,

    dateadd(mm,datediff(mm,'',@dt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+0)+ 8 AS sometimes_correct

Image that shows shows correct answer relative to the answer by Pravin Pandit:

1

We can extend this rationale for finding first Tue of the month and create a function that does that, so for any input date, it will find 1st Tue of the month in question:

ALTER FUNCTION dbo.f_time_floor_1st_tue(@date DATETIME2(3))
RETURNS DATETIME
AS
BEGIN
    RETURN 
    
            DATEADD(mm, DATEDIFF(mm, 0, @date), 0) --truncate date to month start

        -- DATEPART(@month_start) returns month start's weekday, with Sunday starting 1;
        -- Since Sunday starts at 1, we need to perform proper adjustment - move date 6 days forward (7 week days - 1 for sunday) forward and find its datepart, which will be 7
        -- Result: month starting sunday, datepart returns 7; month starting Mon we return 1 (datepart of Mon + 6 days = Sunday, which is 1), month starting tue, we return 2
        -- Effectivelly, datepart offset will always point last Sunday of previous month
        -- Extending this logic for finding first Tuesday, Tuesday should always return 7 we need to move Tue datepart (3) by 4  ( which is 7 days in the week minus 3 
        - DATEPART(dw, 
                4 -- 4 is adjustment so that DATEPART returns 7 for all months starting Tue
                + DATEADD(mm,datediff(mm,0,@date),0) --truncate date to month start
            ) 
        
        -- Since we found last weekday of previous month, we need to add 7
        + 7
    
    ;
END;
GO

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44336

This is how you can find all 'second tuesdays' in 2013.

select 
dateadd(day, 8, datediff(day, 1, dateadd(month, n, '2013-01-07')) / 7 * 7) date
from 
(values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) t(n)

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Without knowing what the actual required inputs and outputs are, all I can give you at the moment is a predicate for identifying a date as the second tuesday of its month:

DATEPART(day,@Date) between 8 and 14 and --Find the second one in the month
DATEPART(weekday,@Date) = DATEPART(weekday,'20130319') --Make sure its a Tuesday

(I use a fixed, known Tuesday, so as to avoid having to know what DATEFIRST settings are in effect when the query is run)

This finds the appropriate Tuesday for the current month, but obviously @Date could be set to any date of interest:

declare @Date datetime
set @Date =  CURRENT_TIMESTAMP

;with Numbers as (select n from (values (0),(1),(2),(3),(4),(5),(6)) t(n)),
PotentialDates as (select DATEADD(day,n,DATEADD(month,DATEDIFF(month,'20010101',@Date),'20010108')) as Date
from Numbers
)
select * from PotentialDates where DATEPART(weekday,Date) = DATEPART(weekday,'20130319')

(And, hopefully also obviously, the query could be part of a larger query, where @Date was instead a column value, and so this can form part of a set-based approach to the entire piece of work)

Upvotes: 3

Related Questions