ReportGuy_JPS ReportGuy_JPS - 3 months ago 11
SQL Question

SQL Function to calculate end time based on duration not working

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:

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:

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 [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!

Answer

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.