SQLServerCentral Article

Friday the 13th

,

Some of us are so supersticious about Friday the 13th, so I thought I'll give you all a heads up so you will be prepared for the next ones coming up in the next 10 years. In this article, we'll look at a quick way to calculate the dates of upcoming Friday the 13ths.

We'll use a tally table to calculate a series of dates. This will be combined with other logic to produce the results.

The Code

The main thing to understand is that if there is a 13th of the month that falls on a Friday, the first day of the month is a Sunday. Therefore, we can limit our checking to those months that have a Sunday as the first day of the month.

With that in mind, let's look at the code. We will start by declaring some variables to get the current first day of the current month. We do with with some date arithmetic.

SET NOCOUNT ON;
-- Today's date
DECLARE @Today date = CURRENT_TIMESTAMP;  
-- How many months have elapsed since 1 January 1900?
DECLARE @StartMonth int = DATEDIFF ( month, '1900-01-01', @Today );
-- Add those to 1 January 1900 to get the start of the current month
DECLARE @StartDate date = DATEADD ( month, @StartMonth, '1900-01-01' ); 

This will give us a @StartDate of 4-1-2018 for this month. This is the basic for future date calculations

The next step is to build up a tally table. To look forward 10 years, we need 120 months. I'll use a simple TVC clause to build out 120 rows.

WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n)
)
SELECT MAX(n)
FROM myTally

This returns 120, and that's what we need. If we were to use DATEADD(month, ) with these numbers, we'd get the months for the next 10 years.

With this, let's combine this code with our restriction that the first of the month needs to be a Sunday. We'll use this restriction. We choose N-1 to start with the current month.

  DATENAME ( dw, DATEADD ( month, N-1, @StartDate ) ) = 'Sunday'

The last part of the code is that we want to return only the date of the Friday the 13th. To do this, we will add 12 days to the first of the month, and that will give us this code:

SET NOCOUNT ON;
DECLARE @Today date = CURRENT_TIMESTAMP;  -- Today's date
DECLARE  @StartMonth int = DATEDIFF ( month, '1900-01-01', @Today ); -- How many months have elapsed since 1 January 1900?
DECLARE  @StartDate date = DATEADD ( month, @StartMonth, '1900-01-01' ); -- Add those to 1 January 1900 to get the start of the current month
WITH cteTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n) -- x 12 rows = 120 rows
)
SELECT
  DATEADD ( day, 12, DATEADD ( month, N-1, @StartDate ) ) AS 'Friday the 13th'
FROM
  cteTally
WHERE
    -- Here is the crux, if the 13th is a Friday, then the first of the month has to be a Sunday
  DATENAME ( dw, DATEADD ( month, N-1, @StartDate ) ) = 'Sunday'  
ORDER BY
  1;

Hope you enjoyed this, and now you know on which dates to watch out for bad luck. 🙂

PS: I can't guarantee you won't come across black cats.

Resources

Rate

4.25 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.25 (4)

You rated this post out of 5. Change rating