• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

date comparison in cfquery

(using ms access database)

hi, i have the following cfquery:

<cfset currentdate = Now()>

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">      
      SELECT * FROM Leads
      WHERE status = 'Received By Dealer'
      ORDER BY ID DESC            
</cfquery>

i am trying to change this cfquery so it also does a date comparison with the 'date_submitted' field that is in this 'Leads' table.

specifically, i am trying to have it check when status equals 'Received By Dealer' AND the current date (i.e 'currentdate') is not greater than
24 hours after the date it was submitted (i.e 'date_submitted').

how do i add this?

0
Judy Deo
Asked:
Judy Deo
  • 3
  • 2
1 Solution
 
mkishlineCommented:
<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>
0
 
Judy DeoAuthor Commented:
got errors with this cfquery...Date_Add was expecting 3 arguments and the '<' sign seems to be causing a problem since it thinks its a beginning bracket for tags...

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>

so i changed the cfquery to this....

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DateAdd(h, 24, 'date_submitted')
     ORDER BY ID DESC          
</cfquery>

but getting the following error:

Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.



0
 
mkishlineCommented:
try:

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() BETWEEN date_submitted AND DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>
0
 
Judy DeoAuthor Commented:
correction:

so i changed the cfquery to this....

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DateAdd(h, 24, date_submitted)
     ORDER BY ID DESC          
</cfquery>
0
 
Judy DeoAuthor Commented:
ah sorry forgot the single quotes around h in:

 AND Now() < DateAdd('h', 24, date_submitted)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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