May 17, 2012 at 3:41 am
Hi All,
I have a table like this,
while I am altering the column, datetime to Int , it is throwing error.
I doesn't have any data in table '
GO
CREATE TABLE tblConvert(ID INT,Dtt DATETIME)
GO
ALTER TABLE tblConvert ALTER COLUMN Dtt INT
go
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
May 17, 2012 at 3:50 am
ALTER TABLE is implicit based, if you look at the CAST CONVERT MSDN page, DateTime to INT has to be explicit so you need to do CONVERT(INT,Dtt) but you cant do that in the alter table command so it would have to be something like this
CREATE TABLE tblConvert(ID INT,Dtt DATETIME)
GO
ALTER TABLE tblConvert DROP COLUMN Dtt
GO
ALTER TABLE tblConvert ADD Dtt INT
GO
or if you have data in the table something like this
CREATE TABLE tblConvert(ID INT,Dtt DATETIME)
GO
ALTER TABLE tblConvert ADD Dtt2 INT
GO
UPDATE tblConvert SET Dtt2 = CONVERT(INT,Dtt)
GO
ALTER TABLE tblConvert DROP COLUMN Dtt
GO
sp_rename 'dbo.tblConvert.Dtt2','Dtt','COLUMN'
GO
May 17, 2012 at 3:52 am
Ramana P (5/17/2012)
Hi All,I have table like this,
while I am altering the column, datetime to Int , it is throwing error.
I doesn't have any data in table '
GO
CREATE TABLE tblConvert(ID INT,Dtt DATETIME)
GO
ALTER TABLE tblConvert ALTER COLUMN Dtt INT
go
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
unfortunatly you'll have to drop the column and re-add it - alter column doesn't support the WITH NOCHECK option either
MVDBA
May 17, 2012 at 3:56 am
Is there any other way....
May 17, 2012 at 4:03 am
the only way to change a column from DateTime to INT would be to drop and re-add the column as its an explicit conversion
May 17, 2012 at 4:05 am
Ramana P (5/17/2012)
Is there any other way....
why would it matter if the column is empty ?
MVDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply