Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL server number of days no leap-year function


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
 

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service