The difference in months between dates in MySQL

Question

I m looking to calculate the number of months between 2 date time fields   Is there a better way than getting the unix timestamp and the dividing by 2 592 000  seconds  and rounding up whithin MySQL

User · Answer

You can also try this   select MONTH NOW   -MONTH table date  as  Total Month Difference  from table name    OR  select MONTH Newer date -MONTH Older date  as  Total Month Difference  from table Name

User · Answer

From the MySQL manual      PERIOD DIFF P1 P2       Returns the number of months between periods P1 and P2  P1 and P2 should be in the format YYMM or YYYYMM  Note that the period arguments P1 and P2 are not date values       mysql  SELECT PERIOD DIFF 200802 200703             -  11   So it may be possible to do something like this   Select period diff concat year d1  if month d1  lt 10  0      month d1    concat year d2  if month d2  lt 10  0      month d2    as months from your table    Where d1 and d2 are the date expressions   I had to use the if   statements to make sure that the months was a two digit number like 02 rather than 2

User · Answer

Try this  SELECT YEAR end date  12   MONTH end date  -  YEAR start date  12   MONTH start date

User · Answer

The DATEDIFF function can give you the number of days between two dates   Which is more accurate  since    how do you define a month    28  29  30  or 31 days

User · Answer

Simple answer given start date as ins frm and end date as ins to  SELECT convert TIMESTAMPDIFF year  ins frm  ins to  UNSIGNED  as yrs         mod TIMESTAMPDIFF MONTH  ins frm  ins to  12  mnths FROM table name   Enjoy

User · Answer

Is there a better way  yes  Do not use MySQL Timestamps  Apart from the fact that they occupy 36 Bytes  they are not at all convenient to work with  I would reccomend using Julian Date and Seconds from midnight for all date time values  These can be combined to form a UnixDateTime  If this is stored in a DWORD  unsigned 4 Byte Integer  then dates all the way up to 2106 can be stored as seconds since epoc  01 01 1970 DWORD max val   4 294 967 295  -  A DWORD can hold 136 years of Seconds   Julian Dates are very nice to work with when making date calculations UNIXDateTime values are good to work with when making Date Time calculations Neither are good to look at  so I use the Timestamps when I need a column that I will not be doing much calculation with  but I want an at-a-glance indication   Converting to Julian and back can be done very quickly in a good language  Using pointers I have it down to about 900 Clks  This is also a conversion from a STRING to an INTEGER of course   When you get into serious applications that use Date Time information like for example the financial markets  Julian dates are de-facto

User · Answer

SELECT    FROM emp salaryrevise view  WHERE curr year Between  2008  AND  2009       AND MNTH Between  12  AND  1

User · Answer

I prefer this way  because evryone will understand it clearly at the first glance   SELECT     12    YEAR to  - YEAR from      MONTH to  - MONTH from   AS months FROM     tab

User · Answer

SELECT    FROM emp salaryrevise view  WHERE curr year Between  2008  AND  2009       AND MNTH Between  12  AND  1

User · Answer

From the MySQL manual      PERIOD DIFF P1 P2       Returns the number of months between periods P1 and P2  P1 and P2 should be in the format YYMM or YYYYMM  Note that the period arguments P1 and P2 are not date values       mysql  SELECT PERIOD DIFF 200802 200703             -  11   So it may be possible to do something like this   Select period diff concat year d1  if month d1  lt 10  0      month d1    concat year d2  if month d2  lt 10  0      month d2    as months from your table    Where d1 and d2 are the date expressions   I had to use the if   statements to make sure that the months was a two digit number like 02 rather than 2

User · Answer

PERIOD DIFF   function  One of the way is MySQL PERIOD DIFF   returns the difference between two periods  Periods should be in the same format i e  YYYYMM or YYMM  It is to be noted that periods are not date values   Code   SELECT PERIOD DIFF 200905 200811

User · Answer

The DATEDIFF function can give you the number of days between two dates   Which is more accurate  since    how do you define a month    28  29  30  or 31 days

User · Answer

Month-difference between any given two dates   I m surprised this hasn t been mentioned yet   Have a look at the TIMESTAMPDIFF   function in MySQL   What this allows you to do is pass in two TIMESTAMP or DATETIME values  or even DATE as MySQL will auto-convert  as well as the unit of time you want to base your difference on   You can specify MONTH as the unit in the first parameter   SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-04   -- Outputs  0     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-05   -- Outputs  1     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-15   -- Outputs  1     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-12-16   -- Outputs  7   It basically gets the number of months elapsed from the first date in the parameter list  This solution automatically compensates for the varying amount of days in each month  28 30 31  as well as taking into account leap years  mdash  you don t have to worry about any of that stuff     Month-difference with precision   It s a little more complicated if you want to introduce decimal precision in the number of months elapsed  but here is how you can do it   SELECT    TIMESTAMPDIFF MONTH  startdate  enddate      DATEDIFF      enddate      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate      MONTH         DATEDIFF      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate    1     MONTH      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate      MONTH       Where startdate and enddate are your date parameters  whether it be from two date columns in a table or as input parameters from a script   Examples   With startdate    2012-05-05  AND enddate    2012-05-27   -- Outputs  0 7097     With startdate    2012-05-05  AND enddate    2012-06-13   -- Outputs  1 2667     With startdate    2012-02-27  AND enddate    2012-06-02   -- Outputs  3 1935

User · Answer

Execute this code and it will create a function datedeifference which will give you the difference in date format yyyy-mm-dd   DELIMITER     CREATE FUNCTION datedifference date1 DATE  date2 DATE  RETURNS DATE NO SQL  BEGIN     DECLARE dif DATE      IF DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2      lt  0    THEN                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH DATE SUB date1  INTERVAL 1 MONTH     -   DAY date2                                                 Y- m- d        ELSEIF DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2      lt  DAY LAST DAY DATE SUB date1  INTERVAL 1 MONTH    THEN                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2                                                 Y- m- d        ELSE                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2                                                 Y- m- d        END IF   RETURN dif  END    DELIMITER

User · Answer

From the MySQL manual      PERIOD DIFF P1 P2       Returns the number of months between periods P1 and P2  P1 and P2 should be in the format YYMM or YYYYMM  Note that the period arguments P1 and P2 are not date values       mysql  SELECT PERIOD DIFF 200802 200703             -  11   So it may be possible to do something like this   Select period diff concat year d1  if month d1  lt 10  0      month d1    concat year d2  if month d2  lt 10  0      month d2    as months from your table    Where d1 and d2 are the date expressions   I had to use the if   statements to make sure that the months was a two digit number like 02 rather than 2

User · Answer

This query worked for me    SELECT   FROM tbl purchase receipt WHERE purchase date BETWEEN  2008-09-09  AND  2009-09-09    It simply take two dates and retrieves the values between them

User · Answer

The DATEDIFF function can give you the number of days between two dates   Which is more accurate  since    how do you define a month    28  29  30  or 31 days

User · Answer

The DATEDIFF function can give you the number of days between two dates   Which is more accurate  since    how do you define a month    28  29  30  or 31 days

User · Answer

PERIOD DIFF calculates months between two dates   For example  to calculate the difference between now   and a time column in your table   select period diff date format now      Y m    date format time    Y m    as months from your table

User · Answer

Is there a better way  yes  Do not use MySQL Timestamps  Apart from the fact that they occupy 36 Bytes  they are not at all convenient to work with  I would reccomend using Julian Date and Seconds from midnight for all date time values  These can be combined to form a UnixDateTime  If this is stored in a DWORD  unsigned 4 Byte Integer  then dates all the way up to 2106 can be stored as seconds since epoc  01 01 1970 DWORD max val   4 294 967 295  -  A DWORD can hold 136 years of Seconds   Julian Dates are very nice to work with when making date calculations UNIXDateTime values are good to work with when making Date Time calculations Neither are good to look at  so I use the Timestamps when I need a column that I will not be doing much calculation with  but I want an at-a-glance indication   Converting to Julian and back can be done very quickly in a good language  Using pointers I have it down to about 900 Clks  This is also a conversion from a STRING to an INTEGER of course   When you get into serious applications that use Date Time information like for example the financial markets  Julian dates are de-facto

User · Answer

Execute this code and it will create a function datedeifference which will give you the difference in date format yyyy-mm-dd   DELIMITER     CREATE FUNCTION datedifference date1 DATE  date2 DATE  RETURNS DATE NO SQL  BEGIN     DECLARE dif DATE      IF DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2      lt  0    THEN                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH DATE SUB date1  INTERVAL 1 MONTH     -   DAY date2                                                 Y- m- d        ELSEIF DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2      lt  DAY LAST DAY DATE SUB date1  INTERVAL 1 MONTH    THEN                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2                                                 Y- m- d        ELSE                 SET dif DATE FORMAT                                          CONCAT                                              PERIOD DIFF date format date1    y m   date format date2    y m   DIV 12                                                 -                                               PERIOD DIFF date format date1    y m   date format date2    y m     12                                                 -                                               DATEDIFF date1  DATE CONCAT YEAR date1   -   MONTH date1    -   DAY date2                                                 Y- m- d        END IF   RETURN dif  END    DELIMITER

User · Answer

As many of the answers here show  the  right  answer depends on exactly what you need  In my case  I need to round to the closest whole number   Consider these examples  1st January -  31st January  It s 0 whole months  and almost 1 month long  1st January -  1st February  It s 1 whole month  and exactly 1 month long   To get the number of whole  complete  months  use   SELECT TIMESTAMPDIFF MONTH   2018-01-01    2018-01-31       gt  0 SELECT TIMESTAMPDIFF MONTH   2018-01-01    2018-02-01       gt  1   To get a rounded duration in months  you could use   SELECT ROUND TIMESTAMPDIFF DAY   2018-01-01    2018-01-31   12 365 24     gt  1 SELECT ROUND TIMESTAMPDIFF DAY   2018-01-01    2018-01-31   12 365 24     gt  1   This is accurate to   - 5 days and for ranges over 1000 years  Zane s answer is obviously more accurate  but it s too verbose for my liking

User · Answer

You can also try this   select MONTH NOW   -MONTH table date  as  Total Month Difference  from table name    OR  select MONTH Newer date -MONTH Older date  as  Total Month Difference  from table Name

User · Answer

This query worked for me    SELECT   FROM tbl purchase receipt WHERE purchase date BETWEEN  2008-09-09  AND  2009-09-09    It simply take two dates and retrieves the values between them

User · Answer

SELECT    FROM emp salaryrevise view  WHERE curr year Between  2008  AND  2009       AND MNTH Between  12  AND  1

User · Answer

I use also PERIODDIFF  To get the year and the month of the date  I use the function  EXTRACT     SELECT PERIOD DIFF EXTRACT YEAR MONTH FROM NOW     EXTRACT YEAR MONTH FROM time   AS months FROM your table

User · Answer

Try this  SELECT YEAR end date  12   MONTH end date  -  YEAR start date  12   MONTH start date

User · Answer

This depends on how you want the   of months to be defined   Answer this questions   What is difference in months  Feb 15  2008 - Mar 12  2009    Is it defined by clear cut   of days which depends on leap years- what month it is  or same day of previous month   1 month   A calculation for Days   Feb 15 -  29  leap year    14 Mar 1  2008   365   Mar 1  2009  Mar 1 -  Mar 12   12 days  14   365   12   391 days  Total   391 days    avg days in month   30    13 03333  A calculation of months    Feb 15 2008 - Feb 15 2009   12 Feb 15 -  Mar 12   less than 1 month Total   12 months  or 13 if feb 15 - mar 12 is considered  the past month

User · Answer

SELECT    FROM emp salaryrevise view  WHERE curr year Between  2008  AND  2009       AND MNTH Between  12  AND  1

User · Answer

I needed month-difference with precision  Although Zane Bien s solution is in the right direction  his second and third examples give inaccurate results  A day in February divided by the number of days in February is not equal to a day in May divided by the number of days in May  So the second example should output   31-5 1  31   13 30     1 3043 and the third example   29-27 1  29   2 30   3     3 1701    I ended up with the following query   SELECT      2012-02-27  AS startdate       2012-06-02  AS enddate      TIMESTAMPDIFF DAY   SELECT startdate    SELECT enddate   AS days      IF MONTH  SELECT startdate     MONTH  SELECT enddate    0   TIMESTAMPDIFF DAY   SELECT startdate   LAST DAY  SELECT startdate     INTERVAL 1 DAY     DAY LAST DAY  SELECT startdate     AS period1           TIMESTAMPDIFF MONTH  LAST DAY  SELECT startdate     INTERVAL 1 DAY  LAST DAY  SELECT enddate    AS period2      IF MONTH  SELECT startdate     MONTH  SELECT enddate     SELECT days   DAY  SELECT enddate      DAY LAST DAY  SELECT enddate    AS period3       SELECT period1     SELECT period2     SELECT period3  AS months

User · Answer

As many of the answers here show  the  right  answer depends on exactly what you need  In my case  I need to round to the closest whole number   Consider these examples  1st January -  31st January  It s 0 whole months  and almost 1 month long  1st January -  1st February  It s 1 whole month  and exactly 1 month long   To get the number of whole  complete  months  use   SELECT TIMESTAMPDIFF MONTH   2018-01-01    2018-01-31       gt  0 SELECT TIMESTAMPDIFF MONTH   2018-01-01    2018-02-01       gt  1   To get a rounded duration in months  you could use   SELECT ROUND TIMESTAMPDIFF DAY   2018-01-01    2018-01-31   12 365 24     gt  1 SELECT ROUND TIMESTAMPDIFF DAY   2018-01-01    2018-01-31   12 365 24     gt  1   This is accurate to   - 5 days and for ranges over 1000 years  Zane s answer is obviously more accurate  but it s too verbose for my liking

User · Answer

This depends on how you want the   of months to be defined   Answer this questions   What is difference in months  Feb 15  2008 - Mar 12  2009    Is it defined by clear cut   of days which depends on leap years- what month it is  or same day of previous month   1 month   A calculation for Days   Feb 15 -  29  leap year    14 Mar 1  2008   365   Mar 1  2009  Mar 1 -  Mar 12   12 days  14   365   12   391 days  Total   391 days    avg days in month   30    13 03333  A calculation of months    Feb 15 2008 - Feb 15 2009   12 Feb 15 -  Mar 12   less than 1 month Total   12 months  or 13 if feb 15 - mar 12 is considered  the past month

User · Answer

You can get years  months and days this way   SELECT  username  date of birth  DATE FORMAT CURDATE      Y   - DATE FORMAT date of birth    Y   -  DATE FORMAT CURDATE     00- m- d    lt  DATE FORMAT date of birth   00- m- d    AS years  PERIOD DIFF  DATE FORMAT CURDATE      Y m     DATE FORMAT date of birth    Y m     AS months  DATEDIFF CURDATE   date of birth  AS days FROM users

User · Answer

Month-difference between any given two dates   I m surprised this hasn t been mentioned yet   Have a look at the TIMESTAMPDIFF   function in MySQL   What this allows you to do is pass in two TIMESTAMP or DATETIME values  or even DATE as MySQL will auto-convert  as well as the unit of time you want to base your difference on   You can specify MONTH as the unit in the first parameter   SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-04   -- Outputs  0     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-05   -- Outputs  1     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-06-15   -- Outputs  1     SELECT TIMESTAMPDIFF MONTH   2012-05-05    2012-12-16   -- Outputs  7   It basically gets the number of months elapsed from the first date in the parameter list  This solution automatically compensates for the varying amount of days in each month  28 30 31  as well as taking into account leap years  mdash  you don t have to worry about any of that stuff     Month-difference with precision   It s a little more complicated if you want to introduce decimal precision in the number of months elapsed  but here is how you can do it   SELECT    TIMESTAMPDIFF MONTH  startdate  enddate      DATEDIFF      enddate      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate      MONTH         DATEDIFF      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate    1     MONTH      startdate   INTERVAL       TIMESTAMPDIFF MONTH  startdate  enddate      MONTH       Where startdate and enddate are your date parameters  whether it be from two date columns in a table or as input parameters from a script   Examples   With startdate    2012-05-05  AND enddate    2012-05-27   -- Outputs  0 7097     With startdate    2012-05-05  AND enddate    2012-06-13   -- Outputs  1 2667     With startdate    2012-02-27  AND enddate    2012-06-02   -- Outputs  3 1935

User · Answer

I prefer this way  because evryone will understand it clearly at the first glance   SELECT     12    YEAR to  - YEAR from      MONTH to  - MONTH from   AS months FROM     tab

User · Answer

