Avatar of Hankinater
Hankinater
Flag for United States of America

asked on 

T-SQL finding second tuesday of every month for next 60 months

I need a T-SQL query that will be calculate the 2nd tuesday of each month for the next 60 months.

I found Kevin Cross's artilce Date of the n-th Day in a Month and was able to alter the following script to give me the 2nd Tuesday of the particular month.  But how how can I alter the script to get all 2nd Tuesdays for the next 60 months?

declare @yr int, @mo tinyint
set @yr = 2014
set @mo = 6
;
with dates( dt )
as
(
   -- select 1st day of month constructed from year and month inputs
   select convert( datetime, 
            convert( char(8), 
               @yr * 10000 + @mo * 100 + 1 
            ) 
         ) 

   union all -- facilitates recursion

   -- add in remainder of days in month
   select dt + 1
   from dates
   -- keeps adding until the next day would be 1st of next month
   where day( dt + 1 ) <> 1 
)
, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
   select dt, datepart( weekday, dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt desc )
   from dates
)
select dt, dw, occurrence, occurrence_reverse
from dates_tagged
where dw = 3 and occurrence = 2  --I added this
; 

Open in new window


Any help is appreciated!

Hank
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon