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
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:
Date
OneQuarterAgo
diff\_days
2009-05-01
2009-02-01
\-89
2009-05-02
2009-02-02
\-89
2009-05-03
2009-02-03
\-89
2009-05-04
2009-02-04
\-89
2009-05-05
2009-02-05
\-89
2009-05-06
2009-02-06
\-89
2009-05-07
2009-02-07
\-89
2009-05-08
2009-02-08
\-89
2009-05-09
2009-02-09
\-89
2009-05-10
2009-02-10
\-89
2009-05-11
2009-02-11
\-89
2009-05-12
2009-02-12
\-89
2009-05-13
2009-02-13
\-89
2009-05-14
2009-02-14
\-89
2009-05-15
2009-02-15
\-89
2009-05-16
2009-02-16
\-89
2009-05-17
2009-02-17
\-89
2009-05-18
2009-02-18
\-89
2009-05-19
2009-02-19
\-89
2009-05-20
2009-02-20
\-89
2009-05-21
2009-02-21
\-89
2009-05-22
2009-02-22
\-89
2009-05-23
2009-02-23
\-89
2009-05-24
2009-02-24
\-89
2009-05-25
2009-02-25
\-89
2009-05-26
2009-02-26
\-89
2009-05-27
2009-02-27
\-89
2009-05-28
2009-02-28
\-89
2009-05-29
2009-02-28
\-90
2009-05-30
2009-02-28
\-91
2009-05-31
2009-02-28
\-92
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.
