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)
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.
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).
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With