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

Question

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

Answer 1

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

Answer 2

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.

Answer 3

Hi @Polachan Paily ,

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.

Please also refer below:

drop table if exists CALENDAR  
  
create table CALENDAR  
(Date date,  
WeekNo int,  
DateName varchar(20));  
  
DECLARE @StartDate  date = '20200101';  
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 10, @StartDate));  
;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

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

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;