Lakeland-FL Lakeland-FL - 5 months ago 17
SQL Question

Convert varchar into datetime in SQL Server

How do I convert a string of format

mmddyyyy
into
datetime
in SQL Server 2008?

My target column is in
DateTime


I have tried with
Convert
and most of the
Date
style values however I get an error message:


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

KM. KM.
Answer

OP wants mmddyy and a plain convert will not work for that:

select convert(datetime,'12312009')

Msg 242, Level 16, State 3, Line 1 
The conversion of a char data type to a datetime data type resulted in 
an out-of-range datetime value

so try this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)
Comments