Today at work we discovered that DATEADD can do date math involving quarters. Which, if you ask me, is pretty cool.
In an effort to
avoid work investigate this scenario, we started playing around with our calendar table and DATEADD.
SELECT [Date], DATEADD(q, -1, [Date]) AS OneQuarterAgo, DATEDIFF(d, [Date], DATEADD(q, -1, [Date])) AS diff_days FROM Calendar WHERE y = 2009
Using that query, we figured out that:
- We had a different idea of quarter than SQL Server
- We understand why SQL Server works like this
- God help us all if we have to figure out financial quarters correctly
Basically, SQL Server says “Give me a date that is three months ago and has the same, or closest, ordinal date value.” This is the most apparent when looking at the output of that previous query specifically for May (three months before May is February):
SELECT [Date], DATEADD(q, -1, [Date]) AS OneQuarterAgo, DATEDIFF(d, [Date], DATEADD(q, -1, [Date])) AS diff_days FROM Calendar WHERE y = 2009 AND m = 5
It’s pretty apparent that the last four days in May have the same date produced by the calculation in the OneQuarterAgo column.
There’s not really anything else left to do here, so I’m going to go back to listening to punk covers of Lady Ga Ga songs.