How to get the number of days of difference between two dates on mysql?

Question

I need to get the number of days contained within a couple of dates on mysql.

For example: Check in date 12-04-2010 Check out date 15-04-2010 and the day difference would be 3

I hope that was clear Thanks

Answer 1

What about the DATEDIFF function ?

Quoting the manual's page :

DATEDIFF() 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

In your case, you'd use :

mysql> select datediff('2010-04-15', '2010-04-12');
+--------------------------------------+
| datediff('2010-04-15', '2010-04-12') |
+--------------------------------------+
|                                    3 | 
+--------------------------------------+
1 row in set (0,00 sec)

But note the dates should be written as YYYY-MM-DD, and not DD-MM-YYYY like you posted.

Answer 2

Use the DATEDIFF() function.

Example from documentation:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1

Answer 3

Refer to Mysql date and time functions, you'll find there many interesting, I promise