CURRENT_DATE() - 7oddities

[nemonoman]

MySQL 4 and 5.0.44

SELECT current_date( )-7 =>20080205
SELECT current_date( )-INTERVAL 7 DAY =>2008-02-05
SELECT current_date( )-7 = current_date( )-INTERVAL 7 DAY =>1 (true)

SELECT current_date( )-INTERVAL 7 DAY = '20080205' =>1
SELECT current_date( )-INTERVAL 7 DAY = '2008-02-05' =>1

SELECT current_date( )-7 = '20080205' =>1 (true)
SELECT current_date( )-7 = '2008-02-05' =>0 (false (!!!))

[rulian]

cause current-date() - 7 = 20080205
i think you mean
SELECT current_date( ) - INTERVAL 7
DAY = '2008-02-05' => 1

at that point you are doing string comparisons
and 20080205 != 2008-02-05

[nemonoman]

I had not seen the CURRENT_DATE()-7 approach before reworking someone else's code, always using the long version CURRENT_DATE()-INTERVAL 7 DAY myself.

Note that the values returned by these 2 approaches CAN BE INTERPRETED DIFFERENTLY. I learned this by some tearing out what is left of my hair.

Here's an example:

mysql_query("DROP TABLE IF EXISTS `testdate` ;");
mysql_query("CREATE TABLE `testdate` (
            `dateToTest` DATE NOT NULL
            ) ENGINE = MYISAM ;");
// TODAY is 12 Feb 2008 -- I'll enter the date 7 days previously
mysql_query("INSERT INTO testdate
            SET dateToTest = '2008-02-05';");

// Let's check the date --
$query="SELECT CURRENT_DATE()-INTERVAL 7 DAY=dateToTest FROM testdate";
echo "\n\n$query\n";
if(mysql_result(mysql_query($query),0)){
    echo " = >> EQUAL with CURRENT_DATE()-INTERVAL 7 DAY, So I will do some process";
}
else{
    echo "= >>Not equal with CURRENT_DATE()-INTERVAL 7 DAY, So I will not do some process";
}
$query="SELECT CURRENT_DATE()-7=dateToTest FROM testdate";
echo "\n\n$query\n";

if(mysql_result(mysql_query($query),0)){
    echo "= >>EQUAL with CURRENT_DATE()-7, So I will do some process";
}
else{
    echo "= >>Not  equal with CURRENT_DATE()-7, So I will not do some process";
}

//But -- get the date into a variable, to reuse it in a different process

$mydate=mysql_result(mysql_query("SELECT dateToTest FROM testdate"),0);
$query="SELECT CURRENT_DATE()-INTERVAL 7 DAY='$mydate'";
echo "\n\n$query\n";
if(mysql_result(mysql_query($query),0)){
    echo "= >>EQUAL with CURRENT_DATE()-INTERVAL 7 DAY, So I will do some process";
}
else{
    echo "= >>Not  equal with CURRENT_DATE()-INTERVAL 7 DAY, So I will not do some process";
}
$query="SELECT CURRENT_DATE()-7='$mydate'";
echo "\n\n$query\n";
if(mysql_result(mysql_query(),0)){
    echo "EQUAL with CURRENT_DATE()-7, So I will do some process";
}
else{
    echo "= >>Not equal with CURRENT_DATE()-7, So I will not do some process";
}

Results:
SELECT CURRENT_DATE()-INTERVAL 7 DAY=dateToTest FROM testdate
= >> EQUAL with CURRENT_DATE()-INTERVAL 7 DAY, So I will do some process

SELECT CURRENT_DATE()-7=dateToTest FROM testdate
= >>EQUAL with CURRENT_DATE()-7, So I will do some process

SELECT CURRENT_DATE()-INTERVAL 7 DAY='2008-02-05'
= >>EQUAL with CURRENT_DATE()-INTERVAL 7 DAY, So I will do some process

SELECT CURRENT_DATE()-7='2008-02-05'
= >>Not equal with CURRENT_DATE()-7, So I will not do some process

NOTE THE RESULT IN THAT LAST TEST. Perhaps someone will save what is left his/her hair.