Computing Holidays

Kevin Feasel



Gerald Britton has a few ways of calculating holidays for date dimensions:

Notice also that I use CROSS APPLY as an expression evaluator.  This keeps the code a little DRY-er.  In OOP-speak, I’ve encapsulated what varies.

Now that I’ve got a basic date table, let’s see about updating those holiday columns.   I’ll do it with an UPDATE command here, though it is possible to make them computed also — it just gets a little messy since there are so many different holidays with different calculations.  Let’s start with Thanksgiving.  In Canada, Thanksgiving Day is the second Monday in October.  In the US, it’s the fourth Thursday in November.  I’ve seen some interesting approaches to solving the problem.  One (for US Thanksgiving) looks like this:

I like the Easter formula.  Click through for details.

Related Posts


Kenneth Fisher explains a couple of database name functions in SQL Server: I’d never seen ORIGINAL_DB_NAME until recently and I thought it would be interesting to highlight it out, and in particular the difference between it and DB_NAME. I use DB_NAME and DB_ID fairly frequently in support queries (for example what database context is a query running from or what database are […]

Read More

Using STRING_AGG In SQL Server 2017

Derik Hammer talks about one of the nicer T-SQL additions in SQL Server 2017: Creating comma separated strings from a column, or delimited strings as I like to call it, is a very common problem in SQL. Beginning with SQL Server 2017 and Azure SQL Database, there is now another option to the existing set of solutions, STRING_AGG(). I […]

Read More


November 2016
« Oct Dec »