Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

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

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>

1.9K
Translate
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

Community Expert , 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

...
Translate
Community Expert ,
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()

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

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

Translate
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

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

Translate
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
Community Expert ,
Nov 22, 2015 Nov 22, 2015

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.

Translate
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

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

Translate
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

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

Translate
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
Community Expert ,
Nov 22, 2015 Nov 22, 2015
LATEST

My pleasure.

Translate
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
Resources