[xheartonfire43x]

I am doing a query to select everything from a table where the date is less than 30 days ago. But when I use the dateadd function it throws an error saying that D (which is the term for day) can't resolve.

<CFQUERY datasource="serviceimport2" name="mydata" USERNAME="#myusername#" PASSWORD="#mypassword#">
select 
field_3 as ro,
field_102 as rodate,
field_97 as advisornum,
field_6 as customerid,
field_2 as storeid
from #session.myimportfile# 
WHERE field_102 > '#dateadd(d,-30,"#dateformat(now(),"dd/mm/yyyy")#")#'
</CFQUERY>

Quote

Error Diagnostic Information

An error occurred while evaluating the expression:

#dateadd(ww,-4,"#dateformat(now(),"dd/mm/yyyy")#")#

Error near line 11, column 21.

Error resolving parameter d

ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

1. You have misspelled the parameter name, or
2. You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.

The error occurred while processing an element with a general identifier of (#dateadd(ww,-4,"#dateformat(now(),"dd/mm/yyyy")#")#), occupying document position (11:20) to (11:70).

[CR250]

#dateadd("d",-30,"#dateformat(now(),"dd/mm/yyyy")#")#

its looking for the variable d with you want the literal d. You were missing the " "

[sansclue]

xheartonfire43x, on 14 Jan, 2009 - 07:03 AM, said:

WHERE field_102 > '#dateadd(d,-30,"#dateformat(now(),"dd/mm/yyyy")#")#'

That line troubles me for a few reasons:

1. Are you comparing a string to a string value? If so, does the > operator actually work as expected with dd/mm/yyyy format?

2. If field_102 value is a date, you should be comparing it to a date rather than a string.

3. dd/mm/yyyy is a very murky format, which can be interpreted different ways. Usually it is better to use a less ambiguous format like yyyy-mm-dd.

4. DateFormat returns a string, not a date object like now(). That means the dateAdd function will have to convert your dd/mm/yyyy string back into a date object and it may (or may not) interpret your dd/mm/yyyy string correctly. So at the very least, you should swap the positions of the two functions. Then you can be sure you are getting the correct date.