Friday 13th with T-SQL – a “shortest” competition

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.

Advertisements

5 thoughts on “Friday 13th with T-SQL – a “shortest” competition

  1. 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

    Like

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

    Like

  3. 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

    Like

  4. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s