Simple: select the 13th of every month in the year, and then exclude those which don't fall on a Thursday.
For SQL Server:
WITH cteThirteens (Thirteenth) As
SELECT DATEFROMPARTS(@year, 1, 13)
UNION SELECT DATEFROMPARTS(@year, 2, 13)
UNION SELECT DATEFROMPARTS(@year, 3, 13)
... continue for all 12 months ...
((DATEPART(dw, Thirteenth) + @@DATEFIRST - 1) % 7) = 4
You need to take the
value into account, since it will alter the weekday numbers returned by the
DATEPART (Transact-SQL) - SQL Server | Microsoft Docs
Also note that your expected results are incorrect:
- 13th March 2020 was a Friday, not a Thursday, so it should not be included.
- 13th December 2020 was a Sunday, not a Thursday, so it should not be included.
- 13th August 2020 was a Thursday, so it's missing from your expected output.