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:

Now, just add a WHERE clause:

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

Results:

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.