DAYSNL

Updated 2023-10-06 23:49:08.500000

Syntax

SELECT [wctFinancial].[wct[DAYSNL] (
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>)

Description

Use the scalar function DAYSNL to calculate the number of days from a start date (inclusive) to an end date (exclusive) excluding all occurrences of Feb-29.

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.

@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

@StartDate cannot be NULL.

@EndDate cannot be NULL.

Examples

This example shows DAYSNL calculation as well as showing the actual number of days between the start date and the end date.

SELECT *,

       wct.DAYSNL([Start Date], [End Date]) as DAYSNL,

       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',

           '2011-01-31'

    UNION ALL

    SELECT '2007-01-31',

           '2011-02-28'

    UNION ALL

    SELECT '2007-02-28',

           '2011-03-31'

    UNION ALL

    SELECT '2006-08-31',

           '2011-02-28'

    UNION ALL

    SELECT '2007-02-28',

           '2011-08-31'

    UNION ALL

    SELECT '2007-02-14',

           '2011-02-28'

    UNION ALL

    SELECT '2007-02-26',

           '2012-02-29'

    UNION ALL

    SELECT '2008-02-29',

           '2013-02-28'

    UNION ALL

    SELECT '2008-02-29',

           '2012-03-30'

    UNION ALL

    SELECT '2008-02-29',

           '2012-03-31'

    UNION ALL

    SELECT '2007-02-28',

           '2011-03-05'

    UNION ALL

    SELECT '2007-10-31',

           '2011-11-28'

    UNION ALL

    SELECT '2007-08-31',

           '2012-02-29'

    UNION ALL

    SELECT '2008-02-29',

           '2012-08-31'

    UNION ALL

    SELECT '2008-08-31',

           '2012-02-28'

    UNION ALL

    SELECT '2009-02-28',

           '2012-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":"DAYSNL","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","DAYSNL":"15","Actual":"15"},{"Start Date":"2007-01-15","End Date":"2007-02-15","DAYSNL":"31","Actual":"31"},{"Start Date":"2007-01-15","End Date":"2007-07-15","DAYSNL":"181","Actual":"181"},{"Start Date":"2007-09-30","End Date":"2008-03-31","DAYSNL":"182","Actual":"183"},{"Start Date":"2007-09-30","End Date":"2007-10-31","DAYSNL":"31","Actual":"31"},{"Start Date":"2007-09-30","End Date":"2008-09-30","DAYSNL":"365","Actual":"366"},{"Start Date":"2007-01-15","End Date":"2011-01-31","DAYSNL":"1476","Actual":"1477"},{"Start Date":"2007-01-31","End Date":"2011-02-28","DAYSNL":"1488","Actual":"1489"},{"Start Date":"2007-02-28","End Date":"2011-03-31","DAYSNL":"1491","Actual":"1492"},{"Start Date":"2006-08-31","End Date":"2011-02-28","DAYSNL":"1641","Actual":"1642"},{"Start Date":"2007-02-28","End Date":"2011-08-31","DAYSNL":"1644","Actual":"1645"},{"Start Date":"2007-02-14","End Date":"2011-02-28","DAYSNL":"1474","Actual":"1475"},{"Start Date":"2007-02-26","End Date":"2012-02-29","DAYSNL":"1827","Actual":"1829"},{"Start Date":"2008-02-29","End Date":"2013-02-28","DAYSNL":"1824","Actual":"1826"},{"Start Date":"2008-02-29","End Date":"2012-03-30","DAYSNL":"1489","Actual":"1491"},{"Start Date":"2008-02-29","End Date":"2012-03-31","DAYSNL":"1490","Actual":"1492"},{"Start Date":"2007-02-28","End Date":"2011-03-05","DAYSNL":"1465","Actual":"1466"},{"Start Date":"2007-10-31","End Date":"2011-11-28","DAYSNL":"1488","Actual":"1489"},{"Start Date":"2007-08-31","End Date":"2012-02-29","DAYSNL":"1641","Actual":"1643"},{"Start Date":"2008-02-29","End Date":"2012-08-31","DAYSNL":"1643","Actual":"1645"},{"Start Date":"2008-08-31","End Date":"2012-02-28","DAYSNL":"1276","Actual":"1276"},{"Start Date":"2009-02-28","End Date":"2012-08-31","DAYSNL":"1279","Actual":"1280"}]}