Re: Calculate 30/360, 30/365 DateDiff

650 views
Skip to first unread message

Michael C#

unread,
Jul 26, 2005, 4:10:22 PM7/26/05
to
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" <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 ?


D1p4k

unread,
Jul 26, 2005, 3:30:02 PM7/26/05
to

D1p4k

unread,
Jul 26, 2005, 4:30:03 PM7/26/05
to
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


Ross Presser

unread,
Jul 26, 2005, 3:56:35 PM7/26/05
to

See a usenet thread from Feb 2004 on this topic: http://tinyurl.com/bwglz

Uri Dimant

unread,
Jul 26, 2005, 10:29:52 PM7/26/05
to
> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.

6+30+31=67

"D1p4k" <dip...@exchange.ml.com> wrote in message

news:5FCA9DFC-15C4-452B...@microsoft.com...

Ross Presser

unread,
Jul 27, 2005, 11:59:25 AM7/27/05
to
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 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

Estuardo Boesche

unread,
Aug 19, 2009, 6:50:41 PM8/19/09
to

Peso

unread,
Aug 20, 2009, 3:44:36 AM8/20/09
to
It depends if you want the European calculation style or North American
calculation style.
See here
http://weblogs.sqlteam.com/peterl/archive/2009/03/15/Excel-DAYS360-clone.aspx

Peso

unread,
Aug 20, 2009, 4:13:30 AM8/20/09
to
No, it doesn't. Try with these three dates.

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

Peso

unread,
Aug 20, 2009, 4:17:22 AM8/20/09
to
I set up a test lab with 4,194,304 date pairs.
These I compared with Excel and my formula is spot on and no value differs
from the Excel formula.

However, yor formula returns wrong value for 91,890 date pairs (2.2%) using
US style, and 156,890 (3.7%) using EUR style.

Peso

unread,
Aug 20, 2009, 4:22:02 AM8/20/09
to
It depends if you want the European calculation style or North American
calculation style.
See here for a complete function http://weblogs.sqlteam.com/peterl/archive/2009/03/15/Excel-DAYS360-clone.aspx

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

Reply all
Reply to author
Forward
0 new messages