SQL函数根据持续时间不工作计算结束时间

Can you help me with this question about scripting scalar functions in T-SQL? I have some data from a call center that contains a start time and a duration for each call. I want to create a function that will output the end time of the call. Here is a sample of the data:

你能帮我解决一下T-SQL中脚本标量函数的问题吗？我有来自呼叫中心的一些数据，其中包含每个呼叫的开始时间和持续时间。我想创建一个输出调用结束时间的函数。以下是数据示例：

CALLID     EMPLOYEE_ID   START_TIME               DURATION     STATUS      
--------  ----------    -----------------------  ------------ ------------
796544     205285       2016-07-29 19:29:02.000  00:00:27     Complete    
796543     205284       2016-07-29 19:25:31.000  00:02:31     Complete    
796542     205284       2016-07-29 19:22:01.000  00:00:50     Complete    
796541     205285       2016-07-29 19:11:58.000  00:00:21     Complete    
796540     205285       2016-07-29 19:07:40.000  00:02:16     Complete

I've created a function that I think should work, but it always returns the start time without adding anything to it. I want it to parse [DURATION] (varchar), calculate the duration in seconds, then add that to the start time. Here's what it looks like:

我创建了一个我认为应该可以工作的函数，但它总是返回开始时间而不向它添加任何内容。我希望它解析[DURATION]（varchar），以秒为单位计算持续时间，然后将其添加到开始时间。这是它的样子：

ALTER FUNCTION [dbo].[AddDuration] (@Duration varchar(8), @StartTime datetime)
RETURNS datetime
BEGIN

DECLARE @Hours      int     = CONVERT(int,SUBSTRING(@Duration,1,2))
DECLARE @Minutes    int     = CONVERT(int,SUBSTRING(@Duration,4,2))
DECLARE @Seconds    int     = CONVERT(int,SUBSTRING(@Duration,7,2))
DECLARE @EndTime    datetime

SET @EndTime = DATEADD(SECOND,(@Hours * 60 * 60) + (@Minutes * 60) + @Seconds, @StartTime)

RETURN (@EndTime)
END

What am I missing? When I do the exact same logic within my SELECT statement, it works perfectly. But the function doesn't.

我错过了什么？当我在SELECT语句中执行完全相同的逻辑时，它完美地工作。但功能没有。

SELECT  [START_TIME]
    ,   [END_SELECT] = (DATEADD(SECOND,
                   (CONVERT(int,SUBSTRING(DURATION,1,2))*60*60)
                 + (CONVERT(int,SUBSTRING(DURATION,4,2))*60)
                 + (CONVERT(int,SUBSTRING(DURATION,7,2)))
                ,  START_TIME)
    )
    ,   [END_FUNCTION] = dbo.AddDuration(DURATION,START_TIME)


START_TIME                END_SELECT                END_FUNCTION
-----------------------   -----------------------   -----------------------
2016-08-25 09:21:00.000   2016-08-25 09:24:55.000   2016-08-25 09:21:00.000
2016-08-25 09:26:00.000   2016-08-25 09:31:22.000   2016-08-25 09:26:00.000

Thank you for your help!

感谢您的帮助！

2 个解决方案

#1

You can add two datetimes together. And, your time looks like it is in a nice proper time format. You might consider:

您可以一起添加两个日期时间。并且，您的时间看起来像是一个很好的适当时间格式。你可能会考虑：

select start_time + cast(cast(duration as time) as datetime)
from t;

This seems easier than a udf.

这似乎比udf更容易。

#2

Try this:

尝试这个：

CREATE TABLE #TABLE1 (CALLID INT,EMPLOYEE_ID   INT,START_TIME    DATETIME,DURATION   TIME,  STATUS      VARCHAR(15))

INSERT INTO #TABLE1

SELECT 796544, 205285, '2016-07-29 19:29:02.000',  '00:00:27', 'Complete' UNION ALL
SELECT 796543, 205284, '2016-07-29 19:25:31.000',  '00:02:31', 'Complete' UNION ALL    
SELECT 796542, 205284, '2016-07-29 19:22:01.000',  '00:00:50', 'Complete' UNION ALL    
SELECT 796541, 205285, '2016-07-29 19:11:58.000',  '00:00:21', 'Complete' UNION ALL    
SELECT 796540, 205285, '2016-07-29 19:07:40.000',  '00:02:16', 'Complete' 

SELECT *,DATEADD(SECOND, 
              DATEPART(HOUR, DURATION)*60*60 -- HOURS TO SECONDS
            + DATEPART(MINUTE, DURATION)*60  -- MINUTES TO SECONDS
            + DATEPART(SECOND,DURATION)      -- SECONDS
            , START_TIME) END_TIME
FROM #TABLE1

#1