29

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;
Ashley K
  • 403
  • 1
  • 6
  • 16
  • If this is SQL Server, `BETWEEN CONVERT(date, GETDATE()) AND DATEADD(DD, -7, CONVERT(date, GETDATE()))`. You have conflicting tags. Is this MySQL or Microsoft SQL Server? – Cᴏʀʏ Mar 06 '17 at 20:52
  • 1
    With the conflicting tags this is nearly impossible to answer. The syntax is vastly different between mysql and sql server. – Sean Lange Mar 06 '17 at 20:57
  • Fixed. My bad. It's SQL Server. – Ashley K Mar 06 '17 at 21:06
  • 1
    Any technical reason why every answer suggest BETWEEN. A hits table will never have a future date. Anything wrong with just > DATEADD(DAY, -7, GETDATE()) ? – MortimerCat Jun 21 '18 at 16:03

8 Answers8

41
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;
azizj
  • 3,428
  • 2
  • 25
  • 33
11

Using dateadd to remove a week from the current date.

datex BETWEEN DATEADD(WEEK,-1,GETDATE()) AND GETDATE()
Guillaume Mercier
  • 371
  • 1
  • 10
8

You can subtract 7 from the current date with this:

WHERE datex BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE()
Icculus018
  • 1,018
  • 11
  • 19
2

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;
Ben
  • 5,952
  • 4
  • 33
  • 44
2

Use the following:

WHERE datex BETWEEN GETDATE() AND DATEADD(DAY, -7, GETDATE())

Hope this helps.

BRogers
  • 3,534
  • 4
  • 23
  • 32
0

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;
Hunter McMillen
  • 59,865
  • 24
  • 119
  • 170
0
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))
brooksrelyt
  • 3,925
  • 5
  • 31
  • 54
user10996728
  • 1
0

For MariaDB:

WHERE datex BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND CURDATE()

It helps me.

Josef Večeřa
  • 1
  • 1