EasyCFM.COM ColdFusion Forums / MS Access Related Issues / using DateFormat(now() 'mm/dd/yyyy in cfquery

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: using DateFormat(now() 'mm/dd/yyyy in cfquery

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

rickaclark
10-24-2009 @ 5:59 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

Excellent..it works fine now Smile  Thanks for your help.

Rick


Website Designed and Developed by Pablo Varando.