Ryan Smith Ryan Smith - 11 months ago 41
SQL Question

Convert char to datetime in sql from an odd format

I have a column

createdate char(26)
with data like

I want to convert this column to a standard

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
format to
in SQL Server 2008?

Thanks in advance

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

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


2004-01-30 21:29:12.893

OR for DateTime2

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

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


2004-01-30 21:29:12.8933740