Trying to query for records less than or equal to 7 days old

[PushCode]

I want my query to only return records where 'hit_date' is less than or equal to 7 days old. The query as I currently have it, returns 0 records, even though it should be returning several, and it's not throwing an error.

Any ideas?

Here's the query:

SELECT a.site_id, b.url, b.company, b.city, b.state_province, b.site_desc
FROM track_sites_by_day a, track_site_info b
WHERE a.site_id = b.site_id
AND DateDiff(d, #dateformat(now(), 'MM/DD/YYYY')#, a.hit_date) <= 7

 

[webmigit]

Change the last line to...

AND a.hit_date >= DateAdd("d",-7,#dateformat(now(), 'MM/DD/YYYY')#,a.hit_date)

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[NorthStarDA]

i like to use this instead of DateDiff

a.hit_date >= CURDATE() - INTERVAL 7 DAY

seems to work well


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[PushCode]

webmigit,

SELECT a.site_id, b.url, b.company, b.city, b.state_province, b.site_desc
	FROM track_sites_by_day a, track_site_info b
	WHERE a.site_id = b.site_id
	AND a.hit_date >= DateAdd("d",-7,#dateformat(now(), 'mm/dd/yyyy')#,a.hit_date)

Trying your suggestion, I'm getting the error: "The dateadd function requires 3 arguments."

NorthStarDA,

SELECT a.site_id, b.url, b.company, b.city, b.state_province, b.site_desc
	FROM track_sites_by_day a, track_site_info b
	WHERE a.site_id = b.site_id
	AND a.hit_date >= CURDATE() - INTERVAL 7 DAY

Trying your suggestion, I'm getting the error: "'CURDATE' is not a recognized function name."

 

[NorthStarDA]

oh sorry, what database are you on? i use this example with MySQL, CURDATE just produces the current date- the function you would use may be different. for mssql i think it's GETDATE()


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[webmigit]

AND a.hit_date >= DateAdd("d",-7,#dateformat(now(), 'MM/DD/YYYY')#)

I don't really know what I was thinking before...

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[PushCode]

NorthStarDA,

I'm using MSSQL. I tried it with getdate() and I'm now getting this error: "Incorrect syntax near '7'."

SELECT a.site_id, b.url, b.company, b.city, b.state_province, b.site_desc
	FROM track_sites_by_day a, track_site_info b
	WHERE a.site_id = b.site_id
	AND a.hit_date >= getdate() - INTERVAL 7 DAY

Webmigit,

This is now returning results without any error, but, if I change the seven to any other number, my results do not change, which leads me to beleive something is still not correct.


This is actuall the the entire query...I was shortenning it for examples sake but perhaps there is some other problem.

SELECT TOP 5
	a.site_id, SUM(a.hits) AS total_hits, b.url, b.company, b.city, b.state_province, b.site_desc
	FROM track_sites_by_day a, track_site_info b
	WHERE a.site_id = b.site_id
	AND a.hit_date >= DateAdd("d",-7,#dateformat(now(), 'MM/DD/YYYY')#)
	AND b.city = '#page.cityid#'
	AND b.state_province = '#page.state_province#'
	GROUP BY a.site_id, b.url, b.company, b.city, b.state_province, b.site_desc
	ORDER BY SUM(a.hits) DESC

Without the 'AND a.hit_date...' line, it works perfectly returning the top five records with the most hits.

Any thoughts.

 

[NorthStarDA]

ok if you're on sql server, how about

AND a.hit_date >= DATEADD(d, -30, GETDATE())

is hit_date a DATE field? i'm not sure if there is any other problem with that query, but the above should give you last 30 days on hit_date.


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[PushCode]

Bingo NorthStarDA! That solved the problem.

BTW hit_date IS a datetime field.

Thanks for your help guys!