Convert bit column to integer

I am converting bit columns of a particular table to integer through an SQL script (this table has some default constraints for default value).

I have to alter the columns for the table, not runtime casting, What script can be used to accomplish this?

Answers


Try using CAST(columnName AS INT) AS IntValue.

e.g.

SELECT columnName, CAST(columnName AS INT) AS IntValue
FROM table

OR you can use CONVERT(INT, columnName) AS IntValue.

UPDATE: If you need to alter the actual metadata of the table, then you first need to drop the constraints then alter the column:

i.e.

ALTER TABLE [Table] DROP CONSTRAINT [ConstraintName];
GO
ALTER TABLE [Table] ALTER COLUMN [ColumnName] INT;

Then recreate any constraints that you need.


If you are concerned about changing the datatype of the column you can use an ALTER query as follows.

ALTER TableName ALTER COLUMN ColumnName INT

Else, only for display purposes, you can use either the CAST or CONVERT function:

CAST(columnName AS INT) AS IntegerVal
CONVERT(int, columnName) AS IntValue

SELECT CONVERT(BIT,'False') AS test1 SELECT CONVERT(BIT,'True') AS test2


We can't simply alter the BIT column to INT. So I suggest to create the new integer column in the table and then using the CAST, update the new integer column with the existing bit values. Then at last you can drop the BIT column from the table.


Finally I managed to get it working:

ALTER TABLE tblname DROP CONSTRAINT DF_tbl_tblname_tblcol
ALTER TABLE tblname ALTER COLUMN tblcol int not null
ALTER TABLE tblname WITH NOCHECK ADD CONSTRAINT [DF_tbl_tblname_tblcol] DEFAULT (0) FOR tblcol

I have used the above SQL statements to alter the table column along with its constructor.


I believe you could extract the bit column as an integer by just using standard CAST() command:

SELECT
  CAS(Bit_Column AS int) AS Int_Column
FROM
  YourTable

However, I'm not sure I understand what you're trying to achieve, probably you could get a better answer if you provided more details, such as the structure of the table and its constraints.


You are getting an error related to a default constraint. Thus you have to drop that constraint before altering the column data type...

Try this to find all the constraints based on this table (you just need to find the proper default on your column) or use SQL Server Management Studio (SSMS) to generate the script for the table, and this will have the default constraint defintion.

select * from sys.all_objects where parent_object_id  = object_id('<tablename>')
go

Then first drop the constraint and then alter the column and add the default again.

alter table bittoint drop constraint DF__bittoint__col2__45D500F0
go
alter table bittoint alter column col2 int
go
alter table bittoint add constraint DF__bittoint__col2__45D500F0 default 0 for col2
go

Need Your Help

How do you draw transparent polygons with Python?

python-imaging-library polygon alpha

I'm using PIL (Python Imaging Library). I'd like to draw transparent polygons. It seems that specifying a fill color that includes alpha level does not work. Are their workarounds?

How can I make the xtick labels of a plot be simple drawings using matplotlib?

python drawing plot matplotlib labels

Instead of words or numbers being the tick labels of the x axis, I want to draw a simple drawing (made of lines and circles) as the label for each x tick. Is this possible? If so, what is the best ...