Topic: using DateFormat(now() 'mm/dd/yyyy in cfquery


rickaclark    -- 10-24-2009 @ 5:59 AM
  I am trying to pull info from my access database that is the same as the current date. I used the following code with no results. What am I doing wrong?

<cfquery name="getEvent" datasource="#dsn#">
select CalendarDate, eventname, starttime
from calendar
where CalendarDate =  #DateFormat(now(), "mm/dd/yyyy")#
</cfquery>

The dateformat is in the same format as what is stored in the database, yet it will not display. Any ideas before I loose the rest of my hair?


megan    -- 10-24-2009 @ 9:01 AM
  First of all the DateFormat() function is only for people and not for databases - you would only use it to format a dat AFTER that date is retrieved from the database to make it look pretty for your users.

Second, the Now() function retrieves both date and time so unless a record is put in at the exact same second as you are retrieving the data then you will never find a match

BUT luckily Coldfusion has some other date/time functions that will come in handy to help you out livedocs is your friend

try

WHERE Year(CalendarDate) = #Year(now())#
            AND Month(CalendarDate) = #Month(now())#
            AND Day(CalendarDate) = #Day(now())#

PS you could also try datePart()

-------------------------------------------------------
The only place where your dream becomes impossible is in your own thinking. ~ Robert H. Schuller

This message was edited by megan on 10-24-09 @ 9:05 AM


rickaclark    -- 10-25-2009 @ 6:07 AM
  Excellent..it works fine now Smile  Thanks for your help.

Rick


EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=30&Topic=14555