# When will the next 13th fall on a Friday?

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

• 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()`

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

Thanks,

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

• "UNION ALL" is a requirement in CTE

• `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.

• 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)?

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

• 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.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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)`

• 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'))`

• 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'))`

• Nigel,

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

Thanks,

Bill

• 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