SQL Server flexible date Functions and calendar Table

If you have a shared database, or know where you want to store it these functions then feel free, however all code from here has been designed to run in a single script in SQL Server 2016.

Note! - It may be easier to create your database in SSMS

Tip! - Set the options to simple to avoid log file bloat and the data is less likely to be business critical.

To make it easier to manage, we have created a schema called Dates, and store all of our related information in this. Think of the schema in this sense as a container or namespace for related objects.

Tip! - When creating a schema in batch SQL statements, wrap it into an sp_executesql statement to avoid the system complaining about it being the only statement in the batch. If it may exist then wrap it in a try block to allow the code to run through.

There are a couple of shared functions that will get used further on, so we are going to provide the code and link, with an explanation on each page. There are a couple of things to note:

Standard DateDiff year calculations are purely year-year, so to calculate age you need to check whether that date is higher or not. Our GetLeapYear function was the most sensible way to work out if we needed to add a day.

Easter date can fail if you don't use the new DateFromParts function, because of lack of day padding.

For scheduling and recurrence, we use our own scheduling table. This enables us to calculate how often something should be repeated.

Our calendar table is set set up to run for any period of time within the DATETIME2 range. In general use, the code below will give you a range that would cover most scenarios.

CalendarCA and CalendarCD are the week numbers ascending and descending for each month, and used in a cross reference against our frequency table above.

We designed the holidays table to be as flexible as possible, and allow for different options to be stored by adding the Calendar Function column. Below we have stored the holidays for England & Wales, Scotland and Northern Ireland separately, cross referencing the calendar table already created.

In theory you can have as many schemes as you like.

There are three functions used that combine them in various ways, all with very similar options.

• GetDateAdjusted moves the date forward or backwards by a set number of days, dependent on the calendar function, and whether you want to include holidays and or weekends.
  
• GetDaysAdjusted counts the number of days between two dates, dependent on the calendar function, and whether you want to include holidays and or weekends. Dates can be counted inclusively or exclusively.
• GetMonthAdjusted counts the number of days in a month, dependent on the calendar function, and whether you want to include holidays and or weekends. Dates can be counted inclusively or exclusively.
  
• GetMonthDay returns a specific day of the month, first, second, last etc.

While you can add a join into your select statement, we have found it easier to reference in another function. Adding two dates, the frequency and both the ascending and descending monthly week number the relevant dates can be checked for a match.

We can now test this data by generating a fake scheduling table.

In our example creating a report to go on the first of January 2017, and repeat every month on the first with every possible date action available.

Looking at 01/01/2018 in isolation, weekend and bank holiday behavior could change the date range to anything from 29/12/2017 to 02/01/2018.