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 is 12-04-2010    Check out date 15-04-2010   The day difference would be 3

User · Answer

Get days between Current date to destination Date   SELECT DATEDIFF  2019-04-12   CURDATE    AS days    output    days   335

User · Answer

SELECT md    DATEDIFF md end date  md start date  AS days FROM  membership dates md   output    id  entity id    start date            end date             days  1   1236      2018-01-16 00 00 00     2018-08-31 00 00 00    227 2   2876      2015-06-26 00 00 00     2019-06-30 00 00 00   1465 3   3880      1990-06-05 00 00 00     2018-07-04 00 00 00   10256 4   3882      1993-07-05 00 00 00     2018-07-04 00 00 00   9130   hope it helps someone in future

User · Answer

Use the DATEDIFF   function   Example from documentation   SELECT DATEDIFF  2007-12-31 23 59 59   2007-12-30        - gt  1

User · Answer

I prefer TIMESTAMPDIFF because you can easily change the unit if need be

User · Answer

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 gt  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

User · Answer

Note if you want to count FULL 24h days between 2 dates  datediff can return wrong values for you  As documentation states   Only the date parts of the values are used in the calculation   which results in select datediff  2016-04-14 11 59 00    2016-04-13 12 00 00   returns 1 instead of expected 0  Solution is using select timestampdiff DAY   2016-04-13 11 00 01    2016-04-14 11 00 00     note the opposite order of arguments compared to datediff   Some examples   select timestampdiff DAY   2016-04-13 11 00 01    2016-04-14 11 00 00    returns 0 select timestampdiff DAY   2016-04-13 11 00 00    2016-04-14 11 00 00    returns 1 select timestampdiff DAY   2016-04-13 11 00 00   now     returns how many full 24h days has passed since 2016-04-13 11 00 00 until now   Hope it will help someone  because at first it isn t much obvious why datediff returns values which seems to be unexpected or wrong

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

days

Examples related to mysql

Examples related to date