SQL Server DAYSNL Function

Updated 2023-10-06 23:49:08.500000

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.

Syntax

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

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.

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.