1

I am getting different results between my stored procedure and my UDF.

The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, then replaces the NULLs with '00:00:00' so that I can sum the amounts. Then takes that date, and does a DATEDIFF on it to give me the total hours worked.

If I input the values ('John Doe', '04/05/2013', '04/16/2013'), the stored procedure is returning the answer I am looking for, the SVF is not. Where the stored procedure would result in "23" for hours worked, the SVF returns "1900-01-24 00:00:00".

I realize that it is with what I am returning in the SVF, but I can't figure it out. I have tried to return TIME(0), VARCHAR(5). It is as if the SVF isn't doing the DATEDIFF bit.

I am working with SQL Server 2012 (11.0.3000)

Below is the SVF and the stored procedure.

The SVF

ALTER FUNCTION [dbo].[fnFindHoursWorked] 
    (@Name varchar(50)
     ,@Start_Date DATE = NULL
     ,@End_Date DATE = NULL)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @HoursWorked DATETIME

    -- Add the T-SQL statements to compute the return value here
    /** create a table variable to store the results of the query **/
    DECLARE @Duty TABLE (Time_on_Duty TIME(0))

    INSERT INTO @Duty(Time_on_Duty)

    /** The actual query that grabs the data from the  server **/
    SELECT   
        CAST(UL.[Date_Time] - LAG(UL.[Date_Time], 1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME(0)) AS 'Time On Duty'
    FROM [LinkedServer].[database].dbo.[tablename] AS UL
    WHERE UL.[Department] = 'Department'
      AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
      AND (UL.[Name] = @Name)
      AND ((CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
            )

    /** Setting all of the NULLS in the table variable to a value on which we can do math **/
    UPDATE @Duty 
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    /** The select statement to grab the total hours worked for the date range **/
    SET @HoursWorked = (SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty' FROM @Duty)

    -- Return the result of the function
    RETURN @HoursWorked
END

Here's the stored procedure:

ALTER PROCEDURE [dbo].[rptTotal_Time_On_duty] 
    @Name varchar(50)
    ,@Start_Date DATE = NULL
    ,@End_Date DATE = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID (N'tempdb..#Duty', N'U') IS NOT NULL
       TRUNCATE TABLE #Duty
    ELSE
       CREATE TABLE #Duty (Time_on_Duty TIME)

    INSERT INTO #Duty(Time_on_Duty)
        SELECT   
            CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On Duty'
        FROM [LinkedServer].[database].dbo.[tablename] AS UL
        WHERE UL.[Department] = 'Department'
          AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
          AND (UL.[Name] = @Name)
          AND (
           (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
        )

    UPDATE #Duty 
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty'
    FROM #Duty

END
  • What happens if you change the proc to use TIME(0) instead of just TIME in the temp table and select cast(.. as ..) ? – RoKa Apr 18 '13 at 12:46
  • I haven't tried messing with the sproc, since that works as I want. However I have done that in the SVF, it doesn't work. FLOAT doesn't like to be cast as a TIME – RateControl Apr 18 '13 at 12:50
  • The reason I ask, is because TIME and TIME(0) are different in terms of precision and number of bytes. If the precision is different, your problem may be caused by rounded values – RoKa Apr 18 '13 at 13:11
  • Yup! Totally, I don't want the ms or ns. I had thought that it is a rounding issue and the engine doesn't compute... – RateControl Apr 18 '13 at 13:16
  • What is preventing you from changing your function to use TIME? – RoKa Apr 18 '13 at 13:20
3

DATEDIFF return the result in int. Change the @HoursWorked data type in your scalar value function to int instead of datetime.

  • That was so obvious, I should slap myself. – RoKa Apr 18 '13 at 14:27
  • Hot damn, knew it was something silly! Thanks a bunch! – RateControl Apr 18 '13 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.