Just for orientation, here is a quick T-SQL code to find out the next Fridays, that will be on 13th day of the month.
SET DATEFIRST 1 DECLARE @d DATE = GETDATE() WHILE (@d < '2099/01/01') BEGIN IF DATEPART(dw,DATEFROMPARTS(YEAR(@d), MONTH(@d), 13)) = 5 BEGIN SELECT @d AS Friday13 END SET @d = DATEADD(m,1,@d) END
I set datefirst = 1 just in case you might have any other regional/language setting, noting that week starts with monday. In accordance with this, I can part the date and look for 5th day of the week (which it will be friday in this case).
Can you come up with shorter function in T-SQL that it would list dates of next couple of Friday the 13th? For example with OVER clause, CROSS APPLY, JOIN, CTE or CURSOR? only imagination is the limit 🙂 Tally table can also be used.
TomazTsql 
How about :
;WITH lv0 AS ( SELECT 0 AS g UNION ALL SELECT 0 ) , –2
lv1 AS ( SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b ) , — 2^ = 4
lv2 AS ( SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b ) , — 4^ = 16
lv3 AS ( SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b ) , — 16^ = 256
lv4 AS ( SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b ) , — 256^ = 65,536
Nums AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS n FROM lv4 )
SELECT DATEADD(DAY, n – 1, GETDATE()) AS ‘Friday13th’ FROM Nums
WHERE DATEPART(DAY, DATEADD(DAY, n – 1, GETDATE())) = 13 AND DATEPART(dw, DATEADD(DAY, n – 1, GETDATE())) = 6
LikeLike
This one is smaller
;WITH Nums AS (SELECT TOP 15000 ROW_NUMBER() OVER (ORDER BY a.object_name) n FROM sys.dm_os_performance_counters a CROSS JOIN sys.dm_os_performance_counters b)
SELECT DATEADD(DAY, n – 1, GETDATE()) AS ‘Friday13th’ FROM Nums
WHERE DATEPART(DAY, DATEADD(DAY, n – 1, GETDATE())) = 13 AND DATEPART(dw, DATEADD(DAY, n – 1, GETDATE())) = 5
LikeLike
Ok 2 lines:
;WITH Nums AS (SELECT TOP 1000 DATEADD(Month,ROW_NUMBER() OVER (ORDER BY a.object_name)- 1, ‘20160101’ ) n FROM sys.dm_os_performance_counters a CROSS JOIN sys.dm_os_performance_counters b)
SELECT n +12 AS ‘Friday13th’ FROM Nums WHERE DATEPART(dw, n) = 7
LikeLike
SELECT n+12 FROM (SELECT DATEADD(M,ROW_NUMBER() OVER (ORDER BY id)-1,’20160101′)n FROM sys.syscolumns)N WHERE DATEPART(dw, n)=7
LikeLike
So far the shortest was submitted on twitter by Mladen Prajdic: https://twitter.com/MladenPrajdic/status/731127126687125504
with code:
declare @d datetime=42501
while @d<=73049
begin
set @d+=7
if(day(@d)=13) select @d
end
Key here is setting date to 42501 (or any other Friday the 13th; in this it is today – 13.5.2016) and just increment by one week – it will always be Friday – until you find next friday on 13th day of the month.
Congrats, Mladen.
LikeLike