Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Convert VarChar to Date in ALTER TABLE

Saw a few threads on this but I have a specific instance where I am trying to do the conversion within an ALTER TABLE statement.

ALTER TABLE Leads
ALTER COLUMN [Created Date] Date

This is throwing an error:

Msg 241, Level 16, State 1, Line 34
Conversion failed when converting date and/or time from character string.
The statement has been terminated.

Created Date is currently set as (varchar(max), null)

like image 347
hansolo Avatar asked Sep 21 '16 13:09

hansolo


People also ask

How do you resolve the conversion of a varchar data type to a datetime data type resulted in an out of range value?

Answer: The error is due to an invalid date format being saved to the custom_rmh_rooms_history SQL table. To resolve this issue, the Windows Regional settings need to be modified and the Short Date format needs to be in MM/dd/yyyy format.

How do I convert a column to a date in SQL?

Use the CONVERT() function to change the format of a date from a given column or expression. This function takes three arguments: The new data type (in our example, NVARCHAR). An expression or column name containing the date to format (in our example, the start_date column).

How do you cast a date?

The CAST() function in MySQL is used to convert a value from one data type to another data type specified in the expression. It is mostly used with WHERE, HAVING, and JOIN clauses. This function is similar to the CONVERT() function in MySQL. It converts the value into DATE datatype in the "YYYY-MM-DD" format.


1 Answers

You could standardize the date format. Something like this:

UPDATE Leads
    SET [Created Date] = TRY_CONVERT(Date, [Created Date], 101);

The third argument is for the MM/DD/YYYY format mentioned in a comment. This will convert the value to a date -- if it can -- and to NULL otherwise. Then, SQL Server will use its default formats to convert back to a string.

NOTE: If you do this, be sure you back up the table, so you don't lost the information in the column!

Then, your code should work:

ALTER TABLE Leads ALTER COLUMN [Created Date] Date;

You can find the rogue values by using:

select [Created Date]
from Leads
where try_convert(date, [Created Date], 101) is null and
      [Created Date] is not null;
like image 157
Gordon Linoff Avatar answered Oct 14 '22 02:10

Gordon Linoff