CODEWITHSUNDEEP
sql-server
AJF
AJF

Reputation: 1921

Copy varchar column data to date column

I am using SQL Server 2017 and I have a table with a column of varchar type but the value is a date, i.e 'dd/mm/yyyy', and I need to convert this to a date value column.

I attempted a straight ALTER TABLE ALTER COLUMN like this:

USE <databasename>

ALTER TABLE <the_table_name> 
    ALTER COLUMN [column name] date;
GO

But the output is an error

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

I added a new column [AltColumnName] with the intention of copying over the column values with an UPDATE statement, and then renaming the columns but so far all forms of UPDATE I try fails with the same "conversion failed" message.

How do I copy over the varchar column values to the date column?

Upvotes: 0

Views: 371

Answers (1)

Thom A
Thom A

Reputation: 95557

Assuming that all rows are dd/MM/yyyy and are valid.

First you need to change your value to an ISO format, we're going to use yyyyMMdd

UPDATE dbo.YourTable
SET DateColumn = CONVERT(varchar(10),CONVERT(date,DateColumn,103),112);

Then you can ALTER your table:

ALTER dbo.YourTable ALTER COLUMN DateColumn date;

Upvotes: 1

Related Questions