How to calculate difference between two dates in months in MySQL
Solution 1
This could work:
SELECT 12 * (YEAR(DateOfService)
- YEAR(BirthDate))
+ (MONTH(DateOfService)
- MONTH(BirthDate)) AS months
FROM table
Solution 2
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')
-- 0
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')
-- 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')
-- 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')
-- 7
It basically gets the number of months elapsed from the first date in the parameter list. This solution accounts for the varying amount of days in each month (28,30,31) as well as leap years.
If you want decimal precision in the number of months elapsed, it's a little more complicated, 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
Solution 3
Try this:
SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ...
Solution 4
TIMESTAMPDIFF(MONTH, Start_date, End_date)
Example:
SELECT TIMESTAMPDIFF(MONTH, BirthDate, DateOfService) AS Months FROM Table
Solution 5
TRY with
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
Related videos on Youtube
Comments
-
Awan about 4 years
I have two columns in a MySQL table:
- DateOfService (datetime)
- BirthDate (date)
I want to run a MySQL query that will provide date difference between these two fields in months.
How can I do this in a MySQL select query?
Thanks.
-
amaster over 10 yearsthis does not answer the OP question as it does not accept datetime format or even date format. Maybe if this question was edited showing this then I might retract my -1.
-
Vaishu about 10 yearsi have used this query..issue arises particular date. the dates are 2014-01-30 to 2014-03-02 differs 30days only.but it is returns 2 months difference.
-
Dipen about 9 yearsHey i am able to achieve above result but my condition is that i need 0.5 year , 1 year, 1.5 year, 2 year ,2.5 year and so on.. how could i do that!!