CF/SQL for Advanced Search #DateDiff()#

[metaphiz]

I'm trying to set up an advanced search on my site and I want to search
with parameters for how old a product is based on the Products table's
DateCreated field (a standard ColdFusion date/time object).  The form
options for users to select are 'unlimited' - 10000 days, '90 days', '14
days', '7 days', and '3 days'.

Here's the code I'm trying and so far I'm getting the same results
whether I select a date or not.  It doesn't appear to be processing any
date info:

<cfif ParameterExists(SearchLength)>     
     <cfif SearchLength IS NOT '10000'>
          <cfif SearchLength IS '90'>
          AND
#DateDiff(D,Now(),CreateODBCDate(Products.DateCreated))# < 90
          <cfelseif SearchLength IS '14'>
          AND
#DateDiff(D,Now(),CreateODBCDate(Products.DateCreated))# < 14
          <cfelseif SearchLength IS '7'>
          AND
#DateDiff(D,Now(),CreateODBCDate(Products.DateCreated))# < 7
          <cfelseif SearchLength IS '3'>
          AND
#DateDiff(D,Now(),CreateODBCDate(Products.DateCreated))# < 3
          </cfif>
     </cfif>
</cfif>

 

[webmigit]

<cfif ParameterExists(SearchLength)>      
     <cfif val(SearchLength) lte 90 and val(SearchLength) gt 0>
          AND 
DateDiff(day,Products.DateCreated,getDate())# < #val(searchLength)#
     </cfif> 
</cfif>

You're very close.. but you used #DateDiff()# cf-side rather than DateDiff() sql-side..

My resource on this was:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp

One major change you'll see is I shortened the output.. It only runs if searchlength is <= 90 or > 0.. Because its supposed to be a number, we can safely use val().. and here's what val does.. Val() returns the number if its a valid number (nothing but numbers and possibly one decimal) and returns 0 if its not a valid number (say someone tries to hack via url.. this protects that).

You may have to reverse getDate() and DateCreated in their parameter positions, I'm not sure which is right in your case.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

Hmm I was just thinking you my have a reason for only wanting those set increments for datediff.. here's a way..

change

<cfif val(SearchLength) lte 90 and val(SearchLength) gt 0>

to

<cfif ListFind("90,14,7,3",val(SearchLength))>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[metaphiz]

Thanks. Oh so what you are saying is that I was illegally trying to use CF functions within a <CFQUERY> where there should have been SQL?

I'm getting an error on the getdate() function:

AND DateDiff(d,DateCreated,getdate()) < 7

I noticed your reference was SQLServer but I'm using Access. Maybe that's why I'm getting the following error?:

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'getdate' in expression.
The error occurred on line 100.

 

[ecobb]

I haven't used Access in so long I can't remember, but is NOW() an Access function? Try using NOW() instead of GETDATE().



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher