Hello,
This is my first time using DateDiff and it looks pretty straightforward; I fear, however, that I keep getting the following error:
Incorrect parameter count in the call to native function ‘DATEDIFF’ :injured:
My goal in the code below is to get all records that are more than 3 days old; the Timestamp variable looks like: 2011-05-22 20:26:42
SELECT * FROM `homepage_homeschool_schedule` WHERE DATEDIFF(DD,Timestamp,CURRENT_TIMESTAMP)>3
Any help would be appreciated.
-Eric
Are you using MySQL? (I ask because you wrapped your code in mySQL formatting forum-code) If so, you do have the wrong parameter count.
MySQL :: MySQL 5.1 Reference Manual :: 11.7 Date and Time Functions
best advice ever: in order to allow an index on the column to be used, and therefore to make your query perform well, it is important never to apply a function to a column
so your DATEDIFF approach is dead right out of the starting gate
a wise man once said that it is far better to get the correct answer after a while than a wrong answer immediately…
and to be sure, DATEDIFF can give you the right answer, but it’s not efficient
if you’re trying to find “more than 3 days old” you should do it this way –
WHERE `timestamp` < CURRENT_DATE - INTERVAL 3 DAY
note that you shouldn’t use a reserverd word like TIMESTAMP for the name of a column
SELECT *
FROM homepage_homeschool_schedule
WHERE dbTime > (SELECT CURRENT_TIME - INTERVAL 3 DAY)
no, the other way around, and without the parentheses, and using CURRENT_DATE instead, and without the extra SELECT keyword
You are correct… and thanks for the link! I read through the page, and have tried the following:
SELECT *
FROM homepage_homeschool_schedule
WHERE dbTime < (SELECT CURRENT_TIME - INTERVAL 3 DAY)
I changed TIMESTAMP to read dbTime because I thought that maybe using TIMESTAMP as a variable name wouldn’t be a good idea. Regardless, now I have no error, but whether by inequality is less than or greater than, I get no rows back.
My goal is to get all records with dbTimes that are earlier than 3 days in the past. Any additional thoughts or hints would be appreciated.
-Eric
did you try it the way i suggested?
WHERE dbtime < CURRENT_DATE - INTERVAL 3 DAY
r937:
Thank you for the solution and your patient response: due to a lag in receiving your messages, it only seemed as though I wasn’t taking your advice right off the bat.
Have a great day…
-Eric