Convert Varchar To Datetime and Add Seconds

Question

I have the following two columns in my table

callstarttime       callduration
20180101215910        120
20180101215710        220

The first column's data type is varchar(255) whilst the second column is int.

What I want is to add the callduration to the callstarttime inorder to get the time which the call ended.

So I need to first convert the callstarttime column to datetime and then use the Dateadd function.

My script is as follows:

select convert(datetime, callstarttime) from Mytable

I have also tried using cast as follows

select cast(callstarttime as datetime) from Mytable

However I am getting the following error

Conversion failed when converting date and/or time from character string.

Similar questions have been asked on Convert varchar column to datetime and how to convert this varchar to datetime format?, however I can't tailor my script based on the solutions provided there.

Answer 1

Let me know how this works for you.

CREATE TABLE #t (callstarttime VARCHAR(255), callduration INT);
INSERT #t ( callstarttime, callduration )
SELECT *
FROM (
VALUES('20180101215910', 120),('20180101215710', 220)
) AS x (callstarttime, callduration);


WITH munge AS (
SELECT *, 
    LEFT(callstarttime, 8) AS d,
    STUFF(STUFF(SUBSTRING(callstarttime, 9, LEN(callstarttime)) , 3, 0, ':'), 6, 0, ':') AS t
FROM #t AS t
)
SELECT *, 
       TRY_CONVERT(DATETIME, d + ' ' + t),
       DATEADD(SECOND, munge.callduration, TRY_CONVERT(DATETIME, d + ' ' + t))
FROM munge

Answer 2

The problem, as I'm sure you're aware, is that SQL Server requires certain punctuation between the numerals before it will consider a character sequence to be a valid datetime. sp_BlitzErik has shown one way to inject those characters. Here's another using the FORMAT function introduced in SQL Server 2012:

declare @s varchar(255) = '20180101215910';
declare @i bigint = @s;  -- implicit type conversion

select
    Raw             = @i,
    WithSeparators  = FORMAT(@i, '####-##-##T##:##:##'),
    AsDateTime      = CONVERT(datetime, FORMAT(@i, '####-##-##T##:##:##')),
    Incremented     = DATEADD(SECOND, 120, CONVERT(datetime, FORMAT(@i, '####-##-##T##:##:##')));


           Raw  WithSeparators       AsDateTime              Incremented
--------------  -------------------- ----------------------- -----------------------
20180101215910  2018-01-01T21:59:10  2018-01-01 21:59:10.000 2018-01-01 22:01:10.000

I have to convert to integer type because that is one that FORMAT will accept. It has to be BIGINT due to the number of digits.

Performance-wise I think you would find it very difficult to measure the difference between the two techniques.

This may have a smaller cognitive load, depending on how familiar you are with reading T-SQL.

Answer 3

If we can assume callstarttime is a standard format and you have permission in MSDB, the agent_datetime function should get you what you're looking for.

CREATE TABLE #mytable (callstarttime varchar(255),callduration int)

INSERT INTO #mytable VALUES ('20180101215910',120),('20180101215710',220)

SELECT 
    msdb.dbo.agent_datetime(SUBSTRING(callstarttime,1,8), SUBSTRING(callstarttime,9,6)) AS [CallStartTime],
    DATEADD(SECOND,callduration,msdb.dbo.agent_datetime(SUBSTRING(callstarttime,1,8), SUBSTRING(callstarttime,9,6))) AS [CallEndTime]
FROM #mytable

DROP TABLE #mytable