Convert char to datetime in sql from an odd format

Question

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 1

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

Convert char to datetime in sql from an odd format

Sign up