Note: The post is the same as the previous one for Oracle but this is for SQL Server. Although the date arithmetic gets a little complicated by the SQL Server syntax, the concept is the same.
If you search the web on how to calculate the number of business days between two dates in SQL Server, you would see so many results. Here is one more with some explanation to go with it.
Example requirement
Let us say that this is the business requirement – “Today, I am calculating the number for days between two dates to get the response time in days but I would really like to exclude the weekends to get an accurate picture”.
I am going to simulate this business scenario below where it shows an activity’s start and end date
WITH response_time AS ( SELECT GETDATE() - 600 AS start_date, GETDATE() - 500 AS end_date ) SELECT a.*, DATEDIFF(day, a.start_date, a.end_date) AS reponse_time_in_days FROM response_time a
The results are simple enough:
START_DATE | END_DATE | REPONSE_TIME_IN_DAYS |
7/1/2012 15:15 | 10/9/2012 15:15 | 100 |
Excluding “weekends”
Now, how do we take out the “weekend days” out from the “RESPONSE_TIME_IN_DAYS”?
- First, we need to know all the dates in between the start and end dates.
- Then, we need to determine which dates among those are either a Saturday or a Sunday
- Finally, we count the resulting dates
Let us go about doing this. Initially, we are going to use a brute force approach and generate all “days” after 1/1/1950 for 100 years (roughly till Dec, 2050) assuming our start and end dates will fall within that range (we will refine the solution later). This article at SQLPerformance.com shows how to generate n rows dynamically and also explains why “MAXDOP 1” was used.
SELECT DATEADD(day, (seq-1), CONVERT(datetime, '1950/01/01 00:00:00', 120) ) AS day_date, DATEPART(dw, DATEADD(day, (seq-1), CONVERT(datetime, '1950/01/01 00:00:00', 120) ) ) AS day_of_week FROM ( --100 years SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ) AS a ORDER BY 1 OPTION (MAXDOP 1)
This query is literally going to generate 100 years’ worth of dates, one per day from 1/1/1950
DAY_DATE | DAY_OF_WEEK |
1-Jan-1950 | 1 |
2-Jan-1950 | 2 |
3-Jan-1950 | 3 |
4-Jan-1950 | 4 |
5-Jan-1950 | 5 |
6-Jan-1950 | 6 |
7-Jan-1950 | 7 |
…..more rows inbetween | |
3-Dec-2049 | 6 |
4-Dec-2049 | 7 |
5-Dec-2049 | 1 |
6-Dec-2049 | 2 |
Day 1 and 7 make the weekend
In the table above, DAY_OF_WEEK 7 and 1 correspond to Saturday and Sunday respectively (whose corresponding dates, we want to exclude). More specifically, the numbering is as follows:
Day of Week | Day |
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
Exclude the weekends
Below, I have put the above pieces together
WITH days AS ( SELECT DATEADD(day, (seq-1), CONVERT(datetime, '1950/01/01 00:00:00', 120) ) AS day_date, DATEPART(dw, DATEADD(day, (seq-1), CONVERT(datetime, '1950/01/01 00:00:00', 120) ) ) AS day_of_week FROM ( --100 years SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ) AS a ), response_time AS ( SELECT GETDATE() - 600 AS start_date, GETDATE() - 500 AS end_date ) SELECT MAX(DATEDIFF(day, start_date, end_date)) AS days_inbetween, COUNT(1) business_days_inbetween FROM response_time resp, days WHERE --------------------------------------------------- --Get only the dates between our start and end dates --------------------------------------------------- days.day_date >= resp.start_date AND days.day_date <= resp.end_date --------------------------------------------------- --...then exclude Sat and Sun --------------------------------------------------- AND days.day_of_week NOT IN (7,1) OPTION (MAXDOP 1)
And the results are as follows:
DAYS_INBETWEEN | BUSINESS_DAYS_INBETWEEN |
100 | 72 |
Exclude holidays too, please!
That was easy enough. Different countries and regions have different holidays. It is not a good idea to hard-code the list of holidays in your query. Now, if we had a calendar table which had the holidays, just add that in to the end of the query (note the highlighted part). The Holidays_Table could have just three columns Region, HolidayDate which is a date and a Description to describe the holiday.
WHERE --------------------------------------------------- --Get only the dates between our start and end dates --------------------------------------------------- days.day_date >= resp.start_date AND days.day_date <= resp.end_date --------------------------------------------------- --...then exclude Sat and Sun --------------------------------------------------- AND days.day_of_week NOT IN (7,1) --------------------------------------------------- --...then exclude week-day holidays --------------------------------------------------- AND NOT EXISTS ( SELECT holiday FROM holidays_table ht WHERE ht.holiday = days.day_date --Optionally include Region! )
Need a more efficient solution?
While this works, it can certainly be tweaked a bit more. We do not need to generate 100 years’ worth of dates – we need to generate exactly the dates in between the start date and end date.
WITH days AS ( SELECT DATEADD(day, (seq-1), @Begin_Date) AS day_date, DATEPART(dw, DATEADD(day, (seq-1), @Begin_Date)) day_of_week FROM ( --Only the x days between begin and end dates SELECT TOP (@Days_Between) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ) a ) ….rest of the SQL
Where the variables could be (for example)
DECLARE @Begin_Date DATETIME DECLARE @End_Date DATETIME DECLARE @Days_Between INT SET @Begin_Date = CONVERT(datetime, '1971/01/01 00:00:00', 120) SET @End_Date = CONVERT(datetime, '1981/01/01 00:00:00', 120) SET @Days_Between = DATEDIFF(day, @Begin_Date, @End_Date)
Notice the highlighted variable substitutions and you will see that we have actually made the day generator SQL simpler and more efficient too.
Hi , This is a fine solution really thank you. but could you please suggest how to use the same solution to return only business hours 8-5pm
Hi Santosh, you mean you want the number of business hours between two dates? The simplest but inaccurate way is to multiply the result by 8. To get the accurate business hours in between the start time and the end time on the begin and end dates should be taken into account.
I tried this code but it is not excluding holidays in my holiday table. My holiday column is a date time column.
SET @Begin_Date = CONVERT(datetime, ‘2020/07/27 00:00:00’, 120);
SET @End_Date = CONVERT(datetime, ‘2020/08/5 00:00:00’, 120);
the holiday is 2020-08-03 22:30:20.000
returns
days_inbetween:9 business_days_inbetween:7
should return days_inbetween:9 business_days_inbetween:6
because monday, august 3, 2020 is a holiday.
i used the following code in regards to my comment above:
SET @Begin_Date = ‘2020/07/27’;
SET @End_Date = ‘2020/08/5’;
SET @Days_Between = DATEDIFF(day, @Begin_Date, @End_Date);
WITH days
AS
(
SELECT
DATEADD(day, (seq-1), @Begin_Date) AS day_date,
DATEPART(dw, DATEADD(day, (seq-1), @Begin_Date)) day_of_week
FROM
(
–Only the x days between begin and end dates
SELECT TOP (@Days_Between) seq
= CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) a
),
response_time
AS
(
SELECT
@Begin_Date AS START_DATE,
–GETDATE() – 30 AS start_date,
@End_Date AS END_dATE
–GETDATE() -22 AS end_date
)
SELECT
MAX(DATEDIFF(day, start_date, end_date)) AS days_inbetween,
COUNT(1) business_days_inbetween
FROM
response_time resp,
days
WHERE
—————————————————
–Get only the dates between our start and end dates
—————————————————
days.day_date >= resp.start_date
AND days.day_date <= resp.end_date
—————————————————
–…then exclude Sat and Sun
—————————————————
AND days.day_of_week NOT IN (7,1)
—————————————————
–…then exclude week-day holidays
—————————————————
AND NOT EXISTS
(
SELECT convert(date,HOL_DATE)
FROM holidays h
WHERE h.hol_DATE = days.day_date
)
OPTION (MAXDOP 1)
Can you please try after declaring @Begin_Date and @End_Date ad DATE instead of DATETIME? I am pretty sure that the time component is messing it up.
DECLARE @Begin_Date DATE = ‘2020/07/27’;
DECLARE @End_Date DATE = ‘2020/08/05’;