Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 203
Developer Avatar asked Oct 02 '09 14:10

Developer


People also ask

How do I convert text to date in sql?

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.

How do you resolve the conversion of a VARCHAR data type to a datetime data type resulted in an out of range value?

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.

How do I convert a string to a 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.


1 Answers

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) 
like image 187
KM. Avatar answered Oct 05 '22 21:10

KM.