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

问题:

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?

回答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)

回答2:

DECLARE @now DateTime
SET @now = '2013-06-20' -- you could say GETDATE()
SELECT DATEADD(day, (DATEPART(week, @now) * 7 + DATEPART(weekday, @now)) - (DATEPART(week, DATEADD(year, -1, @now)) * 7 + DATEPART(weekday, DATEADD(year, -1, @now))), DATEADD(year, -1, @now))

The result is:

2012-06-21 00:00:00.000

回答3:

dateadd('day',-364,@now) hope this can help you