Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Serve DAYS360 function


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
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service