DAYS360

Updated 2023-10-06 23:45:24.680000

Syntax

SELECT [westclintech].[wct].[DAYS360] (
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@method, int,>)

Description

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.

Arguments

@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.

@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.

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.

{"columns":[{"field":"Start Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"End Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"30/360 US","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"30E/360","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"30E360 ISDA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Actual","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Start Date":"2007-01-15","End Date":"2007-01-30","30/360 US":"15","30E/360":"15","30E360 ISDA":"15","Actual":"15"},{"Start Date":"2007-01-15","End Date":"2007-02-15","30/360 US":"30","30E/360":"30","30E360 ISDA":"30","Actual":"31"},{"Start Date":"2007-01-15","End Date":"2007-07-15","30/360 US":"180","30E/360":"180","30E360 ISDA":"180","Actual":"181"},{"Start Date":"2007-09-30","End Date":"2008-03-31","30/360 US":"180","30E/360":"180","30E360 ISDA":"180","Actual":"183"},{"Start Date":"2007-09-30","End Date":"2007-10-31","30/360 US":"30","30E/360":"30","30E360 ISDA":"30","Actual":"31"},{"Start Date":"2007-09-30","End Date":"2008-09-30","30/360 US":"360","30E/360":"360","30E360 ISDA":"360","Actual":"366"},{"Start Date":"2007-01-15","End Date":"2007-01-31","30/360 US":"16","30E/360":"15","30E360 ISDA":"15","Actual":"16"},{"Start Date":"2007-01-31","End Date":"2007-02-28","30/360 US":"28","30E/360":"28","30E360 ISDA":"30","Actual":"28"},{"Start Date":"2007-02-28","End Date":"2007-03-31","30/360 US":"30","30E/360":"32","30E360 ISDA":"30","Actual":"31"},{"Start Date":"2006-08-31","End Date":"2007-02-28","30/360 US":"178","30E/360":"178","30E360 ISDA":"180","Actual":"181"},{"Start Date":"2007-02-28","End Date":"2007-08-31","30/360 US":"180","30E/360":"182","30E360 ISDA":"180","Actual":"184"},{"Start Date":"2007-02-14","End Date":"2007-02-28","30/360 US":"14","30E/360":"14","30E360 ISDA":"16","Actual":"14"},{"Start Date":"2007-02-26","End Date":"2008-02-29","30/360 US":"363","30E/360":"363","30E360 ISDA":"364","Actual":"368"},{"Start Date":"2008-02-29","End Date":"2009-02-28","30/360 US":"360","30E/360":"359","30E360 ISDA":"360","Actual":"365"},{"Start Date":"2008-02-29","End Date":"2008-03-30","30/360 US":"30","30E/360":"31","30E360 ISDA":"30","Actual":"30"},{"Start Date":"2008-02-29","End Date":"2008-03-31","30/360 US":"30","30E/360":"31","30E360 ISDA":"30","Actual":"31"},{"Start Date":"2007-02-28","End Date":"2007-03-05","30/360 US":"5","30E/360":"7","30E360 ISDA":"5","Actual":"5"},{"Start Date":"2007-10-31","End Date":"2007-11-28","30/360 US":"28","30E/360":"28","30E360 ISDA":"28","Actual":"28"},{"Start Date":"2007-08-31","End Date":"2008-02-29","30/360 US":"179","30E/360":"179","30E360 ISDA":"180","Actual":"182"},{"Start Date":"2008-02-29","End Date":"2008-08-31","30/360 US":"180","30E/360":"181","30E360 ISDA":"180","Actual":"184"},{"Start Date":"2008-08-31","End Date":"2009-02-28","30/360 US":"178","30E/360":"178","30E360 ISDA":"180","Actual":"181"},{"Start Date":"2009-02-28","End Date":"2009-08-31","30/360 US":"180","30E/360":"182","30E360 ISDA":"180","Actual":"184"}]}