One quarter ago?

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:

1. We had a different idea of quarter than SQL Server
2. We understand why SQL Server works like this
3. 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

Which produces:

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.