alter table add bit column as case when

Question

Hello I am adding a new column to my table

  ALTER TABLE MyTable ADD  Active  AS    CASE WHEN EndDate < getdate() then 0 ELSE 1 END

How can I set the column to be a bit? because by default it is an int32. What I tried is to use this

ALTER TABLE MyTable ADD  Active BIT AS    CASE WHEN EndDate < getdate() then 0 ELSE 1 END

because of some stackoverflow answers. However it seems is not a good aproach what I am doing.

Answer 1

Cast it to BIT:

CREATE TABLE dbo.MyTable
(
    EndDate datetime2
);

ALTER TABLE MyTable 
ADD  Active AS  CAST(CASE WHEN EndDate < getdate() then 0 ELSE 1 END AS BIT)

SELECT c.name
      ,t.[name]
FROM sys.[columns] c
INNER JOIN sys.[types] t
    ON c.[system_type_id] = t.[system_type_id]
WHERE object_id = OBJECT_ID ('dbo.MyTable')

DROP TABLE dbo.MyTable;