Menu
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?
853
Try using CAST(columnName AS INT) AS IntValue . e.g. OR you can use CONVERT(INT, columnName) AS IntValue .
bit columns hold either 0 or 1. Integer values other than 0 or 1 are accepted, but are always interpreted as 1. Storage size is 1 byte. Multiple bit datatypes in a table are collected into bytes. For example, 7 bit columns fit into 1 byte; 9 bit columns take 2 bytes.
Also, the default for columns of BIT data type is to not allow NULL values; you can change this by explicitly defining the column as allowing NULL values.
SQL Server bit data type is an integer data type that can take only one of these values: 0, 1, NULL. With regard to the storage, if there are less than 9 columns of the bit data in the table, they are stored as 1 byte. If there are 9 to 16 such columns, they consume 2 bytes and so on.
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.
122
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
