CODEWITHSUNDEEP
sql-serverfieldalter
Lawrance Rozario
Lawrance Rozario

Reputation: 461

SQL Server alter field name

I have problem to alter the database field from datetime to integer with values using SQL Server 2005

My code is...

alter table overtime alter column adate numeric(5)

while using this query am getting error like this

Disallowed implicit conversion from data type datetime to data type numeric, table ‘DaiichiPayroll.dbo.Overtime’, column ‘adate’. Use the CONVERT function to run this query.

Any solutions?

Upvotes: 1

Views: 764

Answers (2)

Martin Smith
Martin Smith

Reputation: 453318

Whilst Sachin's answer is probably the best solution you should be aware that the table will still continue to use the storage space for the deleted column until you rebuild the clustered index.

For that reason it would be quite nice if there was some explicit convert syntax that could be run to, for example, change an 8 byte datetime column to an 8 byte bigint column but AFAIK none exists.

An alternative would be to use SSMS to generate the script for you. This will generate a script that rebuilds the whole table. If you have a large table or many NCIs the blocking overhead of this may well be unacceptable however.

Upvotes: 2

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

The server is unable to convert your dateTime column to numeric implicitly. One of the solution is to -

  1. create a new column of numeric type
  2. convert your data from dateTime column to numeric column by writing an additional query and then
  3. delete the dateTime column
  4. Rename that newly added column to the same as that deleted dateTime column.

Upvotes: 6

Related Questions