how do I add 7 days to a date and compare it to the current date in a cfquery?

[rickclark54]

I have classes that are no longer active once the date of the class has passed. However, the class will need to be displayed for seven more days in the backend. I tried using the script below to accomplish this but I keep getting an error “variable DATE is undefined.” Am I heading in the right direction or is there a better way to accomplish this?

<cfquery name="getClasses" datasource="#application.dsn#">

select *

from (classReg INNER JOIN classes ON classReg.classID = classes.classID) INNER JOIN instructors ON classreg.instID = instructors.instID

<!--- adding seven days to the class date (date) comparing it to the current date --->

where #DateFormat(DateAdd('d', 7, date),'yyyy-mm-dd')# > #DateFormat(now(), 'yyyy-mm-dd')#

</cfquery>

[BKBK · Accepted solution]

where #DateFormat(DateAdd('d', 7, date),'yyyy-mm-dd')# > #DateFormat(now(), 'yyyy-mm-dd')#

As Coldfusion tells you, rightly so, it knows no variable called 'date'. You are in a query and, apparently, 'date' is a column name. So use SQL functions instead of Coldfusion functions.

In MySQL, the corresponding where-clause is:

WHERE DATE_ADD(date, INTERVAL 7 DAY) > CURDATE()

In SQL Server, the corresponding where-clause is:

WHERE DATEADD(day,7,date) > GETDATE()

---------------------------------

Afterthought: I would rename the column 'date', as this is a reserved word in some Database Management Systems

[rickclark54]

Since there are 20 different classes with different dates, am I better using a cfif with mySQl statement in a loop?

[BKBK]

You may have 20 date values, but you have just one date column. You need just the column name as the argument in the database functions.

[rickclark54]

That is correct, one date column. I will give it a try. Thanks.

[rickclark54]

that is the answer BKBK, as always, thank you.

[BKBK]

My pleasure.