Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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
0
nmarano
Asked:
nmarano
2 Solutions
 
becraigCommented:
why not set the date as a variable before the query then pass the variable into the query.
0
 
JohnHowlettCommented:
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
 
becraigCommented:
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
 
nmaranoAuthor Commented:
Thanks for your help
0
 
_agx_Commented:
>   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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now