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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

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