to change one datatype to another datatype in sqlserver 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi

i have created a table with 3 rows...

SQL

user_id   int,
user_name varchar(50),
address   varchar(50),
amount    bigint

user_id is primary key.

i have inserted 10 values into table .
Now i want to change amount datatype(bigint) to float..when i try to do this im getting error...can any one get me the solution for this?
Thanks in advance

Regards
Meenakshi

Posted 10-May-12 21:36pm

Meenakshi_V

Updated 11-May-12 12:15pm

Wendelius

v2

Add a Solution

3 solutions

Solution 3

To clarify a bit, basically you can change the data type of a column, see ALTER TABLE[^] and especially ALTER COLUMN. There are restrictions like:
- the column may not be certain data type
- implicit conversion must exist
- it may not be part of a constraint and so on

Now the bigint isn't implicitly convertible to float (AFAIK) so this could be the cause to your problem. Recreating the column would be the solution as posted by phil.o.

Posted 11-May-12 12:24pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2012-05-10T22:07:00

You cannot change the datatype of your column directly. You have to do it in several stages :

1- Create another column with the correct datatype

SQL

ALTER TABLE <yourtable> ADD COLUMN newcolumn float

2- Copy the data from the first column to the new one

SQL

UPDATE <yourtable> SET newcolumn = CONVERT(amount, float)

(make sure there are not any value in amount column that exceeds the float datatype boudaries)

3- Delete the amount column

SQL

ALTER TABLE <yourtable> DELETE COLUMN amount

4- Rename the mycolumn to amount (you'll have to search for this - there is a stored procedure, but I can't remember its name)

Hope this helps.

jdarknight · Accepted Answer · 2012-05-10T21:51:00

Solution 1

can u post the error. so that problem can be much more visible.

Posted 10-May-12 21:51pm

jdarknight

Comments

Meenakshi_V 11-May-12 3:53am

cannot modify table....