T-SQL Conversion Failed when converting date/or time from character string

Question

Perfmon isn't so kind with the way it creates the database when logging directly to SQL:

select top 1 Convert(datetime, CounterDateTime) from CounterData

returns

Conversion failed when converting date and/or time from character string.

The value of that cell is "2012-01-25 14:12:10.802". What is the proper way to convert this to a datetime field during selection?

Answer 1

No better answer but this makes the taste a trifle less bitter.

CAST(CAST(CounterData.CounterDateTime AS CHAR(NN)) AS DATETIME) AS CounterDateTime

Also truncates the value so that additional T-SQL DateTime truncation to the minute, hour, etc is not needed.

Answer 2

I figured out that the following works (instead of the CHAR(24) it is):

select top 1 Cast(Cast(CounterDateTime as CHAR(23)) as datetime) from CounterData

Hoping there is a better solution though.

Answer 3

CONVERT(DATETIME, SUBSTRING(CounterDateTime, 1, 23), 102)