CODEWITHSUNDEEP
sqlsql-serverfunctiondatetimessms
ReportGuy_JPS
ReportGuy_JPS

Reputation: 3

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!

Upvotes: 0

Views: 1699

Answers (2)

Sagar Shelke
Sagar Shelke

Reputation: 517

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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.

Upvotes: 4

Related Questions