SQL Server - How to Calculate the number of fractional months between two dates (UDF)
Posted on 2009-07-14
I want to create a UDF that Calculates the number of fractional months between two dates.
E.g. from 01/01/2008 to 14/02/2008 = 1.542 Months.
I have tried using Datediff function but that has some issues.
First issue :
It only returns whole months.
second Issue :
The DateDiff function will return the number of month boundaries between 2 dates.
so, 01/01/2008 to 31/01/2008 will return 0. This i have fixed by adding 1 to end date.
but since I cannot get fractions 01/01/2008 to 15/01/2008 returns 0 instead of around 0.5.
Third Issue :
The function should handle the dates even if they span over multiple years e.g 02/03/2006 to 02/03/2008 = 24 months
Kindly give some code to do this. Thanks. More info:
The function will be used to calculate the number of leave someone has earned at the of 1.75 days per month. so if someone starts employment on the (dd/mm/yyyy) 01/01/2008, on the 01/02/2008 he would have 1 month x 1.75 days of leave which is = 1.75
similarly if sumone starts on the 15/01/2008, on the 01/02/2008 he would have earned 0.5 of a month x 1.75 = 0.875 days. So thats why I require fractional months.