Easy?-Can I parse a timestamp for a Date Query?..

[maverik59]

Hi I have a time stamp looking like "09/01/2003 18:45:32".
All I want is to do a query based on the date.
I am creating a query which tracks users views but as the time is in there it counts a 'hit' each time somebody views
the page for that time, rather than the day. This means i
have to count the hits up from the table...I could do with
a quicker solution!! Perhaps you can help.

Here's my QUERY

Select count(*) as hits, page,page_id,datetime from usertrack
Where page= "/news_article.cfm"
Group by datetime

Many Thanks

 

[r937]

[tt]select count(*) as hits
, page
, page_id
, year(datetime) as theyear
, month(datetime) as themonth
, day(datetime) as theday
from usertrack
where page= "/news_article.cfm"
group
by page
, page_id
, year(datetime) as theyear
, month(datetime) as themonth
, day(datetime) as theday[/tt]

rudy

http://r937.com/

 

[maverik59]

It doesn't seem to like it? error 1064 error near datetime....

I've been looking along these lines but it still does the same!

Select date_format(datetime,"%d %b %y&quot as look ,count(*) as hits,news_subject,news_frontpage,
page_id
from usertrack,news_items
Where page= "/news_article.cfm"
And month(usertrack.datetime) = 4
and news_id = page_id
group by unique_id

 

[maverik59]

You gave me real room for thought, cheers mate - igot it working after a few changes....select
count(*) as hits,
page
, page_id
,dayofmonth(datetime) as theday
, month(datetime) as themonth
, year(datetime) as theyear
, news_subject
,news_frontpage

from usertrack, news_items
where page= "/news_article.cfm"
and page_id = news_id
group by page
, page_id
, theday
, themonth
,theyear

Many thanks