Aaron Bertrand makes the case for calendar tables:
In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.
Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.