Skip to main content
B
brianism
Inspiring
October 13, 2010
Answered

Query with dates issue

  • October 13, 2010
  • 3 replies
  • 1014 views

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>

    This topic has been closed for replies.
    Correct answer

    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

    3 replies

    B
    brianismAuthor
    Inspiring
    October 13, 2010

    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>

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    October 13, 2010

    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.

    Dave Watts, Eidolon LLC
    A
    Anonymous
    Correct answer
    October 13, 2010

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices