Ryan Smith Ryan Smith - 1 month ago 11
SQL Question

Convert char to datetime in sql from an odd format

I have a column

createdate char(26)
with data like
2004-01-30-21.29.12.893374


I want to convert this column to a standard
DATETIME


However, doing a

Convert(DATETIME, createdate,120)


is giving this error:


The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


I've tried other deviations of the convert value as well, but I feel that the format of the original date value is causing the issue.

I think I need to break the
createdate char(26)
value out using multiple LEFT type statements, but I want to make sure i'm not over complicating this first.

Can any one please help me convert my
char
format to
datetime
in SQL Server 2008?

Thanks in advance

Answer
Declare @String varchar(50) = '2004-01-30-21.29.12.893374'

Select cast(Left(Stuff(Replace(Stuff(@String,11,1,' '),'.',':'),20,1,'.'),23) as datetime)

Returns

2004-01-30 21:29:12.893

OR for DateTime2

Declare @String varchar(50) = '2004-01-30-21.29.12.893374'

Select cast(Stuff(Replace(Stuff(@String,11,1,' '),'.',':'),20,1,'.') as datetime2)

Returns

2004-01-30 21:29:12.8933740