存储过程和 UDF 之间的不同结果

我在我的存储过程和我的 UDF 之间得到不同的结果。

它们两者的要点是，它查询一个远程链接的服务器，将这些结果塞进一个临时表/表变量中，然后用“00:00:00”替换 NULL，以便我可以对金额求和。然后获取该日期，并DATEDIFF对其进行操作以提供总工作时间。

如果我输入值('John Doe', '04/05/2013', '04/16/2013')，存储过程将返回我正在寻找的答案，而 SVF 则不是。如果存储过程的工作小时数为“23”，则 SVF 返回“1900-01-24 00:00:00”。

我意识到这与我returning在 SVF 中的身份有关，但我无法弄清楚。我试过return TIME(0)，VARCHAR(5)。好像 SVF 没有做这件事DATEDIFF。

我正在使用 SQL Server 2012 (11.0.3000)

下面是 SVF 和存储过程。

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

这是存储过程：

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