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:
- 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.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.