I recommend that you download XLeratorDB and try this problem along with me, this is going to be a fun exercise and something new to learn about Business Days.
Working with dates can be one of the more challenging aspects of IT. First, as developers we are sticking with the calendar that has a non-uniform implementation of a year (approximately 365 97/400 days) causing the number of days in the year to fluctuate from year-to-year; an imprecise definition of a month (anywhere from 28 to 31 days); and requirements that our systems understand what the rest of the world means when the terms month and years are being bandied about. For example, if something is due in one month’s time, does that mean in 30 days, in 4 weeks, on the same day of the month as today unless it’s not a business day, or something else?
Further, most of the time when our user is talking about days, they are not really so much concerned with the mathematical difference between two dates so much as they are concerned with the number of business days between 2 dates. For example, when we promise overnight delivery, that promise is usually means delivery on the next business day.
SQL Server doesn’t really provide many tools that help with these type of calculations. True, SQL Server 2012 contains some interesting new date function like EOMONTH and DATEFROMPARTS, but any coding for business date logic is left to the developers
Fortunately, the team at Westclintech has a robust library of SQL Server CLR functions for handling business date logic. They have functions to calculate the:
- next business day;
- next n business days;
- previous business day;
- previous n business days;
- first occurrence of a weekday in a month;
- last occurrence of a weekday in a month;
- the number of business days between 2 dates (inclusive); and
- the number of business days between 2 dates (exclusive)
They also have a number of functions that do the following:
- convert elapsed time to a fraction of a year;
- count the number of days between two dates assuming 360-day year;
- calculate Western Easter;
- Calculate the end-of-month n months forward or backward;
- Calculate and exact date n months forward or backward;
and many more.
I have been trying their XLeratorDB library out and it allows me to do almost any kind of date calculation that I can think use very simple TSQL statements.
Here’s a typical example of the type of information that business users take for granted but that can quite difficult to calculate in SQL Server. Let’s say you needed to produce a report that showed average daily trading volumes the total number of trading days in a year as well as the total number of trading days by month for a year. With a quick internet search you discover that the stock exchange is closed on Saturdays and Sundays and on the following holidays:
- New Year’s Day (January 1st)
- Martin Luther King, Jr. Day (third Monday in January)
- Washington’s Birthday (third Monday in February)
- Good Friday (the Friday before Western Easter)
- Memorial Day (the last Monday in May)
- Independence Day (July 4th)
- Labor Day (the first Monday in September)
- Thanksgiving Day (the fourth Thursday in November)
- Christmas Day (December 25th)
Additionally, if a holiday falls on a Saturday it is observed on the previous Friday. If a holiday falls on a Sunday, it is observed on the subsequent Monday.
XLeratorDB has several functions that help to capture the holiday information above. In this example, I use the BUSINESSDATE, FIRSTWEEKDAY, LASTWEEKDAY, EASTER, and CALCDATE functions 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)
Here are the first few rows of the resultant table.
holiday hdate --------------------- ---------- New Year's Day 2013-01-01 MLK Day 2013-01-21 Washington's Birthday 2013-02-18 Good Friday 2013-03-29 Memorial Day 2013-05-27 Independence Day 2013-07-04 Labor Day 2013-09-02 Thanksgiving Day 2013-11-28 Christmas 2013-12-25 New Year's Day 2014-01-01 MLK Day 2014-01-20 Washington's Birthday 2014-02-17 Good Friday 2014-04-18 Memorial Day 2014-05-26 Independence Day 2014-07-04 Labor Day 2014-09-01 Thanksgiving Day 2014-11-27 Christmas 2014-12-25. . .
SQL Server 2012 users will notice that the XLeratorDB CALCDATE function is very much like the DATEFROMPARTS function. In many cases, I find it easier to use the CALCDATE function, as it works just like the DATE function in EXCEL, whereas the DATEFROMPARTS function is much less flexible in terms of input. When I enter DATE(2013,3,0) in EXCEL or wct.CALCDATE(2013,3,0) they both understand that I mean 2013-02-28. However, when I try this statement in SQL Server 2012
SELECT DATEFROMPARTS (2013,3,0)
I get the following result.
Msg 289, Level 16, State 1, Line 9 Cannot construct data type date, some of the arguments have values which are not valid.
Sure, I can probably code around that, but why would I want to?
XLeratorDB provides a function that store all the holidays in a scalar variable to be used in the calculation of business days. We can use this information to calculate the number of business days in a year.
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)
)
Here’s a very simple example to get stated. We will calculate the number of business days in 2013 using the holiday variable that we just populated.
SELECT wct.BUSDAYS('2013-01-01','2014-01-01',@hol) AS [Business Days]
This returns the following result.
Business Days ------------- 252
If we wanted to know the number of business days for each year from 2013 to 2020, we could have used the following SQL.
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 produces the following result.
Year Business Days ----------- ------------- 2013 252 2014 252 2015 252 2016 252 2017 251 2018 252 2019 252 2020 253
Here we calculate the number of business days in each month in 2013.
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 produces the following result.
Month Business Days ------------------------------ ------------- January 21 February 19 March 20 April 22 May 22 June 20 July 22 August 22 September 20 October 23 November 20 December 21
And in this example, we explicitly calculate each business date in 2013. I am using a number-generating table-valued function from the XLeratorDB/math library to generate integer values from 1 to 252 and then passing the integer values into the BUSINESSDATE function to calculate each business date in 2013 starting from 2013-01-01.
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
Here are some of the rows in the resultant table.
Work day ---------- 2013-01-02 2013-01-03 2013-01-04 2013-01-07 2013-01-08 2013-01-09 2013-01-10 2013-01-11 2013-01-14 2013-01-15 2013-01-16 2013-01-17 2013-01-18 2013-01-22 2013-01-23 2013-01-24 2013-01-25 2013-01-28 2013-01-29 2013-01-30 2013-01-31... 2013-12-19 2013-12-20 2013-12-23 2013-12-24 2013-12-26 2013-12-27 2013-12-30 2013-12-31
XLeratorDB also provides a function that allows me to specify what the weekend days are when I want to do the business date calculation.
In this example, I want to calculate delivery dates for packages using the holidays that I have already calculated about, but I don’t want Saturday’s to be a weekend day, as deliveries can be made on a Saturday.
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 produces the following result.
shiptype Expected Delivery -------------- ----------------- Overnight 2013-11-26 Two-day 2013-11-27 Regular Ground 2013-11-29 Regular Gound 2013-11-30 Bulk 2013-12-02
As you can see, no delivery was predicted for Thanksgiving (2013-11-28), but Saturday (2013-11-30) was included.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
This Business Day Calculation is similar to Excel Functions NETWORKDAYS & WORKDAY.
Hi Pinal,
Thanks for your solutions and support. We are learning lot of things from your Blog.
I got a very difficul problem, in SSIS i need to develop a package which migrate the data sql server to sql server, but the problem is
i have a column like
column1
————
2
8
4
16
512
1024
2048
i need to derive this column to log2(column1) means
2^1, 2^3, 2^2, 2^4, 2^9,..
after deriving column values should appear like
1
3
2
4
9
10
11
that means the colum values are 2power(x) i ned to derive this x , please suggest me.
even i dint get the vb code to derive power value.
Please please please please save me.
Hi Raj,
Try this:
SELECT LOG() / LOG(2)
FROM
Setting the ‘”/LOG(2)” portion simulates a different default base. Figured it was about time I gave something back to this site!
Todd
Sorry, should have had
SELECT LOG(column) / LOG(2)
FROM table
realised I put them in tags by mistake.
Todd
hi – i am trying to exec a report daily – all through the year.
for the next year(first working day of the year) – i want the previous data to be deleted and exec the same proc as always it did for prev year.
i am not able to find the first working day for every year. can you help me here with a simpler liner solution?
Hi Srividya,
I haven’t provided any SQL because there are too many unknowns with what you are trying to do, so I am only suggesting possible design approaches.
You have a challenge here in so much that the first working day of the year is not the same in every region, in every country. In the UK, it is different between England and Scotland. It may very well be different from state to state in the US. If you want to make this portable for globalisation at all, the best way would be to have a lookup table to provide you with the values, using country codes to provide the date value you need. There will only be a handful of exceptions, and everything else can be default.
Outside of that, if you assume that 02/01XXXX is the first possible working day everywhere,then you could simply interrogate that day of week to determine whether it is a work day or not. If not, use that date to find the next Monday and use a standard DATEADD() to advance the date value.
Regards
Todd