Selecting Records for Today only?

[Guest]

I feel this should be an easy one, but everything I try is throwing back zero results.

Basically, I have an existing query that uses an outer join to bring 2 tables together (it's for some very basic site tracking).

Without the 'WHERE' clause, it performs exactly as I'd hoped.  However, I need to be able to narrow down the results to the current day only (and perhaps other date ranges in the near future).

The final output I'm looking for is a list of sites, and the unique visitors for each.  Again, this works fine when I run it without narrowing down to today only.  The 'eventdt' field on the MySQL server is a datetime type.

<cfquery name="getsites" datasource="#datasource#">
SELECT s.id, s.sitename, count(DISTINCT t.uuid) as t_cnt, t.eventdt, t.sitename AS tracksite
FROM sites s
LEFT JOIN tracking t
ON s.sitename=t.sitename
WHERE t.eventdt = #CreateODBCDate(now())#
GROUP BY s.sitename
ORDER BY s.sitename ASC
</cfquery>

Thanks in advance for any pointers 🙂

[Dan_Bracuk · Accepted solution]

today = createdate(year(now()), month(now(), day(now()));

tomorrow = dateadd("d", 1, today);

in your query

where eventdt >= today

and eventdt < tomorrow.

With proper syntax of course.

[Guest]

Excellent, thanks Dan 🙂