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.'
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)