2

i have a date field i have to manipulate, basically i have a query that gets everything and from that query i have to assign a variety of things, one of which is to get late counters. to accomplish this i have to calculate everything that was less than or equals to 90 days or greater than or equals to 90 days.

how can i do this

the answer below is not what i was looking for

user2799983
  • 71
  • 9
  • 1
    Post your actual code that throws the error. – Matt Busche May 30 '14 at 23:09
  • 2
    Why are you trying to convert it CF? What was wrong with the results of your original query? Do not confuse the SQL [dateDiff](http://msdn.microsoft.com/en-us/library/ms189794.aspx) function and the [CF function](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6dd1.html). Though they share the same name, they are very different. The sql function operates within a db, on *multiple* records. While the CF version runs on the CF server, and only compares on a single set of dates. Also, there is no `getDate()` function in CF. That is a SQL Server function. – Leigh May 31 '14 at 01:06
  • 1
    *RE: for the past 90 days* Your sql logic is wrong. It looks for records older than 90 days. Also, how you construct the `where` filter can impact query performance. See: [What makes a SQL statement sargable?](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) . – Leigh May 31 '14 at 19:12

1 Answers1

4

All the dates for the last 90 days?

select updateDate active
from a 
where xxxdate >= <cfqueryparam cfsqltype="cf_sql_date" 
value = "#dateadd('d', -90, now())#"> 

and xxxdate < <cfqueryparam cfsqltype="cf_sql_date" 
value = "#dateadd('d', 1, now())#">

Note that this:

cfsqltype="cf_sql_date"

will strip the time portion away from the results of the datediff function result.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • 2
    -1. I am surprised this got up voted in its current form. Right idea, but there are several problems with the code. Lol, Dan you have got to start testing this stuff before posting! At the very least mention it was not tested ;-) – Leigh May 31 '14 at 19:03
  • I only found 2, wrong function and missing quotes. Each problem occurred twice. Either I am missing something else, or we have different interpretations of "several". – Dan Bracuk Jun 01 '14 at 11:25
  • 1
    If you are asking whether there were other errors, testing the query would answer that question. Otherwise, it is a matter of semantics. Four (4) errors already equates to "several", in my book, but I do not see that the distinction makes much of a difference here. – Leigh Jun 02 '14 at 03:54