Happy Cinco de Mayo and Happy Taco Tuesday!

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.

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, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am an architect at Wayfair.

Leave a Reply

Your email address will not be published. Required fields are marked *