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

Question

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?

select *

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

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

</cfquery>

BKBK · Accepted Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.