How do you find the same weekday last year in SQL?

Question

Often in sales reports and so on you need to compare this day to the same day last year, but based on the same "weekday", not "day of month".

So for example, today is the 20th June 2013 and a Thursday. I want to see sales for today, versus the same THURSDAY last year (21st June 2012, as opposed to 20th June 2012 which was a Wednesday).

How can this be done in T-SQL?

Answer 1

DECLARE @now Date
SET @now = '2013-06-20' -- your example
SELECT DATEADD(week, -52, @now)

SET @now = '2012-06-21' -- leap year test
SELECT DATEADD(week, -52, @now)