Solved

Returning a query with #now()#

Posted on 2013-01-03
5
254 Views
Last Modified: 2013-01-03
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
0
Comment
Question by:nmarano
5 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 38740303
why not set the date as a variable before the query then pass the variable into the query.
0
 
LVL 4

Accepted Solution

by:
JohnHowlett earned 250 total points
ID: 38740307
Then you will need the exact time as well as the date!!

Otherwise:

   select *
    from prospects
    where email = '#form.email#'
    and enterDate > [original date]  AND enterDate < [orginal date plus 1 day]


Or you can use the SQL BETWEEN statement.
0
 
LVL 29

Assisted Solution

by:becraig
becraig earned 250 total points
ID: 38740327
Once you do that I would use a comparative instead of "="   as that is expecting the exact same value, if you are planning to check for date time to the second then be sure to assign that in your statement as you are asking for

Row value = Variable

If you are looking for a period then you are better off looking in a window vs time to the second.
0
 
LVL 2

Author Closing Comment

by:nmarano
ID: 38740388
Thanks for your help
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38740460
>   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">
    ...
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now