Convert column with data MM/DD/YYYY varchar to date in sql server?

Question

I've found some similar questions but haven't been able to get anything to work yet. I'm very much a novice with little SQL experience.

I have a column END_DATE as Varchar(10) where all the rows follow the mm/dd/yyyy format and I would like to convert it to date. I have an empty column formatted as date if that helps. There are 36 million rows.

Answer 1

SELECT CONVERT(DATETIME,YourColumn,101) FROM YourTable

101 is mm/dd/yyyy format.

You zany backwards americans :)

To update your existing column

UPDATE YourTable 
SET YourNewColumn = CONVERT(DATETIME,YourOldColumn,101)

Since it appears you have invalid data, use this method to isolate it:

UPDATE YourTable 
SET YourNewColumn = CONVERT(DATETIME,YourOldColumn,101)
WHERE SomeTableKey BETWEEN ASmallCode AND ABiggerCode

Find a key in your table that you can use to divide up the data and try updating half the table... now halve it again and again until you find the offending data. Post the data here and we will come up with some code to allow for it.

Answer 2

I think you should convert END_DATE to DATETIME type, because you have 36 million rows and it will give a performance boost when you do not have to cast or convert it datetime with select statement.

To answer your question, you can do something like

select CAST(END_DATE AS DATETIME) FROM MyTable