Conversion Failed when Converting DateTime

Question

This is my first post so any mistakes apologies in advance.

I am trying to convert a character string into a date format. My string is in the format '20110709_000000'.

I have tried the following queries after research but both return me the following error: 'Conversion failed when converting datetime from character string.'

I have tried

Select CAST(REPLACE('20110709_000000','_', '') AS DATETIME)

and

SELECT CONVERT(DATETIME,REPLACE('20110709_000000','_', ''),109).

Any help would be appreciated.

Answer 1

Assuming that 000000 is hhmiss you can use substring to get the parts and build a string on the format yyyymmdd hhmiss that you can cast to datetime.

declare @ds varchar(15) = '20110709_102030'

select cast(substring(@ds,  1, 8)+' '+
            substring(@ds, 10, 2)+':'+       
            substring(@ds, 12, 2)+':'+
            substring(@ds, 14, 2) as datetime)

Result:

-----------------------
2011-07-09 10:20:30.000