date diff

[sanchez77]

I have two dates fields stored as VARCHAR in table.

 

How can I query that and return the difference in days?

 

I was using this below, but it's not returning a value. Is there a differernt SELECT statement i can use?

 

$difference = mysql_query("SELECT TIMEDIFF(`storedatein`, `storetimeout`) AS difference FROM lockers WHERE `lockerno` = '$_POST[lockerno]'");//query is execueted

if(mysql_num_rows($difference) > 0)
{
//got a row
$result = mysql_fetch_array($difference) or trigger_error("Error: ".mysql_error(), E_USER_ERROR);
;//assign the results to an array
echo "The locker number " . $_POST[lockerno] . " was rented for " . $result['difference'];
}
else
{
echo 'no results';
}

[p2grace]

Save the dates as datetimes instead of varchars.  Then mysql can calculate the difference.

[ldougherty]

By storing them in varchar format you can not compare them since your script just looks at them as strings rather than actual dates.

 

If you change the column type in mysql to date you will then be able to compare the values.

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html