convert HH:MM:SS to datetime

Question

convert HH:MM:SS to datetime

mandavli

SSCrazy

Points: 2122
More actions
February 7, 2017 at 8:29 am

#335056

I receive duration data that is in hh:mm:ss format and stored in nvarchar(50) column
I want convert that to datetime by adding '01/01/1900' to the time part.
Tsql when I use convert(datetime, '01/01/1900 ' + [duration_hh:mm:ss]) , I get error
Conversion failed when converting date and/or time from character string.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you convert time directly to datetime, itends up being 1900 anyway, so just do a direct conversion.
CONVERT(datetime,convert(time,durationvalue)) As DurationTime2 or directly like convert(datetime,durationvalue) AS DurationTime3
WITH mySampleData(durationvalue) AS ( SELECT CONVERT(nvarchar(50),'00:44:21') UNION ALL SELECT '16:54:47' UNION ALL SELECT '16:54:49' UNION ALL SELECT '16:54:53' UNION ALL SELECT '18:35:11' UNION ALL SELECT '16:02:17' UNION ALL SELECT '15:08:32' UNION ALL SELECT '16:54:51' UNION ALL SELECT '00:44:21' UNION ALL SELECT '16:54:47' ) SELECT convert(time,durationvalue) As DurationTime, CONVERT(datetime,convert(time,durationvalue)) As DurationTime2, convert(datetime,durationvalue) AS DurationTime3 FROM mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

mandavli - Tuesday, February 7, 2017 8:29 AM
I receive duration data that is in hh:mm:ss format and stored in nvarchar(50) column
I want convert that to datetime by adding '01/01/1900' to the time part.
Tsql when I use convert(datetime, '01/01/1900 ' + [duration_hh:mm:ss]) , I get error
Conversion failed when converting date and/or time from character string.

Would you have values greater than 23:59:59? If that's possible then you might need to add a converted value. If not, then you can simply convert to datetime without any concatenation or other intermediate steps as shown by Lowell.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 3

Luis Cazares - Tuesday, February 7, 2017 9:06 AM
mandavli - Tuesday, February 7, 2017 8:29 AM
I receive duration data that is in hh:mm:ss format and stored in nvarchar(50) column
I want convert that to datetime by adding '01/01/1900' to the time part.
Tsql when I use convert(datetime, '01/01/1900 ' + [duration_hh:mm:ss]) , I get error
Conversion failed when converting date and/or time from character string.
Would you have values greater than 23:59:59? If that's possible then you might need to add a converted value. If not, then you can simply convert to datetime without any concatenation or other intermediate steps as shown by Lowell.

good point! i did not consider a duration more than 24 hours; i would certainly expect to sometimes see days:hours:minutes:seconds as a duration!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 4

I have to ask why someone is storing something that will only ever contain digits and colons as NVARCHAR. It should be stored simply as CHAR(8) if the times never exceed 24 hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Jeff Moden - Tuesday, February 7, 2017 9:20 AM
I have to ask why someone is storing something that will only ever contain digits and colons as NVARCHAR. It should be stored simply as CHAR(8) if the times never exceed 24 hours.

It should be stored as time.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2