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")#

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


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 works fine now Smile  Thanks for your help.


EasyCFM.COM ColdFusion Forums :