I'm looking for a way to calculate the days between two dates, but on weekdays. Here is the formula, but it counts weekend days.
DATEDIFF(DAY,STARTDATE,ENDDATE)
SELECT DATEDIFF(DAY,'2015/06/01' , '2015/06/30')
Result of above query of datediff is 29 days which are weekend days. but i need week days that should be 21 by removing Saturday and Sunday(8 days). Any suggestions?
Put it in the WHERE clause
SELECT DATEDIFF(DAY,'2015/06/01' , '2015/06/30')
FROM yourtable
WHERE DATENAME(dw, StartDate) != 'Saturday'
AND DATENAME(dw, StartDate) != 'Sunday'
Or all in a SELECT statement
SELECT (DATEDIFF(dd, StartDate, EndDate) + 1)-(DATEDIFF(wk, StartDate, EndDate) * 2)-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END)
This returns 22:
DECLARE @StartDate AS DATE = '20150601'
DECLARE @EndDate AS DATE = '20150630'
SELECT
(DATEDIFF(DAY, @StartDate, @EndDate))
-(DATEDIFF(WEEK, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
Read this article by Jeff Moden for more information.
Explanation:
First, (DATEDIFF(DAY, @StartDate, @EndDate)) will return the difference in number of days. In this case, it'll be 29. Now, depending on your interpretation of whole days, you may want to add 1 day to its result.
Next,(DATEDIFF(WEEK, @StartDate, @EndDate) * 2):
To quote the article:
DATEDIFFfor theWEEKdatepart doesn't actually calculate weeks, it calculates the number of times a date range contains dates that represent pairs of Saturdays and Sundays. To think of it in more simple terms, it only counts WHOLE WEEKENDS!
So, to exclude the weekends, you must subtract twice the result of this from the first DATEDIFF. Which now will be: 29 - (2 *4) = 21.
Finally, this:
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
removes the partial weeks, which only happens when then @StartDate occurs on a Sunday and the @EndDate occurs on a Saturday.
You can try recursive cte:
WITH cte
AS ( SELECT CAST('2015/06/01' AS DATE) AS dt ,
DATEPART(WEEKDAY, '2015/06/01') AS wd
UNION ALL
SELECT DATEADD(d, 1, dt) AS dt ,
DATEPART(WEEKDAY, DATEADD(d, 1, dt))
FROM cte
WHERE dt < '2015/06/30'
)
SELECT COUNT(*)
FROM cte
WHERE wd NOT IN ( 7, 1 )
Result is 22. You better add some useful calendar table that every database should have. Fill it with some big range and then use it to calculate business days.
I wrote a simple stored procedure - don't know if a SP is what you need but i think you can easily convert it to a function , TVF, whatever:
CREATE PROCEDURE [dbo].[BusinessdaysBetween](@DateFrom DATE, @DateTo DATE, @days INT OUTPUT)
AS
BEGIN
DECLARE @datefirst INT = @@DATEFIRST
SET DATEFIRST 1 --so week starts on monday
SET @days = 0
IF @DateFrom > @DateTo
RETURN NULL
IF @DateFrom = @DateTo
RETURN @days
WHILE @DateFrom <= @DateTo
BEGIN
IF DATEPART(WEEKDAY,@DateFrom) NOT IN (6,7) --Saturday or Sunday
BEGIN
SET @days = @days + 1
END
SET @DateFrom = DATEADD(DAY,1,@DateFrom)
END
SET DATEFIRST @datefirst --restore original setup
END
GO
in my example i call i it like this:
DECLARE @days INT = 0
DECLARE @datefrom DATETIME = GETDATE()
DECLARE @dateto DATETIME = DATEADD(DAY,25,GETDATE())
EXEC dbo.BusinessdaysBetween @datefrom, @dateto, @days OUTPUT
SELECT @days
