SQLTeam.com | Weblogs | Forums

Varchar to date/date time error using convert or cast

I have varchar columns is sql 2008 r2 server that I want to migrate over to a sql 2014 server as date time. Whenever I use cast or convert my varchar columns to date time it gives a conversion error . How can I get around this because this column has to be date time for my application to work

Check the data in the varchar column,either the date is not formatted well(a mix of dmy,mdy etc. formats) or it may have data other than a valid date..also, post the exact error you are getting and the query as well

Check all rows where you might have issues by doing

Select * from migratetable where
isdate(columnmigrate) = 0

Move the data over to the new system into a staging/temporary table - then on that system you can use TRY_CAST or TRY_CONVERT.

For those rows that return a NULL value you can then review further to identify why they cannot be converted to a valid date. You can then determine how to address those issues.

None of these solutions work still getting error

What error are you getting when you use TRY_CAST or TRY_CONVERT?