When will the next 13th fall on a Friday?

  • Ashka Modi

    Old Hand

    Points: 377

    Comments posted to this topic are about the item When will the next 13th fall on a Friday?

    Thanks,

    Ashka Modi
    Software Engineer || credEcard Technologies (india) Pvt. Ltd.

  • Rimvydas Gurskis

    Valued Member

    Points: 52

    How about set-based approach:

    SET DATEFIRST 1

    ;WITH days(Date) AS

    (

    --Get 13th of this month

    SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)

    UNION ALL

    --Get 13th of next months

    SELECT DATEADD(MONTH, 1, Date) FROM days

    )

    SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()

  • Ashka Modi

    Old Hand

    Points: 377

    Good one! 🙂 but I am not much comfortable with UNION ALL. what you say?

    Thanks,

    Ashka Modi
    Software Engineer || credEcard Technologies (india) Pvt. Ltd.

  • Rimvydas Gurskis

    Valued Member

    Points: 52

    "UNION ALL" is a requirement in CTE

  • Ashka Modi

    Old Hand

    Points: 377

    SET DATEFIRST 1

    ;WITH days(Date) AS

    (

    --Get 13th of this month

    SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)

    UNION ALL

    --Get 13th of next months

    SELECT DATEADD(MONTH, 1, Date) FROM days

    )

    SELECT * FROM days

    SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()

    Have you check this? check Execution plan also.

    Thanks,

    Ashka Modi
    Software Engineer || credEcard Technologies (india) Pvt. Ltd.

  • Rimvydas Gurskis

    Valued Member

    Points: 52

    I'm not sure what you mean.

    "SELECT * FROM days" will fail because of recursion limit. But original idea was to get only the first Friday, the 13th. As there is always at least one such day in a year, there will be no more than 23 iterations.

    Is there something wrong with execution plan?

    Sorry for asking, but are you familiar with common table expressions (CTE)?

  • nigel.

    SSChampion

    Points: 11593

    Another set based solution, this time using the good old Tally table[/url]. ((c) Jeff Moden et al. 19xx-2009)

    SELECT TOP 1

    DATEADD(dd,N-1,GETDATE())

    FROM

    Tally

    WHERE

    n < 366 --== One years worth of days

    AND

    DAY(DATEADD(dd,N-1,GETDATE())) = 13

    AND

    --==

    --== Using @@DATEFIRST and the modulus operator we don't need to change or

    --== assume anything about the current DATEFIRST setting

    --==

    (@@DATEFIRST + DATEPART(dw,DATEADD(dd,N-1,GETDATE()))) %7 = 6

    ORDER BY

    N

    The execution plan for this indicates 100% of the query is spent doing a clustered index seek (nice!). That's if you build your tally table with a clustered index of course, as demonstrated in Jeff's tally table article.

    And, as I indicated in the comments, you don't need to mess about with the DATEFIRST setting.

  • Jeff Moden

    SSC Guru

    Points: 995164

    Rimvydas Gurskis (8/27/2009)


    How about set-based approach:

    SET DATEFIRST 1

    ;WITH days(Date) AS

    (

    --Get 13th of this month

    SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)

    UNION ALL

    --Get 13th of next months

    SELECT DATEADD(MONTH, 1, Date) FROM days

    )

    SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()

    Correct me if I'm wrong, but that appears to be a recursive CTE .... which ISN'T set based at all. In fact, recurrsive CTE's are sometimes slower than a memory only While Loop.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Bill Coale-478581

    SSC Enthusiast

    Points: 154

    I agree with Rimvydas' approach.

    And for performance concerns on CTEs on this particular problem, this gives all the years from 1753 to 9999 that have 3 Friday the thirteenths.

    It runs in about 2 to 3 seconds on my small test machine.

    with thirteen (thedate) as(

    select cast('01/13/1753' as datetime) as thedate

    union all

    select DATEADD(mm, 1, thedate)

    from thirteen

    where thedate < '01/01/9999'

    )

    select year(thedate) yr,

    count(*) cnt

    from thirteen

    where DATENAME(dw, thedate) = 'Friday'

    group by year(thedate)

    having count(*) = 3

    OPTION (MAXRECURSION 0)

  • nigel.

    SSChampion

    Points: 11593

    Bill Coale (8/27/2009)


    I agree with Rimvydas' approach.

    And for performance concerns on CTEs on this particular problem, this gives all the years from 1753 to 9999 that have 3 Friday the thirteenths.

    It runs in about 2 to 3 seconds on my small test machine.

    If your concerned with performance I'd go for the tally table approach, The following executes in around 100ms on my machine.

    SELECT

    YEAR(DATEADD(mm,N-1,'17530113')),

    COUNT(*)

    FROM

    dbo.Tally

    WHERE

    N < DATEDIFF(mm,'17530113','99990101')

    AND

    DATENAME(dw, DATEADD(mm,N-1,'17530113')) = 'Friday'

    GROUP BY

    YEAR(DATEADD(mm,N-1,'17530113'))

    HAVING

    count(*) = 3

    ORDER BY

    YEAR(DATEADD(mm,N-1,'17530113'))

  • nigel.

    SSChampion

    Points: 11593

    An this is slightly faster again, by avoiding the string comparison ( = 'Friday'):

    SELECT

    YEAR(DATEADD(mm,N-1,'17530113')),

    COUNT(*)

    FROM

    dbo.Tally

    WHERE

    N < DATEDIFF(mm,'17530113','99990101')

    AND

    (DATEPART(dw,DATEADD(mm,N-1,'17530113')) + @@DATEFIRST) % 7 = 6

    GROUP BY

    YEAR(DATEADD(mm,N-1,'17530113'))

    HAVING

    count(*) = 3

    ORDER BY

    YEAR(DATEADD(mm,N-1,'17530113'))

  • Bill Coale-478581

    SSC Enthusiast

    Points: 154

    Nigel,

    Yes, your tally approach is much faster. 100 ms vs 1400 ms on my machine.

    Thanks,

    Bill

  • RakeshRSingh

    SSCarpal Tunnel

    Points: 4109

    If the current date is equal to 13 then your query will give that as Friday.

    IF (DATEPART(dd, @currDate) 13)

    what about adding one more condition with above one

    OR (DATEPART(dw, @currDate) 1)

    then I think your query is perfect.

    🙂

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply