Difference between two dates in number of months and days SQL

Question

I'm trying to display the difference between two dates in order to calculate the monthly rent, I'm expecting the exact number of months and days because the contract is "monthly" so if it is February, March or April, the monthly amount is fixed. so let's say: Strat_D = 05-Aug-2020 End_D = 20-Sept-2020 I'm using this code to get number of days:

DateDiff("d",[Start_D],[End_D])

the output is: 45 , but expecting: 1 month & 15 days OR 1.5

and if I use:

DateDiff("m",[Start_D],[End_D])

output is: 1 , but expecting: 1.5

Thanks in advance

This is very tricky. What if the start date is 2020-01-31 and the end date is 2020-02-18? — Gordon Linoff, Aug 05 '20 at 16:45
Yeah, you can't just do that easily, since months are variable in amount of days, and there are varying ways to handle that (e.g. calculate fraction by looking at the amount of days in the last month, fixing all months at 365.25/12 = 30.44 days and calculating from that). I believe the first is more common when doing financial stuff, and the second is more usual for statistics to avoid dips/peaks for long and short months. — Erik A, Aug 05 '20 at 16:57

score 1 · Answer 1 · answered Aug 09 '20 at 10:01

As the day count of months varies, you have to count by days to get as close as possible, as there never (except within a month or within July/August or December/January) will be an exact value:

' Returns the decimal count of months between Date1 and Date2.
'
' Rounds by default to two decimals, as more decimals has no meaning
' due to the varying count of days of a month.
' Optionally, don't round, by setting Round2 to False.
'
' 2017-01-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function TotalMonths( _
    ByVal Date1 As Date, _
    ByVal Date2 As Date, _
    Optional Round2 As Boolean = True) _
    As Double
    
    Dim Months      As Double
    Dim Part1       As Double
    Dim Part2       As Double
    Dim Fraction    As Double
    Dim Result      As Double
    
    Months = DateDiff("m", Date1, Date2)
    Part1 = (Day(Date1) - 1) / DaysInMonth(Date1)
    Part2 = (Day(Date2) - 1) / DaysInMonth(Date2)
    
    If Round2 = True Then
        ' Round to two decimals.
        Fraction = (-Part1 + Part2) * 100
        Result = Months + Int(Fraction + 0.5) / 100
    Else
        Result = Months - Part1 + Part2
    End If
    
    TotalMonths = Result
    
End Function


' Returns the count of days of the month of Date1.
'
' 2016-02-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DaysInMonth( _
    ByVal Date1 As Date) _
    As Integer

    Const MaxDateValue  As Date = #12/31/9999#
    Const MaxDayValue   As Integer = 31

    Dim Days    As Integer

    If DateDiff("m", Date1, MaxDateValue) = 0 Then
        Days = MaxDayValue
    Else
        Days = Day(DateSerial(Year(Date1), Month(Date1) + 1, 0))
    End If

    DaysInMonth = Days

End Function

Results:

? TotalMonths(#2020/08/05#, #2020/09/20#)
 1.5

? TotalMonths(#2020/11/15#, #2021/02/15#)
 3.03

Difference between two dates in number of months and days SQL

1 Answers1