Converting text to datetime

Question

Post reply

Converting text to datetime

rnpatter

SSC Eights!

Points: 889
More actions
August 16, 2005 at 8:48 am

#102710

I'd like to convert a text string (yyyymmddhh:mm:ss:mmm) to datetime. I thought something like the following would work, but I'm missing something.
select convert(datetime, '2005081607:58:26:170') or
select cast ('2005081607:58:26:170' as datetime)
Suggestions appreciated.

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

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

You miss a space between date and time

DECLARE @MyDate DATETIME

SET @myDate = '20050816 07:58:26:170'

SELECT CONVERT(DATETIME, '20050816 07:58:26:170') WithConvert, @myDate Implicit

Regards,
gova

rnpatter SSC Eights! Points: 889 More actions · Answer 2

govinn:

Thanks. I think I'm getting closer.

But, the strings I want to convert will ultimately come from a table where they reside in a CHAR column without the space. So, I still need to cope with the missing space somehow.

select MyDateString from MyTable

MyDateString

--------------------

2005081607:58:26:170

2005081508:31:41:745

2005081312:00:01:226

2005081214:10:16:644

2005081213:01:22:788

noeld SSC Guru Points: 96590 More actions · Answer 3

Just add a little string manipulation

SELECT convert(datetime, Left(MyDateString,8) + ' ' + Right(MyDateString, 12) ) as Converted

from MyTable

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

This will work. I am not sure about a way to convert into datetime without a space between date and time in T-SQL.

SET NOCOUNT ON

DECLARE @myTable Table

(

MyDateString VARCHAR(20)

)

INSERT @myTable

SELECT '2005081607:58:26:170' UNION

SELECT '2005081508:31:41:745' UNION

SELECT '2005081312:00:01:226' UNION

SELECT '2005081214:10:16:644' UNION

SELECT '2005081213:01:22:788'

SELECT CONVERT(DATETIME, SUBSTRING(MyDateString, 1, 8) + ' ' + SUBSTRING(MyDateString, 9, 12)) MyDate FROM @myTable

Regards,
gova

rnpatter SSC Eights! Points: 889 More actions · Answer 5

Thanks govinn and Noel. I now see how string manipulation will help.

Best Regards,

rnpatter