CODEWITHSUNDEEP
t-sqlcasting
Woody_1983
Woody_1983

Reputation: 159

Conversion Failed when Converting DateTime

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.

Upvotes: 3

Views: 266

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 3

Garrett Vlieger
Garrett Vlieger

Reputation: 9494

Do you need the 000000 at the end? This should work:

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

Upvotes: 1

Related Questions