CODEWITHSUNDEEP
sqlsql-servert-sql
David Duffett
David Duffett

Reputation: 3145

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?

Upvotes: 6

Views: 13528

Answers (3)

Rachel
Rachel

Reputation: 181

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

Upvotes: -2

gbn
gbn

Reputation: 432271

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)

Upvotes: 25

David Duffett
David Duffett

Reputation: 3145

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

Upvotes: 0

Related Questions