2

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.

3

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
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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.