Calculating business days in SQL Server
May
30
Written by:
Charles Flock
5/30/2012 4:42 PM
Release 1.09 of XLeratorDB / financial and XLeratorDB / financial 2008 contains 5 new date functions: BUSDAYS, BUSINESSDATE, BUSINESSDATEWE, DAYS360 and DAYSNL. In this article, we explore how these functions work and the kinds of calculations that can now easily be done in your SQL Server database.
BUSDAYS
The BUSDAYS function calculates the number of business days from a start date (inclusive) to an end date (exclusive). Saturdays and Sundays are not considered to be business days, and there is a holiday parameter passed into the function which contains the other non-business days to be used in the calculation. One of the ways that we use this function is to calculate the accrued interest for Brazilian government bonds, where interest is only accrued on Brazilian business days.
There is no exact equivalent for the BUSDAYS function in EXCEL. There is a NETWORKDAYS function (which is also in XLeratorDB), but the NETWORKDAYS function includes both the start date and the end date in the calculation of the number of days. For example, NETWORKDAYS calculates the number of days from 2012-05-29 to 2012-05-30 as 2. For interest accrual calculations, we would expect the number of days to be 1. We looked at using the NETWORKDAYS function for the interest calculations, but the nuances of knowing whether or not the start date and/or the end date were non-business days made it impractical.
Given the complexity of trying to use the existing NETWORKDAYS function, we created the new function BUSDAYS to calculate the number of business days between two dates, taking holidays into account. The function has 3 parameters: a start date, an end date, and a comma separated string containing the non-business dates in the format YYYYMMDD. We also created a new aggregate, NBD, which will take the non-business-day dates and put them into the string format used by the function. The NBD function automatically eliminate duplicates.
In our environment we have a table called HOLIDAYS containing the non-business-day dates for each country. We would calculate the number of business days from 2012-05-01 to 2012-06-01 with the following statement.
SELECT wct.BUSDAYS(
'2012-05-01' --Start Date
,'2012-06-01' --End Date
,wct.NBD(holiday) --Holidays
) as BUSDAYS
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
BUSDAYS
-----------
22
How do we know that number is correct?
We can use the XLeratorDB SeriesDate function to create all the dates from 2012-05-01 to 2012-06-01, excluding the Saturdays and Sundays as well as the non-business days. We can then just count the number of rows where the date is greater than or equal to the start date and less than the end date.
DECLARE @sd as datetime = '2012-05-01'
DECLARE @ed as datetime = '2012-06-01'
SELECT COUNT(*) as BUSDAYS
FROM (
SELECT SERIESVALUE
FROM wctMath.wct.SERIESDATE(@sd,@ed,NULL,NULL,NULL)
WHERE DATEPART(dw,seriesvalue) <> 1 --Sunday
AND DATEPART(dw,seriesvalue) <> 7 --Saturday
EXCEPT
SELECT HOLIDAY
FROM HOLIDAYS
WHERE HOLIDAY BETWEEN @sd AND @ed
AND COUNTRY = 'US'
) m(d)
WHERE d >= @sd and d < @ed
This produces the following result.
BUSDAYS
-----------
22
We considered a variety of techniques for calculating the number of business days. We used a very simple test that the solution should be able to produce the following resultant table, showing the number of business days between all the dates in May of 2012, where 2012-05-28 is a holiday.
DECLARE @sd as datetime = '2012-05-01'
DECLARE @ed as datetime = '2012-05-31'
DECLARE @hol as varchar(max) =(SELECT wct.NBD(holiday) FROM HOLIDAYS WHERE COUNTRY = 'US')
SELECT sd
,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
,[31]
FROM(
SELECT DAY(k.SeriesValue) as sd
,DAY(l.SeriesValue) as ed
,wct.BUSDAYS(k.SeriesValue, l.SeriesValue, @hol) as bd
FROM wctMath.wct.SeriesDate(@sd,@ed,NULL,NULL,NULL) k
CROSS APPLY wctMath.wct.SeriesDate(@sd,@ed,NULL,NULL,NULL) l
) n
PIVOT(SUM(bd) for ed in(
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
,[31])) as P
This produces ths following result.
In other words, the calculation has to work backwards and forwards, and has to work for when the start date is equal to the end date. Additionally it had to be flexible enough to allow for different non-business days for each row in the resultant table.There are also some subtle little twists to consider when calculating the number of business days. For example, when you calculate the number of business days (assuming that there are no holidays) from Monday to Friday, the result is 4. If you change the end date to Saturday, the number is 5, which is also the same result that you would get if the end date were Sunday or even the following Monday.
This behavior is subtly different than what happens in the BUSINESSDATE function (see below). When we add 5 business days to Monday, it returns the following Monday, as it is the first business date that is 5 business day after the start date.
We tested the BUSDAYS function by randomly creating 1,000,000 start date and end date combinations and then programmitically counting the number of days between the start date and the end date.
We briefly considered making the BUSDAYS function a multi-input aggregate, but since one of the main ways that we were going to use the function was as part of an interest calculation, we were concerned that an aggregate would make the SQL too complex. We were also concerned about the fact that aggregate functions cannot be used inside other aggregate functions, like the SUM function. We opted to keep the function a scalar and keep things simple.
BUSINESSDATE
The BUSINESSDATE function calculates business dates forward or backward from a specified start date. You can also use the function to calculate a date that is weeks, months, or years from the specified start date and have the calculated date adjusted to a business date based upon a date roll rule parameter supplied to the function. The BUSINESSDATEWE function works just like the BUSINESSDATE function, but it lets you specify a weekend consisting of other than a Saturday and a Sunday.
There are many business situations where you need to calculate the next business date. For example, many securities trade on a t+1 basis. In other words, the securities settle on the next business day after the trade date. Some securities trade on t+3 basis. Many foreign exchange markets operate on a t+2 basis, but you need to consider the holiday calendar from 2 or possibly 3 countries. The BUSINESSDATE function is designed to perform these calculations in SQL Server.
Here’s a simple example. You buy a security on 2012-05-30 which regularly settles on a t+3 basis. You need calculate the settlement date incorporating the US holidays calendar. The following SQL statement will do the calculation for you.
SELECT wct.BUSINESSDATE(
'2012-05-30' --Start Date
,'D' --Date Part
,3 --Number
,NULL --Date Roll Rule
,wct.NBD(holiday) --Holidays
) as [t+3]
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
t+3
-----------------------
2012-06-04 00:00:00.000
If regular way settlement were 1 day, we would simply chage the number parameter to 1.
SELECT wct.BUSINESSDATE(
'2012-05-30' --Start Date
,'D' --Date Part
,1 --Number
,NULL --Date Roll Rule
,wct.NBD(holiday) --Holidays
) as [t+1]
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
t+1
-----------------------
2012-05-31 00:00:00.000
In this example we calculate the settlement date for USD/GBP spot FX transactions on 2012-05-31.
SELECT wct.BUSINESSDATE(
'2012-05-31' --Start Date
,'D' --Date Part
,2 --Number
,NULL --Date Roll Rule
,wct.NBD(holiday) --Holidays
) as [t+2]
FROM HOLIDAYS
WHERE COUNTRY IN('US','GB')
This produces the following result, reflecting the Spring Bank Holiday and the Diamond Jubilee Holidays in Great Britain.
t+2
-----------------------
2012-06-06 00:00:00.000
We can also use the BUSINESSDATE function in much the same way that we would use the DATEADD function, except that we can adjust the result to either the preceding or succeeding business date. Here’s a simple example where we will calculate the next business date (also known as the tom/next business date), the spot business date (t+2), and the 1 through 11 month dates (which are from the spot date) and the 1,2,5,10,20,and 30 year dates.
DECLARE @tod as datetime = '2012-05-29'
DECLARE @hol as varchar(max) =(SELECT wct.NBD(holiday) FROM HOLIDAYS WHERE COUNTRY = 'US')
SELECT n
,dp
,wct.BUSINESSDATE(wct.BUSINESSDATE(@tod,'D',2,NULL,@hol),dp,n,'M',@hol) as [date]
FROM (VALUES
(1,'M'),(2,'M'),(3,'M'),(4,'M'),(5,'M'),(6,'M'),(7,'M'),(8,'M'),(9,'M'),(10,'M'),(11,'M')
,(1,'Y'),(2,'Y'),(5,'Y'),(10,'Y'),(20,'Y'),(30,'Y')
) x(n,dp)
UNION
SELECT 1,'D',wct.BUSINESSDATE(@tod,'D',1,NULL,@hol)
UNION
SELECT 2,'D',wct.BUSINESSDATE(@tod,'D',2,NULL,@hol)
ORDER BY 3
This produces the following result.
n dp date
----------- ---- -----------------------
1 D 2012-05-30 00:00:00.000
2 D 2012-05-31 00:00:00.000
1 M 2012-06-29 00:00:00.000
2 M 2012-07-31 00:00:00.000
3 M 2012-08-31 00:00:00.000
4 M 2012-09-28 00:00:00.000
5 M 2012-10-31 00:00:00.000
6 M 2012-11-30 00:00:00.000
7 M 2012-12-31 00:00:00.000
8 M 2013-01-31 00:00:00.000
9 M 2013-02-28 00:00:00.000
10 M 2013-03-29 00:00:00.000
11 M 2013-04-30 00:00:00.000
1 Y 2013-05-31 00:00:00.000
2 Y 2014-05-30 00:00:00.000
5 Y 2017-05-31 00:00:00.000
10 Y 2022-05-31 00:00:00.000
20 Y 2032-05-31 00:00:00.000
30 Y 2042-05-30 00:00:00.000
Notice that the Monthly and Yearly dates are calculated from the spot date and that the date roll rule is ‘M’. The M (for modified) means that if the calculated date is a non-business date, then roll the date to the next business date, unless the new date is a in a different month, in which case roll the date to the preceding business date. What would the calculation have been using the DATEADD function?
It’s harder to find that out then you would otherwise think, because the DATEADD function does not permit the datepart variable to be passed in as a variable. The on-line documentation clearly states, “User-defined variable equivalents are not valid.” That certainly makes it hard to use in any kind of a set operation, especially for many typical financial calculations. We can use a CASE statement to overcome this limitation.
DECLARE @tod as datetime = '2012-05-29'
DECLARE @hol as varchar(max) =(SELECT wct.NBD(holiday) FROM HOLIDAYS WHERE COUNTRY = 'US')
SELECT n
,dp
,wct.BUSINESSDATE(wct.BUSINESSDATE(@tod,'D',2,NULL,@hol),dp,n,'M',@hol) as [date]
,CASE dp
WHEN 'M' THEN DATEADD(month, n, wct.BUSINESSDATE(@tod,'D',2,NULL,@hol))
WHEN 'Y' THEN DATEADD(year, n, wct.BUSINESSDATE(@tod,'D',2,NULL,@hol))
END as [DATEADD]
FROM (VALUES
(1,'M'),(2,'M'),(3,'M'),(4,'M'),(5,'M'),(6,'M'),(7,'M'),(8,'M'),(9,'M'),(10,'M'),(11,'M')
,(1,'Y'),(2,'Y'),(5,'Y'),(10,'Y'),(20,'Y'),(30,'Y')
) x(n,dp)
UNION
SELECT 1,'D',wct.BUSINESSDATE(@tod,'D',1,NULL,@hol), DATEADD(day,1,@tod)
UNION
SELECT 2,'D',wct.BUSINESSDATE(@tod,'D',2,NULL,@hol), DATEADD(day,2,@tod)
ORDER BY 3
This produces the following result.
n dp date DATEADD
----------- ---- ----------------------- -----------------------
1 D 2012-05-30 00:00:00.000 2012-05-30 00:00:00.000
2 D 2012-05-31 00:00:00.000 2012-05-31 00:00:00.000
1 M 2012-06-29 00:00:00.000 2012-06-30 00:00:00.000
2 M 2012-07-31 00:00:00.000 2012-07-31 00:00:00.000
3 M 2012-08-31 00:00:00.000 2012-08-31 00:00:00.000
4 M 2012-09-28 00:00:00.000 2012-09-30 00:00:00.000
5 M 2012-10-31 00:00:00.000 2012-10-31 00:00:00.000
6 M 2012-11-30 00:00:00.000 2012-11-30 00:00:00.000
7 M 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
8 M 2013-01-31 00:00:00.000 2013-01-31 00:00:00.000
9 M 2013-02-28 00:00:00.000 2013-02-28 00:00:00.000
10 M 2013-03-29 00:00:00.000 2013-03-31 00:00:00.000
11 M 2013-04-30 00:00:00.000 2013-04-30 00:00:00.000
1 Y 2013-05-31 00:00:00.000 2013-05-31 00:00:00.000
2 Y 2014-05-30 00:00:00.000 2014-05-31 00:00:00.000
5 Y 2017-05-31 00:00:00.000 2017-05-31 00:00:00.000
10 Y 2022-05-31 00:00:00.000 2022-05-31 00:00:00.000
20 Y 2032-05-31 00:00:00.000 2032-05-31 00:00:00.000
30 Y 2042-05-30 00:00:00.000 2042-05-31 00:00:00.000
I have highlighted the differences.
There are some interesting subtleties with the BUSINESSDATE function. For example, what if you want to go 1 business day from a Saturday?
SELECT wct.BUSINESSDATE(
'2012-06-02' --Start Date
,'D' --Date Part
,1 --Number
,NULL --Date Roll Rule
,wct.NBD(holiday) --Holidays
)
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
-----------------------
2012-06-04 00:00:00.000
But, if we calculate the number of business days from 2012-06-02 to 2012-06-04, we will get zero. We could also use the following statement to get the next business day.
SELECT wct.BUSINESSDATE(
'2012-06-02' --Start Date
,'M' --Date Part
,0 --Number
,'F' --Date Roll Rule
,wct.NBD(holiday) --Holidays
)
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This calculates zero months from the start and uses the F (Following) date roll rule, which advances the calculated date to the next business date.
DAYS360
The DAYS360 functions calculates the number of days between a start date and an end date using any one of three 30/360 date functions. The 30/360 date conventions assumes that a year contains 12 30-day months. 30/360 date conventions are quite common in fixed income calculations but may also be relevant for other types of financial calculations. As in all the date functions that we are discussing, the number of days calculated includes the start date and not the end date in the calculation. In other words, the number of days from 29-May-2012 to 30-May-2012 is calculated as one day, not two.
The XLeratorDB DAYS360 function is slightly different then the EXCEL function of the same name, as the EXCEL function only supports two methods and produces some results that are hard to reconcile. For example, if you enter the following information in a spreadsheet:
You get the following result:
which is hard to see as anything other than nonsensical. In XLeratorDB, we can enter
SELECT wct.DAYS360('2012-02-29','2012-02-29', 0) as DAYS360
producing the following result.
DAYS360
-----------
0
EXCEL also supports a method parameter. According to the EXCEL documentation, method can either be FALSE or omitted, specifying the US (NASD) method, or it can be TRUE, which specifies the European method, and then gives a definition. However, there are two European methods, which have a variety of names. These are the 30E/360 (Eurobond) or Special German method and the 30E/360 (ISDA) or German method. It looks like what EXCEL supports is the 30/E360 (Eurobond) convention also known as the Special German method. XLeratorDB supports all three methods: US; German; and Special German.
One interesting aspect of the DAYS360 calculations, in both XLeratorDB and EXCEL, is that the calculations do not produce the same results going from start date to end date and from end date to start date when using the US method.
SELECT *
FROM (
SELECT s as [Start]
,e as [End]
,wct.DAYS360(s,e,0) as [fwd]
,-wct.DAYS360(e,s,0) as [bkwd]
FROM (VALUES
('2007-01-15','2007-01-30'),
('2007-01-15','2007-02-15'),
('2007-01-15','2007-07-15'),
('2007-09-30','2008-03-31'),
('2007-09-30','2007-10-31'),
('2007-09-30','2008-09-30'),
('2007-01-15','2007-01-31'),
('2007-01-31','2007-02-28'),
('2007-02-28','2007-03-31'),
('2006-08-31','2007-02-28'),
('2007-02-28','2007-08-31'),
('2007-02-14','2007-02-28'),
('2007-02-26','2008-02-29'),
('2008-02-29','2009-02-28'),
('2008-02-29','2008-03-30'),
('2008-02-29','2008-03-31'),
('2007-02-28','2007-03-05'),
('2007-10-31','2007-11-28'),
('2007-08-31','2008-02-29'),
('2008-02-29','2008-08-31'),
('2008-08-31','2009-02-28'),
('2009-02-28','2009-08-31')
) n(s,e)
) m
WHERE fwd <> bkwd
This produces the following result.
Start End fwd bkwd
---------- ---------- ----------- -----------
2007-01-15 2007-01-31 16 15
2007-01-31 2007-02-28 28 30
2007-02-28 2007-03-31 30 32
2006-08-31 2007-02-28 178 180
2007-02-28 2007-08-31 180 182
2007-02-14 2007-02-28 14 16
2007-02-26 2008-02-29 363 364
2008-02-29 2008-03-30 30 31
2008-02-29 2008-03-31 30 31
2007-02-28 2007-03-05 5 7
2007-10-31 2007-11-28 28 27
2007-08-31 2008-02-29 179 180
2008-02-29 2008-08-31 180 181
2008-08-31 2009-02-28 178 180
2009-02-28 2009-08-31 180 182
The DAYS360 function is most often used in the calculation of accrued interest. Generally the accrued interest calculation looks like:
F * R * DAYS360(start, end, method) / 360
Where F is the face amount and R is the rate of interest.
DAYSNL
Like the DAYS360 function, the DAYSNL function calculates the number of days between a start date (inclusive) and an end date (exclusive), but does not count any occurrence of Feb 29th. Effectively, the DAYSNL function makes every year exactly 365 days (the NL in the function name standing for No Leap-years). As with the DAYS360 frunction, the DAYSNL function is used in some fixed income calculations. Here’s an example of the calculation:
SELECT wct.DAYSNL('2011-02-28','2013-02-28') as DAYSNL
This produces the following result.
DAYSNL
-----------
730
While the function ignores instances of Feb-29th, it does not prohibit you from passing Feb-29th into the function as either the start date or the end date. In the following example, we show 3 different ways of breaking up the 2-year period in the previous example, and the results returned by the function.
SELECT *
,wct.DAYSNL(d1,d3) as [Total]
,wct.DAYSNL(d1,d2) as [Year 1]
,wct.DAYSNL(d2,d3) as [Year 2]
FROM (VALUES
('2011-02-28','2012-02-28','2013-02-28'),
('2011-02-28','2012-02-29','2013-02-28'),
('2011-02-28','2012-03-01','2013-02-28')
) as n(d1,d2,d3)
This produces the following result.
d1 d2 d3 Total Year 1 Year 2
---------- ---------- ---------- ----------- ----------- -----------
2011-02-28 2012-02-28 2013-02-28 730 365 365
2011-02-28 2012-02-29 2013-02-28 730 365 364
2011-02-28 2012-03-01 2013-02-28 730 366 364
As you can see in the second row, Year 1 was calculated as 365 days and year 2 was caclulated as 364 days, which is not equal to the total of 730 days for the 2-year period. This happens because the Year 2 calculation is using 2012-02-29 as the start date and the function excludes all instances of the Feb 29th. This ends up being the same as passing 2012-03-01 as the start date, leaving a 1-day gap between 2012-03-01 and 2012-02-28.
All 5 of these date functions are part of XLeratorDB / finance and XLeratorDB / finance 2008 release 1.09. We think that these function can simplify a great many of your financial calculations, allowing you to do very sophisticated date calculations directly on the database, which is generally where you will want the results persisted. This means no network traffic and potentially big improvements in perfromance for large datasets and sophisticated calculations. Let us know what you think.