DAYS 360 Function

Hi,

Does anyone has the DAYS360 excel formula in a function in sqlserver ? I did this one

FUNCTION dbo.fnDays360_EXCEL
(
@.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

But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...

Does anyone has the solution ?

Thanks

Hi,

Do you mean 30/360? HEre's some C# code that was tested thouroughly, you should be able to get the idea. If you meant Act/360 get back to me.

Good luck,

John

double YearFrac(DateTime dtStartDate, DateTime dtEndDate, int iDaycount)

{

/* According to Excel:

Basis Day count basis

0 or omitted US (NASD) 30/360

1 Actual/actual

2 Actual/360

3 Actual/365

4 European 30/360

7 Bus/252

*/

switch( iDaycount )

{

case 0: // 30/360 (ISDA)

{

int d1, m1, y1, d2, m2, y2;

d1 = dtStartDate.Day;

m1 = dtStartDate.Month;

y1 = dtStartDate.Year;

d2 = dtEndDate.Day;

m2 = dtEndDate.Month;

y2 = dtEndDate.Year;

// ISDA rules

if (d1 == 31) d1 = 30;

if (d2 == 31 && d1 == 30) d2 = 30;

return (360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)) / 360e0;

}

|||

Use the following code...

Code Snippet

Create Function dbo.Days360

(

@.StartDate Datetime,

@.EndDate Datetime

)

Returns Int

as

Begin

Declare

@.d1 int, @.d2 int,

@.m1 int, @.m2 int,

@.y1 int, @.y2 int;

Select @.d1 = Day(@.StartDate), @.m1 = Month(@.StartDate), @.y1 = Year(@.StartDate),

@.d2 = Day(@.EndDate), @.m2 = Month(@.EndDate), @.y2 = Year(@.EndDate)

If (day(@.StartDate) = 1) And (month(@.StartDate) = 3)

Select @.d1 = 30

If (@.d2 = 31) And (@.d1 = 30)-- Then

Select @.d2 = 30

Return ((@.y2 - @.Y1) * 360) + ((@.m2 - @.m1) * 30) + (@.d2 - @.d1)

End

go

Select dbo.Days360('1/20/2007', '2/3/2007')

|||

Hi,

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

Thanks

|||

Hi

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

And for this one also..

Select dbo.Days360('2/28/2007', '3/31/2007') that must return 30

For the C# code...is just this last error