Click on the banner to learn about and purchase my database training on Azure

How to create a table of holidays (national, state, and mobile) in SQL Server

Views: 11.104 views
Reading Time: 5 minutes

Hello guys,
Good afternoon.

In this quick post, I will demonstrate how to create a table with Brazil's national, state, and mobile holidays. This table is very useful for identifying whether a given date is a holiday or not, and is also a prerequisite for creating the post business day table. How to calculate business days in SQL Server.

Another solution to this problem is by using OLE Automation or CLR, querying the API, which returns the national, state, municipal, and facultative holidays. To learn more about this solution, visit the post. SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #).

Generating the data load

The code below will create the Holiday table, and populate it with national, state and mobile holidays (Carnival, Passion of Christ and Corpus Christi).

View source

After executing the procedure (EXEC dbo.stpGera_Holidays), you get these results:

Holidays in ES on 2015

SQL Server - Holidays ES_2

Holidays in RJ at 2016

SQL Server - Holidays RJ_2

That's it,
Until the next post, where I'll talk about calculations with weekdays (SPOILER).