Trying to clean date column but having difficulty; ending up with calculated column error

Question

I have a dataset and I a particular column 'Last_DCR_Date' is in 'varchar' data type format, and my goal is to convert it to DATE format, but so far I've not been able to.

An example of the data in the 'Last_DCR_Date' column looks like this : '24-11-2023', and is in nvarchar.

And I want to transform it to '2023-11-24'. I'll really appreciate the help

I have tried creating new ('day', 'month', 'year') columns by using:

ALTER TABLE mytable
ADD day AS SUBSTRING (Last_DCR_Date, 1, 2) 

....and so on and so forth for the remaining two.

But having done that, I then tried creating a new column by concatenating the 3 columns, e.g.

ALTER TABLE mytable
ADD new_DCR_Date AS CONCAT_WS('/', day, month, year)

But SQL Server says I can't perform that with computed columns

Answer 1

You can alter column to data type Date And select data with appropriate format. Here is sample code:

ALTER TABLE SampleTable
ADD New_DCR_Date DATE; -- it will automatically convert if data has same format

UPDATE SampleTable
SET New_DCR_Date = CONVERT(DATE, Last_DCR_Date, 105); --yyyy-MM-dd

Here is the fiddle link .