Menu
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.'
203
SQL Server: Convert string to date explicitly In SQL Server, converting a string to date explicitly can be achieved using CONVERT(). CAST() and PARSE() functions.
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. You need separators for the date like a “/”, a “.” or a “-“. We use substring to concatenate the “-” to use an acceptable date format and then we use the CONVERT function to convert the characters to sql date.
Using strptime() , date and time in string format can be converted to datetime type. The first parameter is the string and the second is the date time format specifier. One advantage of converting to date format is one can select the month or date or time individually.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
