Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Number of Working Days Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 9:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 1:46 PM
Points: 231, Visits: 428
Comments posted to this topic are about the item Number of Working Days
Post #1417450
Posted Friday, February 8, 2013 5:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 21, 2016 11:48 PM
Points: 151, Visits: 713
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

Post #1417961
Posted Saturday, February 9, 2013 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 1:46 PM
Points: 231, Visits: 428
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?
Post #1418025
Posted Monday, February 11, 2013 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 21, 2016 11:48 PM
Points: 151, Visits: 713
Your answer told me that I was having problems with the browser (Chrome). Tried it with Firefox - works well.
Post #1418587
Posted Monday, May 2, 2016 4:06 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 26, 2016 7:20 AM
Points: 7,816, Visits: 753
Thanks for the script.
Post #1782731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse