ColdFusion

dateDiff problem

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2009

Posts: 5

Reputation: grabit is an unknown quantity at this point

Solved Threads: 0

grabit

Offline

Newbie Poster

dateDiff problem

Sep 14th, 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:

 Help with Code Tags 
 coldfusion Syntax (Toggle Plain Text) 
<cfset getdate = Now()> 
<!---Get date for listing period---> 
<cfset renewYear = #DatePart("yyyy", getdate)# -1
<cfset getdate = Now()> 
<!---Get date for listing period---> 
<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:

 Help with Code Tags 
 coldfusion Syntax (Toggle Plain Text) 
<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())#
<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

Last edited by peter_budo; Sep 16th, 2009 at 2:51 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.