Get date of every second Tuesday of a month

Question

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.

Solution 1

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)

Solution 2

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)

Solution 3

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

Solution 4

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:

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

View more solutions

Comments

[DaFunkyAlex]

Thank you Damien, that helps!