SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Friday the 13th

By Jan Van der Eecken,

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:

Friday the 13th.sql
Total article views: 861 | Views in the last 30 days: 5
 
Related Articles
BLOG

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months ...

FORUM

dateAdd inside where clause

dateAdd function syntax help

FORUM

Wanting first data from within a month...

Selecting Records where you want the first record from a month to join to a larger list of records

FORUM

Finding Current Month's First Day& Last Day

Hi, 1) How to find out the current month's first Date ? 2) How to find out the current month's...

SCRIPT

t-sql first last day date of the month

Returns the First or last day date of the month for a given date.

Tags
t-sql    
 
Contribute