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;
This question is related to
sql
sql-server
database
between
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;
Using dateadd to remove a week from the current date.
datex BETWEEN DATEADD(WEEK,-1,GETDATE()) AND GETDATE()
You can subtract 7 from the current date with this:
WHERE datex BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE()
Use the built in functions:
SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
GROUP BY URLx
ORDER BY Count DESC;
Use the following:
WHERE datex BETWEEN GETDATE() AND DATEADD(DAY, -7, GETDATE())
Hope this helps.
DECLARE @Daysforward int
SELECT @Daysforward = 25 (no of days required)
Select * from table name
where CAST( columnDate AS date) < DATEADD(day,1+@Daysforward,CAST(GETDATE() AS date))
You can use the CURDATE() and DATE_SUB() functions to achieve this:
SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY URLx
ORDER BY Count DESC;
Source: Stackoverflow.com