CODEWITHSUNDEEP
sqlsql-server
fLen
fLen

Reputation: 598

SQL Server: how to change data entry from VARCHAR to DATETIME?

I have below sample data:

03202012 as date but the column datatype is Varchar.

I want to convert it to 2012-03-20 00:00:00.000 as Datetime.

I tried using 

CAST(CONVERT(CHAR(10), Column, 101) AS DATETIME)

But I get an error:

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

Complete code snippet to test:

DECLARE @Column VARCHAR(MAX) = '03202012'

SELECT CAST(CONVERT(CHAR(10), @Column, 101) AS DATETIME)

Upvotes: 2

Views: 91

Answers (3)

Cetin Basoz
Cetin Basoz

Reputation: 23837

Use yyyyMMdd format, that always works:

DECLARE @myDateString varchar(10) = '03202012';
SELECT cast( substring(@myDateString, 5, 4)+
             substring(@myDateString, 1, 2)+
             substring(@myDateString, 3, 2) AS datetime);

Upvotes: 3

fLen
fLen

Reputation: 598

I found below script help me solved my concern.

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

Result: 2016-01-31 00:00:00.000

Thanks all for the effort. :D

Upvotes: 2

Richard Hamilton
Richard Hamilton

Reputation: 26444

In MySQL, you can use the STR_TO_DATE function to convert a string to a date. For your example, it would look like this

STR_TO_DATE("03-02-2012", "%m-%d-%Y");

Note that the format part of the string must match the format part of the date.

Edit: Just found out this is for SQL Server, but I assume this will work there as well.

Upvotes: 0

Related Questions