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

DATEDIFF / MySQL Question

Participant ,
Mar 17, 2021 Mar 17, 2021

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. 

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

I figured it out. Thank you all

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

This is exactly what I did BKBK. 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
Resources