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