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).

  • Check if a SQL Select statement returns no rows
  • Dynamic columns not getting created in T-SQL
  • How Read JSON Data From URL In SQL Server
  • Query access database from SQL management studio without using linked servers
  • SQL Server Management Studio alternatives to browse/edit tables and run queries
  • Generate a row in an aggregation query for a date with no results against it
  • How can this be done in T-SQL?

    3 Solutions collect form web for “How do you find the same weekday last year in SQL?”

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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.