"D1p4k" <dip...@exchange.ml.com> wrote in message
news:6DE347B3-CBEB-4F23...@microsoft.com...
> Has anyone come across the need for performing a day difference count
> between
> 2 dates adhering to a 30/360 day convention? Is there a System Function,
> Custom Function or Proc which can achieve this ?
Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
An Actual DateDiff would give you 67 Days
6+30+31=67
"D1p4k" <dip...@exchange.ml.com> wrote in message
news:5FCA9DFC-15C4-452B...@microsoft.com...
> 30/360 = The number of accrued days is calculated on the basis of a year of
> 360 days and a month of 30 days. However, if the first date of an accrual
> period is not the 30th or 31st, and the last date of the period is the 31st
> of the month then that month is considered to have 31 days. In addition, if
^^^^^^^^^^^^^
It's not clear what you mean by "that month": do you mean that the month of
the start of the period is considered to have 31, or that the month of the
end of the period is considered to have 31?
> the last date of the period is the last day of February, the month of
> February shall not be extended to a 30-day month.
>
> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
>
> An Actual DateDiff would give you 67 Days
Tell me if this works for you.
CREATE FUNCTION dbo.DayCount30_360Days(@d1 DATETIME, @d2 DATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT DATEDIFF(MONTH, @d1, @d2) * 30
+ DAY(@d2)
- DAY(@d1)
- case when day(@d1)>=30 AND day(@d2) = 31 then 1 else 0 end
)
END
GO
Select dbo.DayCount30_360Days('1996-10-25','1996-12-31')
-- result: 66
Select dbo.DayCount30_360Days('1996-10-26','1996-12-31')
-- result: 65
Select dbo.DayCount30_360Days('1996-10-27','1996-12-31')
-- result: 64
Select dbo.DayCount30_360Days('1996-10-28','1996-12-31')
-- result: 63
Select dbo.DayCount30_360Days('1996-10-29','1996-12-31')
-- result: 62
Select dbo.Daycount30_360Days('1996-10-30','1996-12-31')
-- result: 60
From http://www.developmentnow.com/g/113_2005_7_0_0_568087/Calculate-30360-30365-DateDiff.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
FromDate ToDate Excel_US Excel_EUR Peso_US Peso_EUR Estuardo
2036-08-31 2037-06-10 280 280 280 280 279
2036-01-31 2037-10-28 628 628 628 628 627
2036-05-31 2037-06-05 365 365 365 365 364
However, yor formula returns wrong value for 91,890 date pairs (2.2%) using
US style, and 156,890 (3.7%) using EUR style.
And no, your function doesn't work correctly.
No, it doesn't. Try with these three dates.
FromDate -- ToDate -- Excel_US -- Excel_EUR -- Peso_US -- Peso_EUR -- Estuardo/Ross
2036-08-31 -- 2037-06-10 -- 280 -- 280 -- 280 -- 280 -- 279
2036-01-31 -- 2037-10-28 -- 628 -- 628 -- 628 -- 628 -- 627
2036-05-31 -- 2037-06-05 -- 365 -- 365 -- 365 -- 365 -- 364