# How can I get the list of dates of second Tuesdays in a current year

221 Reputation points
2021-04-28T13:45:45.23+00:00

How can I get the list of dates of second Tuesdays in a current year. I am looking for sql to get the list

Regards
Pol

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions

1. 8,126 Reputation points
2021-04-28T14:44:26.303+00:00

Try this:

``````;WITH CTE_Recursive_Date AS (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS [Date]
UNION ALL
SELECT DATEADD(DAY, 1, [Date]) AS [Date]
FROM CTE_Recursive_Date
WHERE [Date] < GETDATE() - 1
)

SELECT CAST(t.[Date] AS date) AS [Date]
FROM (
SELECT [Date], ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date]) AS Nuumber_Of_Week
FROM CTE_Recursive_Date
WHERE DATENAME(weekday, [Date]) = 'Tuesday'
) AS t
WHERE t.Nuumber_Of_Week = 2
OPTION (MAXRECURSION 0);
``````

2. 98,726 Reputation points
2021-04-28T21:02:14.577+00:00

Once you have a table of dates this becomes a quite trivial problem. For a one-off, Guoxiong's recursive CTE can do, but if you need it in more places, it is better to make it a fixed table.

3. 24,176 Reputation points
2021-04-29T02:14:56.293+00:00

Welcome to Microsoft Q&A!

As mentioned by Erland, you could create a fixed calendar table and query from this table based on your changing requirement.

``````drop table if exists CALENDAR

create table CALENDAR
(Date date,
WeekNo int,
DateName varchar(20));

DECLARE @StartDate  date = '20200101';
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM seq
)
INSERT INTO DBO.CALENDAR
SELECT DATE= CONVERT(date, d) ,
(DATEPART(week,  CONVERT(date, d)) - DATEPART(week, DATEADD(day, 1, EOMONTH( CONVERT(date, d), -1)))) + 1
,DATENAME(weekday,  CONVERT(date, d)) DateName
FROM D
ORDER BY DATE
OPTION (MAXRECURSION 0);

SELECT date FROM DBO.CALENDAR
where WeekNo=2 and DateName='Tuesday' and year(date)=year(getdate())
``````

Output:

``````date
2021-01-05
2021-02-09
2021-03-09
2021-04-06
2021-05-04
2021-06-08
2021-07-06
2021-08-10
2021-09-07
2021-10-05
2021-11-09
2021-12-07
``````

Best regards
Melissa

4. 1,886 Reputation points
2021-04-28T21:31:32.2+00:00

If we start with a function to get the N'th week day:

`````` Create Function [dbo].[fnGetNthWeekDay] (
@theDate datetime
, @theWeekday int
, @theNthDay int
)
Returns Table
As
Return

/* ===========================================================================================
Author:      Jeff Williams
Created:     10/17/2019
Description: Returns the Nth day of the week from the beginning or end of the month of the
specified input date (@theDate).

This function was originally developed by Peter Larrson - the difference between his
version and this version are:

1) Removed hard-coded string date literals (e.g. '1900-01-01' and '1753-01-01')
a) Using the integer date value is natively converted in the execution plans
where the string date literals are implicitly converted.
2) Removed the derived table and moved the logic to the CROSS APPLY
3) Removed the extra SIGN function
4) Returns both theDate and nthDate

Input Parameters:
@theDate        datetime to calculate the Nth day from
@theWeekDay     the weekday to calculate
1 = Monday, 2 = Tuesday, ..., 7 = Sunday
@theNthDay      the week number of the month
Valid values: -5, -4, -3, -2, -1, 1, 2, 3, 4, 5

Example Calls:
Select * From dbo.fnGetNthWeekDay('2020-09-01', 1,  1); -- Monday of 1st week
Select * From dbo.fnGetNthWeekDay('2020-11-01', 4,  4); -- Thursday of 4th week
Select * From dbo.fnGetNthWeekDay('2020-05-01', 1, -1); -- Last Monday of month

Revision History
Date       Edited By       Change
---------- --------------- --------------------------------------------------------------
10/17/2019 Jeff Williams   Created
=========================================================================================== */

Select theDate = @theDate
, dt.nthDate
From (Values (dateadd(month, datediff(month, @theNthDay, @theDate), 0)))                             As mm(FirstOfMonth)
Cross Apply (Values (dateadd(day, 7 * @theNthDay - 7 * sign(@theNthDay + 1)
+ (@theWeekday + 6 - datediff(day, -53690, mm.FirstOfMonth) % 7) % 7, mm.FirstOfMonth)))    As dt(nthDate)
Where @theWeekday Between 1 And 7
And datediff(month, dt.nthDate, @theDate) = 0
And @theNthDay In (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5);
``````

Then it becomes very easy to generate a year's data:

``````   With months(num)
As (
Select t.n
From (
Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As t(n)
)
Select *
From months As m
Cross Apply dbo.fnGetNthWeekDay(datetimefromparts(year(getdate()), m.num, 1, 0, 0, 0, 0), 2, 2) As t;
``````