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
Best Answer
DATEDIFF
return the result inint
. Change the @HoursWorked data type in your scalar value function toint
instead ofdatetime
.