Finding the difference of 2 dates: How tough could it be?

[kreut]

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'

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

[StarLion]

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

[r937]

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

[kreut]

SELECT *
FROM homepage_homeschool_schedule
WHERE dbTime > (SELECT CURRENT_TIME - INTERVAL 3 DAY)

[r937]

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

[kreut]

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

[r937]

Any additional thoughts or hints would be appreciated.

did you try it the way i suggested?

WHERE dbtime < CURRENT_DATE - INTERVAL 3 DAY

[kreut]

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