NBD

Updated 2023-10-06 23:58:33.710000

Syntax

SELECT [wctFinancial].[wct].[NBD] (
   <@Holiday, datetime,>)

Description

Use the aggregate function NBD to create a comma separated string of non-business dates in the format YYYYMMDD. This string is used in various programs to store holiday information used in date calculations.

Arguments

@Holiday

the holiday or non-business day to be included in the concatenated result. @Holiday is an expression of type datetime or of a type that can be implicitly converted to datetime.

Return Type

nvarchar(max)

Remarks

SQL Server 2005 users need to limit the length of the concatenated string to 8,000 bytes. The length of the string is the (number of holidays * 9) – 1, implying a limit of 889 non-business days in the string.

Weekend days do not need to be specified as non-business days.

Examples

For Example purposes, we have put the US holiday calendar into a table called HOLIDAYS which is keyed by country code and the holiday date.

CREATE TABLE HOLIDAYS

(

    COUNTRY CHAR(2) NOT NULL,

    HOLIDAY DATETIME NOT NULL,

    PRIMARY KEY (

                    COUNTRY,

                    HOLIDAY

                )

);

INSERT INTO HOLIDAYS

VALUES

('US', '2012-01-02');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-01-16');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-02-20');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-05-28');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-07-04');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-09-03');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-10-08');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-11-12');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-11-22');

INSERT INTO HOLIDAYS

VALUES

('US', '2012-12-25');

Now we can populate the NBD string.

SELECT wct.NBD(holiday) as NBD

FROM HOLIDAYS

WHERE COUNTRY = 'US'

      and YEAR(Holiday) = 2012;

This produces the following result.

{"columns":[{"field":"NBD"}],"rows":[{"NBD":"20120102,20120116,20120220,20120528,20120704,20120903,20121008,20121112,20121122,20121225"}]}

Normally, there will be no need to return the NBD string. It can just be passed into functions as required. In this example, we will create a string of US holidays and then calculate the number of business days between 2021-05-15 and 2012-07-15.

SELECT wct.BUSDAYS(   '2012-05-15',    --Start Date

                      '2012-07-15',    --End Date

                      wct.NBD(holiday) --Non-business Days

                  ) as BUSDAYS

FROM HOLIDAYS

WHERE COUNTRY = 'US';