dateDiff problem

Report · Sep 14, 2009

Hi Folks
In my db i have a datelisted column formatted to short date ie dd/mm/yyyy)

My theory is that say if that datepart year is 2007 i want to list all dates that are then due on the same date in the year 2009.

I can make this work manually by using:
<cfset getdate = Now()>

<cfset renewYear = #DatePart("yyyy", getdate)# -1

What i want to do is make this dynamic. and i am trying to now is use dateDiff to work out the -1 part of the above.

I currently have:
a query as follows:
<cfquery name="getrenew" datasource="#dsn#">
SELECT ID, datelisted, renewaldue, region, area, hitcount, webcount, category, busname, buscontact, busphone, mailto, paymethod
FROM listings
WHERE Year(datelisted) = #DatePart("yyyy", Now())# - #dateDiff("yyyy",datelisted,Now())#

in theory this should give me (as an example only)
if the year datelisted is 2007
Year(datelisted) = 2009-2

I am getting an error though that tells me that datelisted is undefined in the dateDiff function

Can anyone please help or see another way to do this

cheers and thanks in advance
Grabit

Thanks to you all

Report · Sep 14, 2009

WHERE Year(datelisted) = #DatePart("yyyy", Now())# -

#dateDiff("yyyy",datelisted,Now())#

you're trying to use cf functions on your WHERE clause on the db server. until

the cfquery completes, cf server won't know what "datelisted" is. use your db's

functions instead.