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.
CodePudding user response：
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';
CodePudding user response：
You just need to set DATEFORMAT to dmy first.
SET DATEFORMAT dmy;
ALTER TABLE t ALTER COLUMN d date;