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

New Here ,
Nov 21, 2015 Nov 21, 2015

Copy link to clipboard

Copied

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>

Views

1.4K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Nov 22, 2015 Nov 22, 2015
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()---------------------------------Afterthou...

Likes

Translate

Translate
Adobe Community Professional ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Nov 22, 2015 Nov 22, 2015

Copy link to clipboard

Copied

LATEST

My pleasure.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines