CODEWITHSUNDEEP
sqlms-access-2016
Osama
Osama

Reputation: 21

Difference between two dates in number of months and days SQL

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

Upvotes: 2

Views: 613

Answers (1)

Gustav
Gustav

Reputation: 55816

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

Upvotes: 1

Related Questions