Cast or Convert?

Question

I have am getting an error when I try and alter a date column:

Arithmetic overflow error for type varchar, value = 20100601.000000.

I would like it to go from float to the datetime format of 20100601.

Since Alter doesn't work, how can I use cast or convert to change the datatype for every entry in the table?

Answer 1

"20100601.000000" when cast to datetime from float means 20 million, 100 thousand, 601 days after 01 Jan 1900.

"20100601" as a string is 01 Jun 2010.

I'd use this to be 100% sure of conversions

SELECT CAST(CAST(CAST(20100601.000000 AS int) AS char(8)) AS datetime)

Answer 2

I'm sure there must be a better way...

ALTER TABLE dbo.YourTable ADD
    NewColumn datetime NULL

UPDATE [YourTable]
   SET  [NewColumn] = 
        CAST(CAST(CAST(ROUND(YourOriginalColumn,0) as INT) AS char(8)) AS DATETIME)

Then if the idea is to replace the old column after you have sanity checked things, made a backup etc.

ALTER TABLE dbo.Cars
    DROP COLUMN YourOriginalColumn

EXECUTE sp_rename N'dbo.YourTable.NewColumn', N'YourOriginalColumn', 'COLUMN' 

Answer 3

UPDATE table_name
SET new_date_column=CAST(CAST(CAST( old_date_column AS INT) AS VARCHAR) AS DATETIME)

This will update the new column