Help with using DateDIFF function in MySQL

Hey all,

Thanks in advance for any help that you can render!  I'm currently extremely frustrated by a problem trying to use DateDiff in one of my SQL queries.

The code is shown below and i've got a commented out line that runs fine but when I try to add in a DateDiff check the function still runs but it doesn't actually return results based on that datediff check as far as I can tell.

NEXTREVIEWREMIND is a Date field that stores the next time I want to trigger the operations I do after this sql query.

For example, NEXTREVIEWREMIND might be 2009-05-13 (next week) and based on $curdate I know that 2009-05-13 - 2009-05-06 = 7 which is > 0 so it should not be a match.  In my database i've seeded some values that should match including putting a NEXTREVIEWREMIND date on an entry of 2009-05-06 which if we do the math should mean that DateDIFF == 0 which will be true.  The other conditions are also met (NUMTIMESREMINDED is 0 which is less than the max of 3 and the status is active).

Any ideas on what i'm doing wrong or even a good way to test out datediff in some sort of debug mode?

Thanks,
Nathan

$curdate = date("Y-m-d");
$sql2 = "SELECT * FROM $tableUsers WHERE STATUS = 'ACTIVE' AND CURRENTLYREVIEWING = 1 AND DATEDIFF(NEXTREVIEWREMIND, $curdate) <= 0 AND NUMTIMESREMINDED < $maxremind";
//$sql2 = "SELECT * FROM $tableUsers WHERE STATUS = 'ACTIVE' AND CURRENTLYREVIEWING = 1 AND NUMTIMESREMINDED < $maxremind";
$sql_result2 = mysql_query ($sql2, $connection ) or die ('request "Could not execute SQL query" '.$sql);

Open in new window

LVL 2
ironwill96Asked:
Who is Participating?
 
shobinsunCommented:
Hi,

this code I am using:

$datediff = date('Y-m-d');

$sql2 = "SELECT * FROM tablename WHERE DATEDIFF('$datediff','2008-11-30')>=0";

It works fine.

So you should ensure that the other conditions are right.

0
 
bevhostCommented:
SELECT DATEDIFF(now(),NEXTREVEIWREMIND) from $tableUsers LIMIT 0,20
Should give you a list of what DATEDIFF is returning.
0
 
shobinsunCommented:
Hi,

You can check the return value by :

$datediff = date('Y-m-d');

$sql2 = "SELECT DATEDIFF('$datediff',NEXTREVEIWREMIND) FROM $tableUsers";
$run2 = mysql_query($sql2);
$row2 = mysql_fetch_row($run2);
echo $row2[0];
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
ironwill96Author Commented:
I ran a query from phpmyadmin doing a SELECT DATEDIFF(NEXTREVIEWREMIND, "2009-05-06") AS DateDiff and it showed me the values for all of my differences and they are correct.  I see values of 7 where expected and 0 where expected.

The question still remains, why doesn't it work when DATEDIFF function is used as part of my WHERE clause?

Thanks,
Nathan
0
 
shobinsunCommented:
Hi,

I think the problem is behind the other conditions clauses. Please check that with removing the clauses on by one.

Its working for me.

Regards
0
 
ironwill96Author Commented:
Ok found the problem while looking into the other clauses like you suggested.

The issue was in this section of the code: DATEDIFF(NEXTREVIEWREMIND, $curdate)

It needed to be: DATEDIFF(NEXTREVIEWREMIND, \"$curdate\")

Apparently you have to have the " " marks around dates inside of DATEDIFF which I didn't realize.
0
 
ironwill96Author Commented:
Thanks for pointing me in the right direction!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.