Problem With Date Calculations

[murfitUK]

I have a mysql database with records containing dates in the format "yyyy-mm-dd". Each record has a start date and an end date. I need to be able to calculate the number of days between these dates (inclusive). EG 2009-02-01 to 2009-02-01 is 1 day, 2009-02-01 to 2009-02-02 is 2 days, 2008-11-05 to 2008-11-28 is 24 days etc.Everything works fine except towards the end of March!2009-03-01 to 2009-03-28 gives 28 days (good)2009-03-01 to 2009-03-29 gives 29 days (good)2009-03-01 to 2009-03-30 gives 29 days (not good)2009-03-01 to 2009-03-31 gives 30 days (not good)This is the formula I've got (can't remember where I got it from):$days=floor(((strtotime($endDay)-strtotime($startDay))/3600/24)+1);I'm sure it is a php issue rather than the mysql database. The fields are set up using mysql's type "date" with default "0000-00-00" so data is stored simply as "2008-10-27" etc.Has it got something to do with the changeover from GMT to BST? If so, how do I fix it? Is there a better formula?Thanks.

[boen_robot]

Make SQL fix it.If you can select the two dates (let's call them date1 and date2 for the sake of the example), you can make MySQL substract them, and convert the result to days, like:

SELECT TO_DAYS(date2 - date1) FROM ...;

[gala]

please help, I am totally new here... I'm using your line and getting Null result. What is wrong?SELECT TO_DAYS('2009-04-01' - '2009-04-05');

[Ingolme]

please help, I am totally new here... I'm using your line and getting Null result. What is wrong?SELECT TO_DAYS('2009-04-01' - '2009-04-05');

To put it in simple terms, you're trying to subtract 5 from 1 (1 - 5)You can't do that with dates. When performing the subtraction, the highest date must be on the left and the lowest dat on the right.

[gala]

I've changed the dates' positions, but the result is the same - NULL. Are you sure the expression is correct?SELECT TO_DAYS('2009-04-05' - '2009-04-01');

[justsomeguy]

I haven't looked up the TO_DAYS function in the manual, but should it be like this:SELECT TO_DAYS('2009-04-05') - TO_DAYS('2009-04-01');

[gala]

That is right! Thank you so much!Now the next question - How to get this number out ;(I am using this code, but it prints "Array" instead of number. Would you, please, help...$con = mysql_connect("my_db","my_pass", "my_name");if (!$con) die('Could not connect to My-db: ' . mysql_error());mysql_select_db("my_db", $con);$result = mysql_query("SELECT TO_DAYS('2009-04-03') - TO_DAYS('2009-04-01')");while($row = mysql_fetch_array($result)) { echo $row; echo "<br />"; }

[justsomeguy]

$row is an array, $row[0] would hold the first field. You can also give it a name and refer to it by name.

$result = mysql_query("SELECT TO_DAYS('2009-04-03') - TO_DAYS('2009-04-01') AS num");while($row = mysql_fetch_assoc($result)){  echo $row['num'];  echo "<br />";}