SQL Alter Column in table From Bit to Int

Question

So What I am looking to do is alter a table and change a column from an BIT to INT. Although currently in the column there are null values I would like to change to -1 and not allow null. Since BIT only holds 0 and 1, how would I Alter the Column to an INT and Set the Null values to -1

I was thinking something along the lines of

ALTER TABLE TABLENAME
ALTER COLUMN COLUMNAME INT SET DEFAULT -1
WHEN NULL THEN CAST(-1 AS INT)

Answer 1

Hope the following code snippet will help you out.

--Created table for testing
CREATE TABLE Test(COl Bit)

--Insertint values
INSERT INTO Test VALUES(0)
INSERT INTO Test VALUES(NULL)
INSERT INTO Test VALUES(1)

--Change the column type
ALTER TABLE Test ALTER COLUMN COL INT

-- Update null values to -1
UPDATE Test SET COL = ISNULL(COL,-1) WHERE COL is NULL

-- Changing the column to not null
ALTER TABLE Test ALTER COLUMN COL int NOT NULL

Answer 2

proper Syntax is:

ALTER TABLE {TABLENAME} 
ALTER {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Do it step by step as shown below

create table test2(id int not null identity primary key ,
 col bit);


ALTER TABLE test2
ALTER COLUMN col INT NULL --Alter to change the type to INT

alter table test2
add constraint dflt default -1 for col  --Alter to create a default value