Query with dates issue

[brianism]

Can anyone tell me why my query here is will not pull out an item that falls on today's date? It is supposed to show items if the calendardate is greater than or equal to today's date.  It's not showing the "equal to".

<cfquery name="getevents" datasource="mydb">
    select * from calendar
    where calendardate >= #now()#
    order by eventname, calendardate
</cfquery>

[Guest · Accepted solution]

now() includes the date and time of right now... So if your item was today

but 3 hours ago, your query will not return it.  Plus, if you want to do

that, use the SQL version which is for MSSQL getDate()

For your issue, use this:

<cfquery name="getevents" datasource="mydb">
    select * from calendar
    where calendardate >= '#DateFormat(now(), "mm/dd/yyyy")# 00:00:00'
    order by eventname, calendardate
</cfquery>

Regards,

Dave @ Oyova Software

http://www.oyova.com - Web Design and Development

[Dave Watts]

The Now() function returns a date and time. If you're only setting the date for other values, the time will be the beginning of that day, and Now() will return a later value instead of an equal value. If you're only comparing dates, you need to just use dates and not times.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

[brianism]

Thanks guys,

I ended up using this and it worked:

<cfquery name="getevents" datasource="mydb">
    select * from calendar
    where calendardate >= CURDATE()
    order by eventname, calendardate
</cfquery>