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
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
Could you please validate all the answers so far and provide any update?
Thank you for understanding!
Best regards
Melissa
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.
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;
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.
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);
14 people are following this question.