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?