In order to calculate in years, days and months separately, you need to take some care, as some problems go unnoticed if you do not test with different dates.
Calculations based on day difference give problems by the difference of days in each month, and end up returning some strange things when converting to YYYY MM DD, such as rounding errors and broken numbers.
In addition, when you have two dates in the right order, but with a final month less than initial, and / or final day less than the initial day, you need to remember to pass negative days and months to the months and years of the calculation, and add the days of the month in the right amount. These two dates show the problem well, test them on the proposed solutions:
Data inicial | Data final
2013-09-30 | 2014-06-28
One solution would be to use a series of
IF s for this in
SELECT , however, it would have to calculate correctly the conditions for the number of days of each month. It can be resolved with multiple% s of% s, and although complex the result, works well. Fortunately MySQL has several built-in functions to handle dates, sparing you from the boring part.
IF , which already takes into account the days of each month, and to use it correctly, it was enough to extract the days, then the months and finally the years of the initial date, represented by
DATE_SUB() or% with%.
I avoided using
CURRENT_DATE so I did not have to deal with hours, minutes, and seconds.
Follow the query:
DATE_SUB( DATE_SUB( DATE_SUB( CURRENT_DATE,
INTERVAL YEAR( DataDeAdmissao ) - 1 YEAR
), INTERVAL MONTH( DataDeAdmissao ) - 1 MONTH
), INTERVAL DAY( DataDeAdmissao ) - 1 DAY
) AS Calculo,
CURRENT_DATE AS Hoje,
YEAR( @calculo ) - 1,' ANOS, ',
MONTH( @calculo ) - 1,' MESES E ',
DAY( @calculo ) - 1,' DIAS'
) AS Extenso
Test the operation in SQL Fiddle
To not show "0 YEARS", "0 MONTHS" or "0 DAYS", just use the following setting:
The same logic can be used for the plural.
YEAR( @calculo ) - 1,' ANOS, ' ,
IF( YEAR( @calculo) > 1, CONCAT( YEAR( @calculo ) - 1,' ANOS, ' ), '' ) ...
If you are going to use such a thing for work calculations, for example, do not want to solve this in a SELECT. Labor calculation involves a number of extra parameters, and actually counting has to be done following the peculiarities of the legislation.
The concept of difference of days, years and months is not as absolute as it seems. When you talk about "two years of difference," or "three months of difference" you may or may not be considering the extra leap years, it depends how this value will be used. It does not have a solution that works for every case. Always look at the final application you are going to give to the value (it looks strange, but that's right).
Example: "a year" before a February 29 is the first of March, or February 28 of the previous year?
Still based on the previous point: remember that you can reverse the order of subtractions in the select above , to first deal with years, months and days. Depending on the criteria you want, this can make a difference in critical dates and extreme cases.
No additional protections against reversed dates and absurd values were added just to not complicate the response. Probably these cases and possible errors would be handled in the main application anyway.