CODEWITHSUNDEEP
sqlsql-server
zesla
zesla

Reputation: 11803

Clean a messy column and update table in SQL Server

I have a table dbo.mytbl in SQL Server database where column sale_date is messy.

It contains something like None, empty string, not available, 2016, etc - other than those date-like format 2019-12-05.

It's currently of datatype nvarchar(max).

I can clean up the column like below. All those not like dates are converted to NULL 

SELECT 
    CASE 
       WHEN ISDATE(sale_date) = 1 
          THEN CONVERT(DATE, sale_date) 
          ELSE NULL 
    END AS sale_date
FROM dbo.mytbl

I want to update the table, clean and modify the sale_date column in the database, using alter table.

How can I achieve that?

ALTER TABLE dbo.mytbl
ALTER COLUMN ????????????;

Upvotes: 0

Views: 579

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use try_convert()!

If you are not worried about the non-dates, you can do:

update mytbl
    set sale_date = try_convert(date, sale_date);

This will set all the non-dates to NULL. Then you can safely do:

alter table mytbl alter column sale_date date;

Upvotes: 3

Related Questions