Holidays

Obtain holiday data from HolidayAPI.com using SQL Server

Prerequisites:

See SQLHTTP easy setup for HolidayAPI.com
See API Call documentation for parameter values and other information

Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_HolidayAPI_Fetch  @Country = 'US', --enter 2 OR 6 digit country
                           @Year = '2016', --enter 4 digit year
                           @Month = '11', --enter 1 OR 2 digit month
                           @Response = @Response OUTPUT,
                           @StatusCode = @StatusCode OUTPUT,
                           @StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
      EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
      BEGIN

            DECLARE @X xml
            SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

            --The XPath syntax below was easily generated by executing the following commands:
            --EXEC SQLHTTP.net.XQueryHelper @X
            --and then executing: 
            --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/holidays[1]/JsonObject' --
            
            SELECT   T.C.value(N'@date', N'nvarchar(MAX)') AS [date]
                    ,T.C.value(N'@name', N'nvarchar(MAX)') AS [name]
                    ,T.C.value(N'@observed', N'nvarchar(MAX)') AS [observed]
                    ,T.C.value(N'@public', N'nvarchar(MAX)') AS [public]
            FROM @X.nodes(N'/JsonObject/holidays[1]/JsonObject') T(C)

      END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_HolidayAPI_Fetch @Country = 'US', --enter 2 OR 6 digit country

@Year = '2016', --enter 4 digit year

@Month = '11', --enter 1 OR 2 digit month

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/holidays[1]/JsonObject' --

SELECT T.C.value(N'@date', N'nvarchar(MAX)') AS [date]

,T.C.value(N'@name', N'nvarchar(MAX)') AS [name]

,T.C.value(N'@observed', N'nvarchar(MAX)') AS [observed]

,T.C.value(N'@public', N'nvarchar(MAX)') AS [public]

FROM @X.nodes(N'/JsonObject/holidays[1]/JsonObject') T(C)

END

date name public

------------ ----------------------- -----------

2016-11-08 Election Day false

2016-11-08 Presidential Election false

2016-11-11 Veterans Day true

2016-11-24 Thanksgiving Day true

2016-11-25 Black Friday false



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.