andrew gothard (5/6/2014)
Personally for things like this, I prefer a DateDim / Calendar table - apart from the fact this makes the SQL CLeaner, and you only have to do the calcs once then look them up - it's also a lot cleaner when things like Financial Year End changes to just run an update against the table than change all the code
Totally agree. If folks would migrate to this model, the questions and problems around date processing would largely disappear. Also, its cheap to do. Only a few thousand rows (for some years back and some in the future). Capture business rules (fiscal months, weeks and quarters and fiscal year end). Common text representations of dates, days and months. flags for statutory holidays, company holidays, weekends etc.
Best of all...one source for the rules and conversions ==> only one thing to maintain. I've seen too many fancy date calculations that give wrong results in corner (or even the general!) cases. Using a Date/Calendar table eliminates 99% of the calculations and all of the bugs the calculations cause.
Really I don't know why this isn't the go-to solution for every business.