Avatar of nmarano
nmarano
 asked on

Returning a query with #now()#

Experts-

I'm having some trouble returning results from a query.  I am using SQL server 2008 and the field (enterDate) is set as a dateTime in the table.  When I run the query below, it does not return any records although there are records in the DB that should be returned.  Any suggestions would be appreciated.  EnterDate field in the db looks like this....
2013-01-03 09:51:09.613


<cfquery name="getBillable" datasource="#application.dsn#">
    select *
    from prospects
    where email = '#form.email#'
    and enterDate = #dateFormat(now(),'yyyy-mm-dd')#      
</cfquery>

Thanks
Nick
ColdFusion Language

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
becraig

why not set the date as a variable before the query then pass the variable into the query.
ASKER CERTIFIED SOLUTION
JohnHowlett

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
nmarano

ASKER
Thanks for your help
_agx_

>   and enterDate > [original date]  AND enterDate < [orginal date plus 1 day]

Small typo. I think he meant  ">= [original date]" rather than "> [original date]"

Edit: That approach works well.  Using cfqueryparam it is a simple as:

    WHERE enterDate >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">
    AND      enterDate < <cfqueryparam value="#dateAdd('d', 1, now())#" cfsqltype="cf_sql_date">
    ...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23