Convert Varchar To Datetime and Add Seconds

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

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.

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

add a commentÂ |Â

up vote
2
down vote

favorite

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.

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

add a commentÂ |Â

up vote
2
down vote

favorite

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.

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

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.

sql-server sql-server-2016 type-conversion

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

edited 22 hours ago

Michael Green

13.2k82857

edited 22 hours ago

Michael Green

13.2k82857

edited 22 hours ago

Michael Green

13.2k82857

asked yesterday

db100

111

New contributor

asked yesterday

db100

111

asked yesterday

db100

111

New contributor

db100 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
3
down vote

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

answered yesterday

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
1
down vote

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.

answered 21 hours ago

Michael Green

13.2k82857

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

db100 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217316%2fconvert-varchar-to-datetime-and-add-seconds%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
3
down vote

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

answered yesterday

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
3
down vote

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

answered yesterday

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
3
down vote

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

answered yesterday

sp_BlitzErik

19.5k1161101

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

answered yesterday

sp_BlitzErik

19.5k1161101

answered yesterday

sp_BlitzErik

19.5k1161101

answered yesterday

sp_BlitzErik

19.5k1161101

answered yesterday

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
1
down vote

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.

answered 21 hours ago

Michael Green

13.2k82857

add a commentÂ |Â

up vote
1
down vote

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.

answered 21 hours ago

Michael Green

13.2k82857

add a commentÂ |Â

up vote
1
down vote

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.

answered 21 hours ago

Michael Green

13.2k82857

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.

answered 21 hours ago

Michael Green

13.2k82857

answered 21 hours ago

Michael Green

13.2k82857

answered 21 hours ago

Michael Green

13.2k82857

answered 21 hours ago

Michael Green

13.2k82857

add a commentÂ |Â

db100 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

db100 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky