Highlighted

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

New Here ,
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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

1.3K

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

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

New Here ,
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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

1.3K

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
Nov 21, 2015 0
Adobe Community Professional ,
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
Reply
Loading...
Nov 22, 2015 1
New Here ,
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
Reply
Loading...
Nov 22, 2015 0
Adobe Community Professional ,
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
Reply
Loading...
Nov 22, 2015 0
New Here ,
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
Reply
Loading...
Nov 22, 2015 0
New Here ,
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
Reply
Loading...
Nov 22, 2015 0
BKBK LATEST
Adobe Community Professional ,
Nov 22, 2015

Copy link to clipboard

Copied

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
Reply
Loading...
Nov 22, 2015 0