Working with dates can be a challenging aspect of IT, especially when it comes to calculating business days. SQL Server doesn’t provide many tools to handle these types of calculations, but fortunately, there are third-party libraries available that can help.

One such library is XLeratorDB by Westclintech. XLeratorDB offers a robust set of SQL Server CLR functions specifically designed for handling business date logic. These functions can calculate the next business day, previous business day, first occurrence of a weekday in a month, last occurrence of a weekday in a month, and more.

Let’s take a look at an example to understand how these functions can be used. Suppose we need to produce a report that shows the average daily trading volumes, the total number of trading days in a year, and the total number of trading days by month for a year. To calculate the number of business days, we need to consider weekends and holidays.

XLeratorDB provides functions to capture holiday information, such as New Year’s Day, Martin Luther King Jr. Day, Independence Day, and Christmas. These functions can be used to calculate the holidays for the stock exchange.

SELECT x.holiday, CAST(x.hdate AS date) AS hdate
FROM (
    SELECT
        wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day],
        wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day],
        wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday],
        wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday],
        wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day],
        wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day],
        wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day],
        wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day],
        wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas
    FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020)) n(y)
) p
CROSS APPLY (
    VALUES
        ('New Year''s Day',[New Year's Day]),
        ('MLK Day',[MLK Day]),
        ('Washington''s Birthday',[Washington's Birthday]),
        ('Good Friday',[Good Friday]),
        ('Memorial Day',[Memorial Day]),
        ('Independence Day',[Independence Day]),
        ('Labor Day', [Labor Day]),
        ('Thanksgiving Day',[Thanksgiving Day]),
        ('Christmas',Christmas)
) x(holiday, hdate)

Once we have the holiday information, we can use the XLeratorDB function BUSDAYS to calculate the number of business days between two dates. For example, to calculate the number of business days in 2013:

DECLARE @hol AS VARCHAR(MAX) = (
    SELECT wct.NBD(CAST(x.hdate AS date))
    FROM (
        SELECT
            wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day],
            wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day],
            wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday],
            wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday],
            wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day],
            wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day],
            wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day],
            wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day],
            wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas
        FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020)) n(y)
    ) p
    CROSS APPLY (
        VALUES
            ('New Year''s Day',[New Year's Day]),
            ('MLK Day',[MLK Day]),
            ('Washington''s Birthday',[Washington's Birthday]),
            ('Good Friday',[Good Friday]),
            ('Memorial Day',[Memorial Day]),
            ('Independence Day',[Independence Day]),
            ('Labor Day', [Labor Day]),
            ('Thanksgiving Day',[Thanksgiving Day]),
            ('Christmas',Christmas)
    ) x(holiday, hdate)
)

SELECT wct.BUSDAYS('2013-01-01','2014-01-01',@hol) AS [Business Days]

This will return the number of business days in 2013, which is 252.

We can also calculate the number of business days for each year from 2013 to 2020:

SELECT n.y AS [Year], wct.BUSDAYS(wct.CALCDATE(n.y,01,01),wct.CALCDATE(n.y+1,01,01),@hol) AS [Business Days]
FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020)) n(y)

This will produce a result showing the number of business days for each year.

Additionally, we can calculate the number of business days in each month of a specific year:

SELECT DATENAME(m,wct.CALCDATE(2013,n.m,1)) AS [Month], wct.BUSDAYS(wct.CALCDATE(2013,n.m,1),wct.CALCDATE(2013,n.m+1,1),@hol) AS [Business Days]
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) n(m)

This will give us the number of business days for each month in 2013.

Finally, we can explicitly calculate each business date in a specific year:

SELECT CAST(wct.BUSINESSDATE(wct.CALCDATE(2013,1,1),'D',k.seriesvalue,'F',@hol) AS date) AS [Work day]
FROM wct.SeriesInt(1,252,NULL,NULL,NULL) k

This will generate a table of all the business dates in 2013.

XLeratorDB also provides a function called BUSINESSDATEWE that allows you to specify which days of the week are considered weekends. This can be useful when calculating delivery dates, for example:

SELECT n.shiptype, CAST(wct.BUSINESSDATEWE(wct.CALCDATE(2013,11,25),'D',n.d,'F',@hol,0,NULL) AS date) AS [Expected Delivery]
FROM (VALUES (1,'Overnight'),(2,'Two-day'),(3,'Regular Ground'),(4,'Regular Gound'),(5,'Bulk')) n(d,shiptype)

This will calculate the expected delivery dates for different types of shipments, considering weekends and holidays.

As you can see, XLeratorDB provides a comprehensive set of functions for handling business date logic in SQL Server. These functions can simplify complex date calculations and make working with dates much easier for developers.

So, if you find yourself struggling with date calculations in SQL Server, give XLeratorDB a try and see how it can help you streamline your code.