Happy Cinco de Mayo and Happy Taco Tuesday!
May 5th, 2020
Happy Cinco de Mayo and Happy Taco Tuesday!
May 5th, 2020
 
 
Image shamelessly stolen from North Forty News

It's not often Cinco de Mayo falls on a Tuesday. What does this have to do with SQL Server? Well, this morning I caught myself wondering, "How often do these intersect to provide for an even more glorious feast?" Well, that's a question a quick T-SQL query can answer.

First, I start with a sequence of 61 numbers from -30 to +30:

;WITH nums(n) AS 
(
  SELECT -30 UNION ALL SELECT n + 1 FROM nums WHERE n < 30
)
SELECT n FROM nums;

This produces 61 rows from -30 to 30, including 0.

Then we can convert those to a specific date each year: 30 years into the past, this year, and 30 years into the future, based on "today" (May 5th of this year, in case you are playing with this on a different day):

DECLARE @ThisCincoDeMayo date = DATEFROMPARTS(YEAR(GETDATE()), 5, 5);
 
;WITH nums(n) AS 
(
  SELECT -30 UNION ALL SELECT n + 1 FROM nums WHERE n < 30
),
CincoDeMayos([date]) AS
(
  SELECT DATEADD(YEAR, -n, @ThisCincoDeMayo)
  FROM nums
)
SELECT d 
  FROM CincoDeMayos
  ORDER BY [date];

This returns a series of dates, from 1990-05-05 through 2050-05-05. Discovering which of those fall on a Tuesday is a simple matter of using DATENAME (you will have to adjust if you're not using one of the English languages, but I think this is easier than using DATEPART and adjusting for DATEFIRST).

DECLARE @ThisCincoDeMayo date = DATEFROMPARTS(YEAR(GETDATE()), 5, 5);
 
;WITH nums(n) AS 
(
  SELECT -30 UNION ALL SELECT n + 1 FROM nums WHERE n < 30
),
CincoDeMayos([date], dayname) AS
(
  SELECT DATEADD(YEAR, -n, @ThisCincoDeMayo),
         DATENAME(WEEKDAY, DATEADD(YEAR, -n, @ThisCincoDeMayo))
  FROM nums
)
SELECT [date], dayname 
  FROM CincoDeMayos
  ORDER BY [date];

Results:

date        dayname
==========  ==========
1990-05-05  Saturday
1991-05-05  Sunday
1992-05-05  Tuesday
1993-05-05  Wednesday
1994-05-05  Thursday

2019-05-05  Sunday
2020-05-05  Tuesday
2021-05-05  Wednesday

2046-05-05  Saturday
2047-05-05  Sunday
2048-05-05  Tuesday
2049-05-05  Wednesday
2050-05-05  Thursday

Now, just add a WHERE clause:

 ... FROM CincoDeMayos WHERE dayname = 'Tuesday' ...

Results:

date        dayname
==========  ==========
1992-05-05  Tuesday
1998-05-05  Tuesday
2009-05-05  Tuesday
2015-05-05  Tuesday
2020-05-05  Tuesday
2026-05-05  Tuesday
2037-05-05  Tuesday
2043-05-05  Tuesday
2048-05-05  Tuesday

As a fun aside, there's a mathematical pattern there, but it's a bit more sporadic than I expected. 6-11-6-5-6-11-6-5.

Anyway, an interesting quick exercise that is a little less cumbersome to work through than scrolling through a date picker or examining physical calendars, and a fun distraction from real life, even if only for a moment.

Enjoy the day, and I hope you're safe and healthy.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.