T-SQL: Convert varchar date column to date type

Question

I have the following column date in a SQL Server database.

The datatype is currently varchar, and the dates are stored in the following format: 2010-04-24

I'm running the following command:

ALTER TABLE games ALTER COLUMN date date

But I get the following error:

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

What am I doing wrong?

Answer 1

There might be a few dates that don't work. Which version of SQL Server are you using? 2008 I presume, if you have a DATE datatype - right?

My suggestion would be:

1) Create a new column of type DATE

ALTER TABLE dbo.Games
   ADD NewDate DATE

2) Run an update script on your table and see which entries might not convert into DATE

UPDATE dbo.Games
SET NewDate = CAST([date] AS DATE)
WHERE ISDATE([date]) = 1

The check for ISDATE() = 1 should filter out those entries that cannot be converted to a DATE datatype.

Once all your entries have been successfully converted, you could always drop the old [date] column and rename the new column back to [date] - but using a reserved word like DATE for a column name isn't really smart - I'd try to use something more speaking, something more expressive (relating to the problem domain you're in).