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

Question

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)

If you're on SQL Server 2008 or newer, I'd recommend to use DATE (if you need the date only - no time portion) or then DATETIME2(n) for date & time instead of DATETIME (which has a number of annoying issues). — marc_s
– marc_s, Commented Jan 5, 2016 at 5:51

Gordon Linoff · Accepted Answer · 2016-01-05 01:34:49Z

3

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);

edited Jan 5, 2016 at 1:34

Gordon Linoff

1.3m62 gold badges710 silver badges859 bronze badges

answered Jan 5, 2016 at 0:54

Cetin Basoz

24.1k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

I would be inclined to use left() and right(), but this is the better answer.

Felix Pamittan · Accepted Answer · 2016-01-05 01:10:13Z

2

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

edited Jan 5, 2016 at 1:10

Felix Pamittan

31.9k7 gold badges44 silver badges71 bronze badges

answered Jan 5, 2016 at 0:57

fLen

5984 gold badges13 silver badges28 bronze badges

6 Comments

mohan111 Over a year ago

Is it okay to ask the question and post the answer by same person. I have never seen in SO @Ajay2707

Ajay2707 Over a year ago

There is no issue with that, only want to close the question by accepting any one answer.

fLen Over a year ago

@Ajay2707, how can i do that to close this question?

Ajay2707 Over a year ago

in each answer, below the down arrow there is right sign, click on it.

fLen Over a year ago

there's a label stating: You can accept your own answer in 2 days.

|

Richard Hamilton · Accepted Answer · 2016-01-05 00:53:46Z

0

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.

answered Jan 5, 2016 at 0:53

community wiki

Richard Hamilton

1 Comment

Rahul Over a year ago

NO, it will not. Question is tagged with SQL Server and in such case no point in giving a MySQL specific answer. Isn't it?

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

6 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

1 Comment

Related