Calculate 30/360, 30/365 DateDiff

Has anyone come across the need for performing a day difference count betwee
n
2 dates adhering to a 30/360 day convention? Is there a System Function,
Custom Function or Proc which can achieve this ?If I remember correctly that's some sort of financial/bank calculation? If
you could refresh me on the specifics of how it's done by hand I might be
able to find something for you...
"D1p4k" <dipakb@.exchange.ml.com> wrote in message
news:6DE347B3-CBEB-4F23-AC94-DF5E7B9B9484@.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 ?|||On Tue, 26 Jul 2005 12:30:02 -0700, D1p4k wrote:

> Has anyone come across the need for performing a day difference count betw
een
> 2 dates adhering to a 30/360 day convention? Is there a System Function,
> Custom Function or Proc which can achieve this ?
See a usenet thread from Feb 2004 on this topic: http://tinyurl.com/bwglz|||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
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|||> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
6+30+31=67
"D1p4k" <dipakb@.exchange.ml.com> wrote in message
news:5FCA9DFC-15C4-452B-AA1E-9384C55F7756@.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
> 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
>|||On Tue, 26 Jul 2005 13:30:03 -0700, D1p4k wrote:

> 30/360 = The number of accrued days is calculated on the basis of a year o
f
> 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 31s
t
> of the month then that month is considered to have 31 days. In addition, if[/color
]
^^^^^^^^^^^^^
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