question

PolachanPaily-2805 avatar image
0 Votes"
PolachanPaily-2805 asked MelissaMa-msft commented

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PolachanPaily-2805,

Could you please validate all the answers so far and provide any update?

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @PolachanPaily-2805,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.