Hankinater

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?

Any help is appreciated!

Hank

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
;
```

Any help is appreciated!

Hank

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks Scott - That's perfect!!

not sure why my suggestion is "yikes!", as it's running sub-second, which is just fine for a "one-shot" stuff ... and even for daily operations ...

Seriously?

declare @startdate datetime

set @startdate = convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120);

DECLARE @starting_date datetime

DECLARE @number_of_months_to_gen int

SET @starting_date = '20140601' --<-- chg starting date as needed

SET @number_of_months_to_gen = 60 --<-- chg number of months as needed

with dates( dt )

as

(

-- select 1st day of month constructed from year and month inputs

select @startdate

union all -- facilitates recursion

-- add in remainder of days in month

select dt + 1

from dates

-- keeps adding until the next day would in 60 months

where dt <= dateadd( month, 60, @startdate )

)

, dates_tagged( dt, dw, dm, occurrence)

as

(

select dt, datepart( weekday, dt ), datepart( month, dt )

, row_number()

over( partition by datepart( month, dt ), datepart( weekday, dt )

order by dt )

from dates

)

select dt, dw, occurrence

from dates_tagged

where dw = 3 and occurrence = 2 --I added this

order by dt

option (maxrecursion 10000)

;

----------------------------------------------------------------------------------------------------

--force day of @starting_date to be the first

SET @starting_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @starting_date), 0)

;WITH

cteDigits AS (

SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

),

cteMonthNumbers AS (

--generated month numbers are 0-based, thus 0 thru (#months_needed - 1), so 40 months = monthNumbers 0 thru 39

SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS monthNumber

FROM cteDigits [1s]

CROSS JOIN cteDigits [10s]

CROSS JOIN cteDigits [100s]

WHERE

[100s].digit * 100 + [10s].digit * 10 + [1s].digit <= (@number_of_months_to_gen - 1)

)

SELECT

DATEADD(DAY, DATEDIFF(DAY, 1, day_7_of_month) / 7 * 7 + 7, 1) AS second_tues_of_month

FROM cteMonthNumbers

CROSS APPLY (

SELECT DATEADD(DAY, 6, DATEADD(MONTH, monthNumber, @starting_date)) AS day_7_of_month

) AS cross_apply_1

ORDER BY

second_tues_of_month

**Almost 11,000**logical I/Os vs**0**I/Os and you think there's no real difference?declare @startdate datetime

set @startdate = convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120);

DECLARE @starting_date datetime

DECLARE @number_of_months_to_gen int

SET @starting_date = '20140601' --<-- chg starting date as needed

SET @number_of_months_to_gen = 60 --<-- chg number of months as needed

**set statistics io on**

set statistics time onset statistics time on

with dates( dt )

as

(

-- select 1st day of month constructed from year and month inputs

select @startdate

union all -- facilitates recursion

-- add in remainder of days in month

select dt + 1

from dates

-- keeps adding until the next day would in 60 months

where dt <= dateadd( month, 60, @startdate )

)

, dates_tagged( dt, dw, dm, occurrence)

as

(

select dt, datepart( weekday, dt ), datepart( month, dt )

, row_number()

over( partition by datepart( month, dt ), datepart( weekday, dt )

order by dt )

from dates

)

select dt, dw, occurrence

from dates_tagged

where dw = 3 and occurrence = 2 --I added this

order by dt

option (maxrecursion 10000)

;

--------------------------

--force day of @starting_date to be the first

SET @starting_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @starting_date), 0)

;WITH

cteDigits AS (

SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

),

cteMonthNumbers AS (

--generated month numbers are 0-based, thus 0 thru (#months_needed - 1), so 40 months = monthNumbers 0 thru 39

SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS monthNumber

FROM cteDigits [1s]

CROSS JOIN cteDigits [10s]

CROSS JOIN cteDigits [100s]

WHERE

[100s].digit * 100 + [10s].digit * 10 + [1s].digit <= (@number_of_months_to_gen - 1)

)

SELECT

DATEADD(DAY, DATEDIFF(DAY, 1, day_7_of_month) / 7 * 7 + 7, 1) AS second_tues_of_month

FROM cteMonthNumbers

CROSS APPLY (

SELECT DATEADD(DAY, 6, DATEADD(MONTH, monthNumber, @starting_date)) AS day_7_of_month

) AS cross_apply_1

ORDER BY

second_tues_of_month

Or add either of these statements to the top:

set language [British English]

set datefirst 1

and check the results.

set language [British English]

set datefirst 1

and check the results.

Well, I agree that setting datefirst or language will change, but that can be "fixed" by replacing the

where dw = 3

accordingly.

for the "performance" part, I repeat that for my version, which runs sub-second, and presumably for a one-shot (or once per day), my efforts to "tune" something will go somewhere else but not on this one.

if this code is really a performance killer on my db instance, for example because the query is used all the time, I will put that data into a table (and I have my calendar table ready ANYHOW), so we won't discuss that "code" anyhow.

I understand that there are differences, but simply not worth "optimizing" for this small set of data.

where dw = 3

accordingly.

for the "performance" part, I repeat that for my version, which runs sub-second, and presumably for a one-shot (or once per day), my efforts to "tune" something will go somewhere else but not on this one.

if this code is really a performance killer on my db instance, for example because the query is used all the time, I will put that data into a table (and I have my calendar table ready ANYHOW), so we won't discuss that "code" anyhow.

I understand that there are differences, but simply not worth "optimizing" for this small set of data.

Actually I had written my comment just based on the initial q, I had not even seen your code yet. Although "Yikes!" certainly applies to both.

Frankly, I don't even consider this "optimizing", rather simply writing decent code to begin with. Generating thousands of rows' worth of data and searching through them to get 60 needed values is just extremely poor coding.

As to overall overhead, this is just a tiny code snippet. Hundreds or thousands of such snippets could appear in procs, and with each snippet getting executed multiple times a day. Thus, allowing such poor coding will easily waste

Finally, why encourage people to write such horribly inefficient code to begin with? Code like this tends to be copied and used over and over, as witnessed by it being copied here initially. Good coders should have a basic, minimal standard of coding, not lazily using known poor techniques because "it will only run once a day".

I mean, even the most basic date construction in the code:

select convert( datetime,

convert( char(8),

@yr * 10000 + @mo * 100 + 1

)

)

is very poor coding technique.

Frankly, I don't even consider this "optimizing", rather simply writing decent code to begin with. Generating thousands of rows' worth of data and searching through them to get 60 needed values is just extremely poor coding.

As to overall overhead, this is just a tiny code snippet. Hundreds or thousands of such snippets could appear in procs, and with each snippet getting executed multiple times a day. Thus, allowing such poor coding will easily waste

*multiple millions*of I/Os per day.Finally, why encourage people to write such horribly inefficient code to begin with? Code like this tends to be copied and used over and over, as witnessed by it being copied here initially. Good coders should have a basic, minimal standard of coding, not lazily using known poor techniques because "it will only run once a day".

I mean, even the most basic date construction in the code:

select convert( datetime,

convert( char(8),

@yr * 10000 + @mo * 100 + 1

)

)

is very poor coding technique.

but you can put the "recursion" stop condition also

Open in new window