Excel's Days360 Function

Kan du forklare nærmere hvad du vil opnå?

HM - en måde kunne være:

Function D360(begin_date, end_date)
D360 = Excel.WorksheetFunction.days360(begin_date, end_date, true)
End Function

>MUGS
Jeg skal bruge antal dage mellem to datoer i et 360 dages system formodentlig efter et EU baseret system.

Skal lige se, ast jeg kan bruge excel functionen direkte. Krævel vel at excell er installeret

Fandt denne SQL undersættelse

CREATE FUNCTION dbo.fnDays360
(
@startDate DateTime,
@endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (CASE WHEN Day(@endDate)=31 THEN 30 ELSE Day(@endDate) END) -
  (CASE WHEN Day(@startDate)=31 THEN 30 ELSE Day(@startDate) END)
  + ((DatePart(m, @endDate) + (DatePart(yyyy, @endDate) * 12))
  -(DatePart(m, @startDate) + (DatePart(yyyy, @startDate) * 12))) * 30
END

Her er en anden clon, somarbejder med både us og eu metode:

Excel DAYS360 clone
CREATE FUNCTION dbo.DATEDIFF360
(
    @source DATETIME,
    @target DATETIME,
    @style BIT = 0
)
RETURNS INT
AS
BEGIN
        RETURN  CASE @style
                    -- European style
                    WHEN 1 THEN CASE
                                    WHEN DATEPART(DAY, @target) = 31 THEN 30
                                    ELSE DATEPART(DAY, @target)
                                END
                                - CASE
                                      WHEN DATEPART(DAY, @source) = 31 THEN 30
                                      ELSE DATEPART(DAY, @source)
                                  END
                    -- US style
                    ELSE CASE
                            WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAYOFYEAR, @source) = 60 AND DATEPART(MONTH, @source) = 2 THEN 30
                            WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAY, @source) < 30 THEN 31
                            WHEN DATEPART(DAY, @target) = 31 THEN 30
                            ELSE DATEPART(DAY, @target)
                        END
                        - CASE
                              WHEN @source = DATEADD(MONTH, DATEDIFF(MONTH, -1, @source), -1) THEN 30
                              ELSE DATEPART(DAY, @source)
                          END
                END + 30 * DATEDIFF(MONTH, @source, @target)
END

Så har jeg bixer noget sammen som virker

Public Function DATEDIFF360(Start_Date As Date, Slut_Date As Date, EUStyle As Boolean)
If EUStyle Then
    DAGE = DATEPART2(Slut_Date) - DATEPART2(Start_Date)
Else
    If DatePart("D", Slut_Date) = 31 And DatePart("Y", Start_Date) = 60 And DatePart("M", Start_Date) = 2 Then
        DAGE = 30
    ElseIf DatePart("D", Slut_Date) = 31 And DatePart("D", Start_Date) < 30 Then
       
        DAGE = 31
    Else
        DAGE = DATEPART2(Slut_Date)
    End If
   
    If Start_Date = DateAdd("M", DateDiff("M", -1, Start_Date), -1) Then
        MINUSDAGE = 30
    Else
        MINUSDAGE = DatePart("D", Start_Date)
    End If
   
    DAGE = DAGE - MINUSDAGE
End If

    DATEDIFF360 = DAGE + 30 * DateDiff("M", Start_Date, Slut_Date)
End Function

Public Function DATEPART2(xDate As Date)
    If DatePart("D", xDate) = 31 Then
        DATEPART2 = 30
    Else
        DATEPART2 = DatePart("D", xDate)
    End If
End Function

Sub TEST()
MsgBox DATEDIFF360(#1/1/2009#, #3/21/2010#, True)
End Sub