Calculate Date Difference Excluding Bank Hols

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Date Difference Excluding Bank Hols

Author

Topic

LornaDooley
Starting Member

3 Posts

Posted - 2013-08-28 : 06:41:27

Found this previous post and it worked a treat! I have a Bank holiday table (ID column, and Date column), I now need advise of how to to exclude bank holidays in the datediff returned. Can anyone help please?

quote:
--------------------------------------------------------------------------------
Originally posted by senthil_nagore

Much Simple function as your requirement

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count

END

Select dbo.days_diff('04/09/2010','04/12/2010') as diff

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 08:35:49

[code]SELECT
1 + DATEDIFF(DAY, @date1, @date2) -
SUM(CASE WHEN bh.Date >= @Date1 AND bh.Date <= @date2 THEN 1 ELSE 0 END) AS days_diff
FROM
YourBankHolidayTable bh;[/code]This is inclusive of both days. If you don't want that, remove the "1 +"

LornaDooley
Starting Member

3 Posts

Posted - 2013-08-28 : 11:31:42

Thank you James K for your prompt reply. I updated my UDF applying your SUM idea to a BankHolCount variable, then deducted the value from my Return value that I was passing back - Test successful!

quote:
Originally posted by James K

SELECT
1 + DATEDIFF(DAY, @date1, @date2) -
SUM(CASE WHEN bh.Date >= @Date1 AND bh.Date <= @date2 THEN 1 ELSE 0 END) AS days_diff
FROM
YourBankHolidayTable bh;
This is inclusive of both days. If you don't want that, remove the "1 +"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 11:43:44

You are quite welcome.

In many cases, it might be more efficient to include the code directly into your query than making it into a UDF. So if performance becomes an issue, consider doing that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-28 : 12:13:59

Another option might be to create a Calendar table, then you can join to it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources