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.d
select *
from prospects
where email = '#form.email#'
and enterDate = #dateFormat(now(),'yyyy-mm
</cfquery>
Thanks
Nick
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.d
select *
from prospects
where email = '#form.email#'
and enterDate = #dateFormat(now(),'yyyy-mm
</cfquery>
Thanks
Nick
ColdFusion Language
Last Comment
_agx_
why not set the date as a variable before the query then pass the variable into the query.
ASKER CERTIFIED SOLUTION
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
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 questionSOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Thanks for your help
> 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">
...
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