When will the next 13th fall on a Friday?

Question

Post reply

When will the next 13th fall on a Friday?

Ashka Modi

Old Hand

Points: 377
More actions
August 18, 2009 at 5:04 pm

#385210

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.

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

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

Rimvydas Gurskis Valued Member Points: 52 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

Rimvydas Gurskis

Valued Member

Points: 52

August 27, 2009 at 3:59 am

#1045397

"UNION ALL" is a requirement in CTE

Ashka Modi Old Hand Points: 377 More actions · Answer 4

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 More actions · Answer 5

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: 11636 More actions · Answer 6

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.

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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.

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

Bill Coale-478581 SSC Enthusiast Points: 154 More actions · Answer 8

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: 11636 More actions · Answer 9

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
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 10

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
How to post questions

Bill Coale-478581 SSC Enthusiast Points: 154 More actions · Answer 11

Nigel,

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

Thanks,

Bill

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 12

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.

🙂