February 7, 2013 at 9:07 pm
Comments posted to this topic are about the item Number of Working Days
February 8, 2013 at 5:38 pm
Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.
CREATE FUNCTION [dbo].[CalculateBusinessDays]
(
@StartDate DATE,
@EndDate DATE
)
RETURNS INTEGER
AS
BEGIN
IF @EndDate IS NULL
SELECT @EndDate = GETDATE()
RETURN CONVERT(INTEGER,
(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)
- ISNULL((SELECT COUNT(*) FROM tblHoliDays
WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))
END
February 9, 2013 at 8:48 am
Dave Vroman (2/8/2013)
Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.
CREATE FUNCTION [dbo].[CalculateBusinessDays](
@StartDate DATE,
@EndDate DATE
)
RETURNS INTEGER
AS
BEGIN
IF @EndDate IS NULL
SELECT @EndDate = GETDATE()
RETURN CONVERT(INTEGER,
(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)
- ISNULL((SELECT COUNT(*) FROM tblHoliDays
WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))
END
What sorts of errors are you getting when you try to run the function?
February 11, 2013 at 11:41 am
Your answer told me that I was having problems with the browser (Chrome). Tried it with Firefox - works well.
May 2, 2016 at 4:06 pm
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply