Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am getting different results between my stored proc and my SVF.

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 proc is returning the answer I am looking for, the SVF is not. Where the sproc 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 sproc.

The SVF

 ALTER FUNCTION [dbo].[fnFindHoursWorked] 
(
    -- Add the parameters for the function here
@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 proc:

ALTER PROCEDURE [dbo].[rptTotal_Time_On_duty] 
    -- Add the parameters for the stored procedure here
@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
share|improve this question
    
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

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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