SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate Date Difference Excluding Bank Hols
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LornaDooley
Starting Member

3 Posts

Posted - 08/28/2013 :  06:41:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 08/28/2013 :  08:35:49  Show Profile  Reply with Quote
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 +"
Go to Top of Page

LornaDooley
Starting Member

3 Posts

Posted - 08/28/2013 :  11:31:42  Show Profile  Reply with Quote
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 +"

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 08/28/2013 :  11:43:44  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/28/2013 :  12:13:59  Show Profile  Reply with Quote
Another option might be to create a Calendar table, then you can join to it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000