Is there a better way  yes  Do not use MySQL Timestamps  Apart from the fact that they occupy 36 Bytes  they are not at all convenient to work with  I would reccomend using Julian Date and Seconds from midnight for all date time values  These can be combined to form a UnixDateTime  If this is stored in a DWORD  unsigned 4 Byte Integer  then dates all the way up to 2106 can be stored as seconds since epoc  01 01 1970 DWORD max val   4 294 967 295  -  A DWORD can hold 136 years of Seconds   Julian Dates are very nice to work with when making date calculations UNIXDateTime values are good to work with when making Date Time calculations Neither are good to look at  so I use the Timestamps when I need a column that I will not be doing much calculation with  but I want an at-a-glance indication   Converting to Julian and back can be done very quickly in a good language  Using pointers I have it down to about 900 Clks  This is also a conversion from a STRING to an INTEGER of course   When you get into serious applications that use Date Time information like for example the financial markets  Julian dates are de-facto

User · Answer

DROP FUNCTION IF EXISTS  calcula edad     CREATE DEFINER  root   localhost  FUNCTION  calcula edad  pFecha1 date  pFecha2 date  pTipo char 1   RETURNS int 11  Begin    Declare vMeses int    Declare vEdad int     Set vMeses   period diff  date format  pFecha1    Y m     date format  pFecha2    Y m              Si el dia de la fecha1 es menor al dia de fecha2  restar 1 mes      if day pFecha1   lt  day pFecha2  then     Set vMeses   VMeses - 1    end if     if pTipo  A  then     Set vEdad   vMeses div 12     else     Set vEdad   vMeses     end if     Return vEdad  End  select calcula edad curdate   born date  M   --  for number of months between 2 dates

User · Answer

I needed month-difference with precision  Although Zane Bien s solution is in the right direction  his second and third examples give inaccurate results  A day in February divided by the number of days in February is not equal to a day in May divided by the number of days in May  So the second example should output   31-5 1  31   13 30     1 3043 and the third example   29-27 1  29   2 30   3     3 1701    I ended up with the following query   SELECT      2012-02-27  AS startdate       2012-06-02  AS enddate      TIMESTAMPDIFF DAY   SELECT startdate    SELECT enddate   AS days      IF MONTH  SELECT startdate     MONTH  SELECT enddate    0   TIMESTAMPDIFF DAY   SELECT startdate   LAST DAY  SELECT startdate     INTERVAL 1 DAY     DAY LAST DAY  SELECT startdate     AS period1           TIMESTAMPDIFF MONTH  LAST DAY  SELECT startdate     INTERVAL 1 DAY  LAST DAY  SELECT enddate    AS period2      IF MONTH  SELECT startdate     MONTH  SELECT enddate     SELECT days   DAY  SELECT enddate      DAY LAST DAY  SELECT enddate    AS period3       SELECT period1     SELECT period2     SELECT period3  AS months

User · Answer

This depends on how you want the   of months to be defined   Answer this questions   What is difference in months  Feb 15  2008 - Mar 12  2009    Is it defined by clear cut   of days which depends on leap years- what month it is  or same day of previous month   1 month   A calculation for Days   Feb 15 -  29  leap year    14 Mar 1  2008   365   Mar 1  2009  Mar 1 -  Mar 12   12 days  14   365   12   391 days  Total   391 days    avg days in month   30    13 03333  A calculation of months    Feb 15 2008 - Feb 15 2009   12 Feb 15 -  Mar 12   less than 1 month Total   12 months  or 13 if feb 15 - mar 12 is considered  the past month

User · Answer

You can get years  months and days this way   SELECT  username  date of birth  DATE FORMAT CURDATE      Y   - DATE FORMAT date of birth    Y   -  DATE FORMAT CURDATE     00- m- d    lt  DATE FORMAT date of birth   00- m- d    AS years  PERIOD DIFF  DATE FORMAT CURDATE      Y m     DATE FORMAT date of birth    Y m     AS months  DATEDIFF CURDATE   date of birth  AS days FROM users

User · Answer

PERIOD DIFF calculates months between two dates   For example  to calculate the difference between now   and a time column in your table   select period diff date format now      Y m    date format time    Y m    as months from your table

User · Answer

This depends on how you want the   of months to be defined   Answer this questions   What is difference in months  Feb 15  2008 - Mar 12  2009    Is it defined by clear cut   of days which depends on leap years- what month it is  or same day of previous month   1 month   A calculation for Days   Feb 15 -  29  leap year    14 Mar 1  2008   365   Mar 1  2009  Mar 1 -  Mar 12   12 days  14   365   12   391 days  Total   391 days    avg days in month   30    13 03333  A calculation of months    Feb 15 2008 - Feb 15 2009   12 Feb 15 -  Mar 12   less than 1 month Total   12 months  or 13 if feb 15 - mar 12 is considered  the past month

User · Answer

PERIOD DIFF   function  One of the way is MySQL PERIOD DIFF   returns the difference between two periods  Periods should be in the same format i e  YYYYMM or YYMM  It is to be noted that periods are not date values   Code   SELECT PERIOD DIFF 200905 200811

User · Answer

The Query will be like   select period diff date format now     Y m   date format created   Y m    from customers where     Gives a number of calendar months since the created datestamp on a customer record  letting MySQL do the month selection internally

User · Answer

Is there a better way  yes  Do not use MySQL Timestamps  Apart from the fact that they occupy 36 Bytes  they are not at all convenient to work with  I would reccomend using Julian Date and Seconds from midnight for all date time values  These can be combined to form a UnixDateTime  If this is stored in a DWORD  unsigned 4 Byte Integer  then dates all the way up to 2106 can be stored as seconds since epoc  01 01 1970 DWORD max val   4 294 967 295  -  A DWORD can hold 136 years of Seconds   Julian Dates are very nice to work with when making date calculations UNIXDateTime values are good to work with when making Date Time calculations Neither are good to look at  so I use the Timestamps when I need a column that I will not be doing much calculation with  but I want an at-a-glance indication   Converting to Julian and back can be done very quickly in a good language  Using pointers I have it down to about 900 Clks  This is also a conversion from a STRING to an INTEGER of course   When you get into serious applications that use Date Time information like for example the financial markets  Julian dates are de-facto

User · Answer

Simple answer given start date as ins frm and end date as ins to  SELECT convert TIMESTAMPDIFF year  ins frm  ins to  UNSIGNED  as yrs         mod TIMESTAMPDIFF MONTH  ins frm  ins to  12  mnths FROM table name   Enjoy

User · Answer

From the MySQL manual      PERIOD DIFF P1 P2       Returns the number of months between periods P1 and P2  P1 and P2 should be in the format YYMM or YYYYMM  Note that the period arguments P1 and P2 are not date values       mysql  SELECT PERIOD DIFF 200802 200703             -  11   So it may be possible to do something like this   Select period diff concat year d1  if month d1  lt 10  0      month d1    concat year d2  if month d2  lt 10  0      month d2    as months from your table    Where d1 and d2 are the date expressions   I had to use the if   statements to make sure that the months was a two digit number like 02 rather than 2

User · Answer

I use also PERIODDIFF  To get the year and the month of the date  I use the function  EXTRACT     SELECT PERIOD DIFF EXTRACT YEAR MONTH FROM NOW     EXTRACT YEAR MONTH FROM time   AS months FROM your table

User · Answer

The Query will be like   select period diff date format now     Y m   date format created   Y m    from customers where     Gives a number of calendar months since the created datestamp on a customer record  letting MySQL do the month selection internally

User · Answer

DROP FUNCTION IF EXISTS  calcula edad     CREATE DEFINER  root   localhost  FUNCTION  calcula edad  pFecha1 date  pFecha2 date  pTipo char 1   RETURNS int 11  Begin    Declare vMeses int    Declare vEdad int     Set vMeses   period diff  date format  pFecha1    Y m     date format  pFecha2    Y m              Si el dia de la fecha1 es menor al dia de fecha2  restar 1 mes      if day pFecha1   lt  day pFecha2  then     Set vMeses   VMeses - 1    end if     if pTipo  A  then     Set vEdad   vMeses div 12     else     Set vEdad   vMeses     end if     Return vEdad  End  select calcula edad curdate   born date  M   --  for number of months between 2 dates

[mysql] The difference in months between dates in MySQL

Examples related to mysql

Examples related to date

Examples related to datediff

Examples related to date-arithmetic