DAYS360
Updated: 11 May 2012
Use the scalar function DAYS360 to calculate the number of days from a start date (inclusive) to an end date (exclusive) using any of several 30/360 day count conventions.
Syntax
SELECT [wctFinancial].[wct].[DAYS360] (
<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@method, int,>)
Arguments
@StartDate
the start date for the calculation. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@EndDate
the end date for the calculation. @EndDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@method
A numeric identifier indicating which 30/360 day count convention to use. Use 0 for the US method (also known as 30/360 US), 1 for the Special German method (also known as 30E/360, 30/360 ICMA, and Eurobond basis), or 2 for the German method (also known as 30E360 ISDA). @method is an expression of type int or of a type that can be implicitly converted to int.
Return Type
int
Remarks
· If @method < 0 or @method > 2 an error message is generated.
· If @method is NULL, then @method = 0
· @StartDate cannot be NULL
· @EndDate cannot be NULL
Examples
This example shows the three different calculation methods for a variety of date combinations as well as showing the actual number of days between the start date and the end date.
SELECT *
,wct.DAYS360([Start Date],[End Date], 0) as [30/360 US]
,wct.DAYS360([Start Date],[End Date], 1) as [30E/360]
,wct.DAYS360([Start Date],[End Date], 2) as [30E360 ISDA]
,DATEDIFF(d,[Start Date],[End Date]) as Actual
FROM (
SELECT '2007-01-15','2007-01-30' UNION ALL
SELECT '2007-01-15','2007-02-15' UNION ALL
SELECT '2007-01-15','2007-07-15' UNION ALL
SELECT '2007-09-30','2008-03-31' UNION ALL
SELECT '2007-09-30','2007-10-31' UNION ALL
SELECT '2007-09-30','2008-09-30' UNION ALL
SELECT '2007-01-15','2007-01-31' UNION ALL
SELECT '2007-01-31','2007-02-28' UNION ALL
SELECT '2007-02-28','2007-03-31' UNION ALL
SELECT '2006-08-31','2007-02-28' UNION ALL
SELECT '2007-02-28','2007-08-31' UNION ALL
SELECT '2007-02-14','2007-02-28' UNION ALL
SELECT '2007-02-26','2008-02-29' UNION ALL
SELECT '2008-02-29','2009-02-28' UNION ALL
SELECT '2008-02-29','2008-03-30' UNION ALL
SELECT '2008-02-29','2008-03-31' UNION ALL
SELECT '2007-02-28','2007-03-05' UNION ALL
SELECT '2007-10-31','2007-11-28' UNION ALL
SELECT '2007-08-31','2008-02-29' UNION ALL
SELECT '2008-02-29','2008-08-31' UNION ALL
SELECT '2008-08-31','2009-02-28' UNION ALL
SELECT '2009-02-28','2009-08-31'
) n([Start Date], [End Date])
This produces the following result
Start Date End Date 30/360 US 30E/360 30E360 ISDA Actual
---------- ---------- ----------- ----------- ----------- -----------
2007-01-15 2007-01-30 15 15 15 15
2007-01-15 2007-02-15 30 30 30 31
2007-01-15 2007-07-15 180 180 180 181
2007-09-30 2008-03-31 180 180 180 183
2007-09-30 2007-10-31 30 30 30 31
2007-09-30 2008-09-30 360 360 360 366
2007-01-15 2007-01-31 16 15 15 16
2007-01-31 2007-02-28 28 28 30 28
2007-02-28 2007-03-31 30 32 30 31
2006-08-31 2007-02-28 178 178 180 181
2007-02-28 2007-08-31 180 182 180 184
2007-02-14 2007-02-28 14 14 16 14
2007-02-26 2008-02-29 363 363 364 368
2008-02-29 2009-02-28 360 359 360 365
2008-02-29 2008-03-30 30 31 30 30
2008-02-29 2008-03-31 30 31 30 31
2007-02-28 2007-03-05 5 7 5 5
2007-10-31 2007-11-28 28 28 28 28
2007-08-31 2008-02-29 179 179 180 182
2008-02-29 2008-08-31 180 181 180 184
2008-08-31 2009-02-28 178 178 180 181
2009-02-28 2009-08-31 180 182 180 184