How many weeks are inside of two dates.

Question

I have starting dates and ending dates in my database (MySQL). How can I get the answer, how many weeks(or days) are inside of those 2 dates? (mysql or php)

For example I have this kind of database:

Started and | will_end
2009-12-17 | 2009-12-24
2009-12-12 | 2009-12-26
...

Update to the question:
How to use DATEDIFF? How can I make this to work? or should I use DATEDIFF completly differently?

SELECT DATEDIFF('Started ','will_end') AS 'Duration' FROM my_table WHERE id = '110';

Answer 1

MySQL has datediff which returns the difference in days between two dates, since MySQL 4.1.1.

Do note that, as per the manual, DATEDIFF(expr1,expr2) returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Answer 2

You can use the TO_DAYS function on each date and subtract the two to calculate the difference in days.

Answer 3

DATEDIFF

Find the days and divide by 7

Answer 4

<?php
  $dayDif    = date('z',strtotime('2009-12-17)') - date('z',strtotime('2009-12-24)');
  $numWeeks  = $dayDif / 7;
?>

The z option for php's date function gives you the day of the year (0 - 365). By subtracting the two values you find how many days between dates. Then factor by seven for the number of weeks.

Read this page closely, the date() function is rich. http://php.net/manual/en/function.date.php

Answer 5

If the two columns $d1 and $d2 store unix timestamp obtained from time() then this simple line suffices:

$diffweek = abs($d1 - $d2) / 604800;

Otherwise if the columns are of DATETIME type, then:

$diffweek = abs(strtotime($d1) - strtotime($d2)) / 604800;

p/s: 604800 is the number of seconds in a week (60 * 60 * 24 * 7)

p/s2: you might want to intval($diffweek) or round($diffweek)