Convert 14 Char String to Datetime

Question

Convert 14 Char String to Datetime

Dom 951

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?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Bruce (SqlWork.com) 81,356 Volunteer Moderator

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)

Dom 951 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 951 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 951 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 ;:

Answer 2

Ramana Kopparapu 306

We can also get the output with STUFF..

DECLARE @str VARCHAR(14) = '2024040611102200021';

SELECT CONVERT(datetime,

   LEFT(@str,8) + ' ' + 

   STUFF(STUFF(RIGHT(@str,6),3,0,':'),6,0,':'), 120);

Output:

2024-04-06 11:10:22.000

Share via

Convert 14 Char String to Datetime

1 additional answer

Your answer