Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1304
  • Last Modified:

Change column from varchar to int?

I have a table in a database that has a column defined as varchar 12.  I attempted to use the sum function in isql and it failed.  

Server: Msg 409, Level 16, State 2, Line 2
The sum or average aggregate operation cannot take a varchar data type as an argument.


Does this have to be INT or something else?  Is it possible to change the column now that it has been created and populated?  It only has numbers in it.  How can I go about getting around this?

Thanks.
0
robrandon
Asked:
robrandon
2 Solutions
 
adatheladCommented:
SUM will only be able to work on numerical data such as INT.

If all the values in the column are integers, then yes you can easily change it. You should just be able to go into design view in Enterprise Manager and change it from a VARCHAR to INT.

If if you want to keep them as varchar, try:
SELECT SUM(CAST(YourField AS INTEGER))
 but really, if they will always hold numerical data it should be an appropriate datatype
0
 
john_reddinCommented:
Hello,
   A mathamatical function can only be performed on a numeric field.   You could either change the data type or use a Convert/Cast function.

The convert syntax is convert(int, <column_name>)  
   - replace the angle brackets and column_name with the correct value
   - This works only if the values within the column remain numeric.


To convert the data type to an int then you can do the following.

Alter table <table_name> alter column <column_name> int

   -This is a more perminant solution, and assumes the values within
    <column_name> are  between -2^31 (-2,147,483,648) and
     2^31 - 1 (2,147,483,647).

Regards
   John
0
 
robrandonAuthor Commented:
Awesome.  Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now