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

Polachan Paily 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
{count} votes

4 answers

Sort by: Most helpful
  1. Guoxiong 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);
    
    0 comments No comments

  2. Erland Sommarskog 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.

    0 comments No comments

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

    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.

    0 comments No comments

  4. Jeffrey Williams 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;
    
    0 comments No comments