Skip to main content
R
rickclark54
Known Participant
November 21, 2015
Answered

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

  • November 21, 2015
  • 1 reply
  • 2009 views

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>

    This topic has been closed for replies.
    Correct answer BKBK

    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

    1 reply

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    November 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

    R
    rickclark54Author
    Known Participant
    November 22, 2015

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    November 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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices