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

DATEDIFF / MySQL Question

Participant ,
Mar 17, 2021 Mar 17, 2021

Copy link to clipboard

Copied

I have a query where I am trying to get all records from a data table (TIMELOG) that have happened (EVENTTIME) in the past 7 days or less.

 

The table name is TIMELOG and the field name is EVENTTIME.

I want all records where EVENTTIME is in the past 7 days or less.

SELECT * FROM TIMELOG
WHERE
#DateDiff("d",EVENTTIME,now())# < 7

I don't know why this doesn't work.
I get this error message
Variable EVENTTIME is undefined. 

Views

253

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

Community Expert , Mar 17, 2021 Mar 17, 2021

You were getting the error because EVENTTIME, being a database column, is unknown to ColdFusion. A simple solution is to use MSQL date functions:

 

SELECT *
FROM TIMELOG
WHERE DATEDIFF(CURDATE(),EVENTTIME) < = 7

Votes

Translate

Translate
Participant ,
Mar 17, 2021 Mar 17, 2021

Copy link to clipboard

Copied

I figured it out. Thank you all

Votes

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
Community Expert ,
Mar 17, 2021 Mar 17, 2021

Copy link to clipboard

Copied

You were getting the error because EVENTTIME, being a database column, is unknown to ColdFusion. A simple solution is to use MSQL date functions:

 

SELECT *
FROM TIMELOG
WHERE DATEDIFF(CURDATE(),EVENTTIME) < = 7

Votes

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
Participant ,
Mar 17, 2021 Mar 17, 2021

Copy link to clipboard

Copied

LATEST

This is exactly what I did BKBK. Thank you

Votes

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