Search code examples
sqlsql-serverssrs-2008datediff

Date from Thursday to last Thursday

I am cracking my skull as to how to query records where the date field returns data from Thursday to the previous 7 days (Thursday). This is for reporting and I need assistance.

I had a look at the DATEDIFF function but not too sure how to have this date generic to run the SSRS report automatically.

Solution 
  • SELECT * 
FROM <TABLE_NAME> 
WHERE 
        <YOURDATE> >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -4) AND 
        <YOURDATE> <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 3)

    The code will select all the rows from the current Thursday to the previous Thursday.

    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -4): Gives the date for the last Thursday DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 3): Gives the date for the current Thursday