I am getting an error when converting a varchar column into a data column

I am using SQL Server.

I have a table with a column called [DateReceived]. Now the column is a ‘varchar’ column. The date format is: ’18/05/2022′. When I try to convert it to a ‘date’ column using:

ALTER TABLE Correspondence ALTER COLUMN [DateReceived] DATE;

I get the following error:

Conversion failed when converting date and/or time from character
string.

>Solution :

You cannot simply convert a text date column to a bona fide date in this way. What you could do would be to create a new column and populate it using TRY_CONVERT:

ALTER TABLE Correspondence ADD DateReceivedNew Date;

Then, populate it using TRY_CONVERT:

UPDATE Correspondence
SET DateReceivedNew = TRY_CONVERT(date, DateReceived, 103);
-- note: use format mask 103 for dd/mm/yyyy

In the case of any error, your DateReceived column may have bad data in it. You may use the following query with TRY_CONVERT to flush out any offending records:

SELECT *
FROM Correspondence
WHERE TRY_CONVERT(date, DateReceived, 103) IS NULL;

Finally, you may drop the original DateReceived column, and rename DateReceivedNew:

ALTER TABLE Correspondence DROP COLUMN DateReceived;
sp_rename 'Correspondence.DateReceivedNew', 'DateReceived', 'COLUMN';