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
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];
Now, just add a WHERE clause:
... FROM CincoDeMayos WHERE dayname = '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.