2 Replies - 5858 Views - Last Post: 15 January 2009 - 08:28 AM Rate Topic: -----

#1 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

dateadd function

Posted 14 January 2009 - 08:03 AM

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).


Is This A Good Question/Topic? 0
  • +

Replies To: dateadd function

#2 CR250  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 14-June 08

Re: dateadd function

Posted 14 January 2009 - 10:41 AM

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

its looking for the variable d with you want the literal d. You were missing the " "
Was This Post Helpful? 0
  • +
  • -

#3 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: dateadd function

Posted 15 January 2009 - 08:28 AM

View Postxheartonfire43x, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1