SQL Query Where Date = Today Minus 7 Days

Question

I have a SQL table of hits to my website called ExternalHits. I track the URL as URLx and the date the page was accessed as Datex. I run this query every week to get the count of total hits from the week before, and every week I have to manually change the "between" dates. Is there some way I can change my query so that the "between" dates are something like TODAY AND TODAY-7? Ijust want to not have to manually change the dates every week.

    SELECT URLX, COUNT(URLx) AS Count     FROM ExternalHits     WHERE datex BETWEEN '02/27/2017' AND '03/05/2017'         GROUP BY URLx     ORDER BY Count DESC;  

Answer 1

declare @lastweek datetime declare @now datetime set @now = getdate() set @lastweek = dateadd(day,-7,@now)  SELECT URLX, COUNT(URLx) AS Count FROM ExternalHits WHERE datex BETWEEN @lastweek AND @now GROUP BY URLx ORDER BY Count DESC;  

Answer 2

Using dateadd to remove a week from the current date.

datex BETWEEN DATEADD(WEEK,-1,GETDATE()) AND GETDATE()