SQL Server DAYS360 Function

Updated 2023-10-06 23:45:24.680000

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.

Syntax

SELECT [westclintech].[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.