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

bkhjykvo 于 12个月前发布在其他

关注(0)|答案(1)|浏览(137)

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

sql-server

来源：https://stackoverflow.com/questions/77677525/trying-to-clean-date-column-but-having-difficulty-ending-up-with-calculated-col

1条答案

按热度按时间

e0uiprwp1#

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 .

赞(0）回复(0）举报 12个月前

我来回答

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

1条答案

相关问题

热门标签

最新问答