I have a field in a table called TraceID that stores data like this: 2024040611102200021 The first 8 characters represent the date (YYYYMMDD). The next 6 characters represent the time (HHMMSS). The last five are insignificant numbers. So this would be 04.06.2024 @ 11:10:22 (AM). I create the representation of the timestamp: LEFT(TraceID,14) as TStamp How can I now convert this string to a datetime type?

simple: declare @d varchar(20) = '2024040611102200021' select cast(SUBSTRING(@d,1,4) + '-' + SUBSTRING(@d,5,2) + '-' + SUBSTRING(@d,7,2) + 'T' + SUBSTRING(@d, 9,2) + ':' + SUBSTRING(@d, 11,2) + ':' + SUBSTRING(@d, 13,2) as datetime)

Convert 14 Char String to Datetime

Accepted answer

Bruce (SqlWork.com) 61,491 Reputation points

2024-04-06T20:36:48.5866667+00:00
simple:

declare @d varchar(20) = '2024040611102200021' select cast(SUBSTRING(@d,1,4) + '-' + SUBSTRING(@d,5,2) + '-' + SUBSTRING(@d,7,2) + 'T' + SUBSTRING(@d, 9,2) + ':' + SUBSTRING(@d, 11,2) + ':' + SUBSTRING(@d, 13,2) as datetime)
Please sign in to rate this answer.

1 person found this answer helpful.
Dom 771 Reputation points

2024-04-07T11:46:37.25+00:00

Yeah, I went that way with substrings. Was hoping there was a new function or something that would handle this more elegantly. But it works. Thanks!

Dom 771 Reputation points

2024-04-07T12:20:44.53+00:00

One more question: In your example, you declare the parameter as a literal value. In my query, I need it to be whatever is in the TraceID field for each record. I'm assuming I just forget about the "declare" part and just replace '@d' with the actual field name? Or is there some other preferred syntax?

Dom 771 Reputation points

2024-04-07T12:28:13.28+00:00

And then to make things even more complicated:

"Msg 8116, Level 16, State 1, Line 2 Argument data type bigint is invalid for argument 1 of substring function."

This is what finally ended up working for me:

cast (SUBSTRING(convert(char,[TraceID]),1,4) +'-'+SUBSTRING(convert(char,[TraceID]),5,2) +'-'+SUBSTRING(convert(char,[TraceID]),7,2)+'T'+SUBSTRING(convert(char,[TraceID]), 9,2) +':'+SUBSTRING(convert(char,[TraceID]), 11,2) +':'+SUBSTRING(convert(char,[TraceID]), 13,2) as datetime) Set_Date

Thanks again for helping me get there ;:
Sign in to comment

Share via

Convert 14 Char String to Datetime

0 additional answers