Getting the true number of months between two dates (in MySQL)

ve

Hi, I'm trying to get a query to work whereby I pass two dates and I get the precise number of months between the two dates in MySQL

SELECT period_diff( date_format( '2009-05-14' , '%Y%m' ) , date_format( '2009-06-01', '%Y%m' ) )

E.g. if I use the PERIOD_DIFF() function along with the DATE_FORMAT() function on two MySQL Date columns, I can in theory get a signed integer, the number of months between them (as above). Right now if I perform a PERIOD_DIFF call and pass E.g. these dates 2009-05-14 AND 2009-05-31, the number of months in difference is 0 (as you would expect), but if you get the difference between 2009-05-14 AND 2009-06-01 the number is now 1. The problem with the PERIOD_DIFF function is that it doesn't take the days in to consideration, only the year and months. So this is why you end up with results like this.

So my question...is there a function in MySQL that will actually return the true number of months between two dates that includes the days in the month during the calculation? In the above I should really not be getting a value of 1 until 2009-06-14, not @ 2009-06-01.

Any pointers would be greately appeciated. I am driven mad trying to solve this.

Delirium

Not familiar with MySQL but in T-SQL you can use DATEDIFF to calculate the difference in months. Is that function available in MySQL?

Syntax in T-SQL: DATEDIFF( month, Date1, Date2)

ve

Cheers koth, there is a DATEDIFF function in MySQL, but AFAIK it only returns the number of days between two dates and offers nothing other than that. It would be great if it allowed me to specify the output type in days, months, years, etc, but unfortunately it doesn't seem to do that.

Anyone other ideas, anyone?

showry

haven't really tested this but something like:

SELECT period_diff( date_format( '2009-06-17' , '%Y%m' ) , date_format( '2009-05-14', '%Y%m' ) ) - case when date_format( '2009-06-17' , '%d' ) > date_format( '2009-05-14' , '%d' ) then 0 else 1 end;

am not too well up on mysql so there might be a function to do it, and it might need some extra logic to deal with date 1 being less than date 2

ve

Cheers showry for your suggestion. I see where you are going with your code, but it's a bit hacky compared to what I had in mind. What I am really interested in knowing is if there is indeed a function in MySQL already that can do what I'm looking for?

The DATE/TIME functions (here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html), don't seem to give me that ability, but perhaps I'm missing something.

showry

ve wrote: »

it's a bit hacky compared to what I had in mind.

You say that like it's a bad thing

You could always put it in a userdefined function if you want to keep it tidier but I don't think there's a standard sql function to do it.

rancheros

ve wrote: »

Cheers showry for your suggestion. I see where you are going with your code, but it's a bit hacky compared to what I had in mind. What I am really interested in knowing is if there is indeed a function in MySQL already that can do what I'm looking for?

The DATE/TIME functions (here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html), don't seem to give me that ability, but perhaps I'm missing something.

Dont't think there are any functions, but you could use your code where you get the number of days, put it in a case statement and work out the number of months using a mod function

sicruise

Could you not use something like

((12*(YEAR(firstDate) - YEAR(secondDate))) + (MONTH(firstDate) - MONTH(secondDate)))

but in proper SQL i.e. using SUM etc..

MOH

ve wrote: »

Hi, I'm trying to get a query to work whereby I pass two dates and I get the precise number of months between the two dates in MySQL

SELECT period_diff( date_format( '2009-05-14' , '%Y%m' ) , date_format( '2009-06-01', '%Y%m' ) )

E.g. if I use the PERIOD_DIFF() function along with the DATE_FORMAT() function on two MySQL Date columns, I can in theory get a signed integer, the number of months between them (as above). Right now if I perform a PERIOD_DIFF call and pass E.g. these dates 2009-05-14 AND 2009-05-31, the number of months in difference is 0 (as you would expect), but if you get the difference between 2009-05-14 AND 2009-06-01 the number is now 1. The problem with the PERIOD_DIFF function is that it doesn't take the days in to consideration, only the year and months. So this is why you end up with results like this.

So my question...is there a function in MySQL that will actually return the true number of months between two dates that includes the days in the month during the calculation? In the above I should really not be getting a value of 1 until 2009-06-14, not @ 2009-06-01.

Any pointers would be greately appeciated. I am driven mad trying to solve this.

Are you saying that everything from 2009-05-14 to 2009-06-13 inclusive should return 0, then 1 until 2009-07-13, then 2 etc. ?

Effectively, it's:

SELECT MONTH(date2) - MONTH(date1) ....

unless DAY(MONTH2) - DAY(MONTH1) < 0, in which case you want to subtract 1.

Not sure about the syntax in mysql, but something like

SELECT CASE
WHEN DAY(date2) >= DAY(date1) THEN period_diff( date_format( '2009-05-14' , '%Y%m' ) , date_format( '2009-06-01', '%Y%m' ) )
ELSE period_diff( date_format( '2009-05-14' , '%Y%m' ) , date_format( '2009-06-01', '%Y%m' ) ) - 1

should do it