Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Issues with DateTime

    I am making a new Stored Procedure...

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE dbo.DateDifferenceBusinessDays

    /* Parameters */

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

    AS
    BEGIN

    /* Procedure body */

    DECLARE @DayToAdd Integer,
    @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))
    SET @StartDate = DATEADD(day,@DayToAdd,@StartDate)
    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,
    @DayToAdd Integer,
    @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))
    SET @StartDate = DATEADD(day,@DayToAdd,@StartDate)
    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. #2
    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

    CREATE PROCEDURE dbo.DateDifferenceBusinessDays

    /* Parameters */

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

    AS
    BEGIN

    /* Procedure body */

    DECLARE @DayToAdd Integer,
    @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 @StartDate = DATEADD(day,@DayToAdd,@StartDate)
    SET @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))

    -- Add Hours to days
    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. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The return value of a stored procedure is always an integer.

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

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Quote Originally Posted by Pat Phelan View Post
    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

    CREATE FUNCTION dbo.DateDiffBusinessDays(
    /* Paramters */
    @StartDate DATETIME,
    @EndDate DATETIME
    )

    RETURNS Float
    AS
    BEGIN
    /* Function body */

    DECLARE @DayToAdd Integer,
    @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 @StartDate = DATEADD(day,@DayToAdd,@StartDate)
    SET @NumberOfHours = (DATEDIFF(hour, @StartDate, @EndDate))

    -- Add Hours to days
    SET @NumberOfDays = @NumberOfDays + (@NumberOfHours/60)

    Return @NumberOfDays
    --Select @NumberOfDays
    END

    because I can specify returns as float.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    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,
    julian_business_nbr INTEGER NOT NULL,
    ...);

    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:

    SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
    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. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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;
    George
    Home | Blog

Posting Permissions

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