两个日期在月数和天数上的差异SQL

Question

为了计算月租金，我试图显示两个日期之间的差异，我期望月和日的确切数量，因为合同是“每月”的，所以如果是2月、3月或4月，每月的金额是固定的。比如说：Strat_D=05-Aug-2020 End_D=20-Sept-2020我用这个代码来计算天数：

DateDiff（"d"，[Start_D]，[End_D]）

输出为：45，但预期为：1个月

如果我使用：

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

输出为：1，但预期：1.5

提前谢谢

Answer 1

由于月份的天数不同，您必须按天计算以尽可能接近，因为没有确切的值（一个月内或7月/8月或12月/1月内除外）：

' 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

结果：

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

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