Getting the months and years between two dates using Datediff
There is a round up issue with the Datediff function.
When the reporting date month is the same as the target month you can get a round up
For example using a report date of 2012-05-06
And looking for the age of a person (years and months only)
This code looks like it should work:
declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-01' --yyyy,mm,dd Birth Dates for examples
Select
"Years" = (datediff(month,@mdob,@AsOnDate)/12) , -- Integer Div to get years from total months
"Months" = (datediff(month,@mdob,@AsOnDate)% 12) -- Mod Div to get remaining months
However we get this results for a report date of '2012-05-06' and Birth Date of :
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 1 year 0 months - Wrong - report is 20 days before birthday should be 0 Years 11 months
This round up is very easy to miss!! :unsure:
To correct this we need to check the dates and if the report month and target month are the same then we need to correct for the round up by reducing a month if the target day is greater than the report day (not a full month yet). The following code corrects for this.
declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-07' --yyyy,mm,dd Birth Date for example
Select
"Years" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART(Month, @AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)/12)
ELSE (datediff(month,@mdob,@AsOnDate)/12)
END,
"Months" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART( Month,@AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)% 12)
ELSE (datediff(month,@mdob,@AsOnDate)% 12)
END
Results with correction:
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 0 years 11 months - correct - report is 20 days before birthday