1. Registered User
Join Date
Apr 2004
Location
Arizona
Posts
75

I am making a new Stored Procedure...

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* Parameters */

@StartDate DATETIME,
@EndDate DATETIME,
@NumberOfDays Float = 0 OUTPUT

AS
BEGIN

/* Procedure body */

@NumberOfHours Float

-- Calc days minus holidays & weekends
SELECT @NumberOfDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (Select Count(*) From Holidays Where HolidayDate Between @StartDate and @EndDate)

-- Remove days to calc hours
SELECT @DayToAdd = (DATEDIFF(dd, @StartDate, @EndDate))
SELECT @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))
SET @NumberOfDays = @NumberOfDays + (@NumberOfHours/60)

Return @NumberOfDays
END

This will calculate the number of Business Days and Hours in decimal. When I run it as T-SQL I get:

DECLARE @StartDate DATETIME = '4/15/2016 07:00:00 AM',
@EndDate DATETIME = '4/22/2016 08:00:00 AM',
@NumberOfDays Float = 0,
@NumberOfHours Float
-- Calc days minus holidays & weekends
SELECT @NumberOfDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (Select Count(*) From Holidays Where HolidayDate Between @StartDate and @EndDate)
-- Remove days to calc hours
SELECT @DayToAdd = (DATEDIFF(dd, @StartDate, @EndDate))
SELECT @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))
SET @NumberOfDays = @NumberOfDays + (@NumberOfHours/60)
Print @NumberOfDays

Output is : 6.01667

Which is right but when I run it as a stored procedure:

DECLARE @NumberofDays Float = 0
Exec @NumberofDays = DateDifferenceBusinessDays '4/15/2016 07:00:00 AM','4/22/2016 08:00:00 AM'
Print @NumberofDays

Output is : 6

Why is it not calculating hours right?

2. Registered User
Join Date
Apr 2004
Location
Arizona
Posts
75
I tried to get rid of the Selects and replace them with Sets and it made no difference. I also did this as a test...

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* Parameters */

@StartDate DATETIME,
@EndDate DATETIME,
@NumberOfDays Float = 0 OUTPUT

AS
BEGIN

/* Procedure body */

@NumberOfHours Float

-- Calc days minus holidays & weekends
SET @NumberOfDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (Select Count(*) From Holidays Where HolidayDate Between @StartDate and @EndDate)

-- Remove days to calc hours
SET @DayToAdd = (DATEDIFF(dd, @StartDate, @EndDate))
SET @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))

SET @NumberOfDays = @NumberOfDays + (@NumberOfHours/60)

--Return @NumberOfDays
Select @NumberOfDays as NumDays
END

And it returned the right answer through the select. It is the return that seems to only want to return an integer and not the float.

I have no idea why this is not working.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
The return value of a stored procedure is always an integer.

I would suggest an OUTPUT parameter as one way to achieve this.

-PatP

4. Registered User
Join Date
Apr 2004
Location
Arizona
Posts
75
Originally Posted by Pat Phelan
The return value of a stored procedure is always an integer.

I would suggest an OUTPUT parameter as one way to achieve this.

-PatP
I gave in and made it a UDF...

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* Paramters */
@StartDate DATETIME,
@EndDate DATETIME
)

RETURNS Float
AS
BEGIN
/* Function body */

@NumberOfHours Float,
@NumberOfDays Float

-- Calc days minus holidays & weekends
SET @NumberOfDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (Select Count(*) From Holidays Where HolidayDate Between @StartDate and @EndDate)

-- Remove days to calc hours
SET @DayToAdd = (DATEDIFF(dd, @StartDate, @EndDate))
SET @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))

SET @NumberOfDays = @NumberOfDays + (@NumberOfHours/60)

Return @NumberOfDays
--Select @NumberOfDays
END

because I can specify returns as float.

5. Registered User
Join Date
Jan 2013
Posts
355

## wrong approach

The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a long weekend or company holiday.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
...);

INSERT INTO Calendar
VALUES ('2007-04-05', 42),
('2007-04-06', 43), -- good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44),
('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next Tuesday:

FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2.cal_date = '2007-04-10';

Your real problem is that you are still thinking in procedural coding. SQL is a declarative language, and calendars are irregular.

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Pat's right... you are mis-using the RETURN statement (https://msdn.microsoft.com/en-us/library/ms174998.aspx).

Use the OUTPUT clause (you were nearly there!)
Code:
```CREATE PROCEDURE dbo.fun_with_output_parameters (
@normal_paramter  INT
, @output_parameter DECIMAL(15,4) = 1.2345 OUTPUT
)
AS
BEGIN
SET @output_parameter = 2.3456;
END
;
GO

DECLARE @output_value DECIMAL(15,4) = 3.4567;

SELECT @output_value AS output_value_before;

EXEC dbo.fun_with_output_parameters
@normal_paramter  = 7
, @output_parameter = @output_value OUTPUT
;

SELECT @output_value AS output_value_after;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•