How do I change a column data type from varchar(255) to date?

Question

I am using a reporting database which consists of 20 tables on SQL Server. In marketing table I have a column report_date which is currently a varchar(255). It is basically a date formatted in a way 2017-12-12. I want to change the type of this column to a date. I’m running this script but getting errors. The script is down below:

USE [reporting].[dbo].[marketing]
GO
SELECT CONVERT(date, 'report_date');

These are the errors I’m getting.

Msg 911, Level 16, State 1, Line 1 Database 'dbo' does not exist. Make sure that the name is entered correctly.

Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.

How should I adjust the script?

Answer 1

If you want to change the column's data type (and you should) then you need to use an alter table statement. Your first error message is because of the USE directive - it should be

USE [reporting]
GO

Your second error message is because 'report_date' is a string constant, not a column name.

The select statement should be

SELECT CAST(report_date as date)  -- Don't use Convert without the style argument....
FROM [dbo].[marketing]

Note that if you have even a single value that can't be converted to date you will get the second error again.

Basically I would recommend first making sure that the select statement completes without any exceptions, and only then alter the table:

USE reporting
GO

ALTER TABLE [dbo].[marketing] 
     ALTER COLUMN report_date DATE
GO

Answer 2

TRY THIS:

SELECT CONVERT(DATE, report_date) --If only for comparison
ALTER TABLE marketing ALTER COLUMN report_date DATE --If want to change in the table

Answer 3

The proper way to do this is like;

ALTER TABLE reportin.dbo.marketing ALTER COLUMN 'report_date' date

And you can check this How do you change the datatype of a column in MS SQL?

Answer 4

"Convert" is used for conversion from one datatype to other in select queries, you need to use alter statement for altering database columns and also USE [databasename] is enough, so rewriting your query here :

USE [reporting]
GO
ALTER TABLE marketing ALTER COLUMN ReportDate DATE

Answer 5

Slow, but safe way is to:

1. Create a new column (ALTER TABLE dbo.MyTable ADD MyNewColumn DATE NULL;)
2. Update the new column using the old one (UPDATE dbo.MyTable SET MyNewColumn = CONVERT(DATE, MyColumn);)
3. Drop the old column (ALTER TABLE dbo.MyTable DROP MyColumn;) - Alternatively, you can rename this column instead and keep it as is)
4. Rename the new column (EXEC sp_rename 'dbo.MyTable.MyNewColumn', 'MyColumn', 'COLUMN';)

You might have to drop indexes beforehand, but this method (and it's alterations) help to prevent data loss.

If you encounter an error during casting, you should eliminate those values from the update (by for example adding a WHERE clause) and investigate them manually.

If you are using SQL Server 2012 or newer, you can use TRY_CONVERT() to ignore the values which cannot be converted to DATE. In this case you will have NULL in your new column.

Before you do anything, make sure, that all applications and code which is working with this column can handle the changes.

Notes

You might want to rebuild the table/indexes after a change like this.