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
TIME(0)
instead of justTIME
in the temp table andselect cast(.. as ..)
?FLOAT
doesn't like to becast
as aTIME
TIME
andTIME(0)
are different in terms of precision and number of bytes. If the precision is different, your problem may be caused by rounded valuesTIME
?