T-SQL Hint: What are the values that we can insert into BIT column


I met a code that CONVERT(BIT, Value) and insert into BIT column. Let’s see what are the possible values.

DROP TABLE IF EXISTS #Bit;
GO

CREATE TABLE #Bit ([BIT] BIT, Inserted VARCHAR(128));
GO

INSERT #Bit ([BIT], Inserted)
SELECT 0, '0'
UNION ALL SELECT NULL, 'NULL'
--UNION ALL SELECT CONVERT(BIT, 'Yes'), 'CONVERT(BIT, ''Yes'')'
--UNION ALL SELECT CONVERT(BIT, 'No'), 'CONVERT(BIT, ''No'')'
UNION ALL SELECT CONVERT(BIT, GETDATE()), 'CONVERT(BIT, GETDATE())'
UNION ALL SELECT 1, '1'
UNION ALL SELECT 2, '2'
UNION ALL SELECT 3, '3'
UNION ALL SELECT 3.14, '3.14';
GO

SELECT * FROM #Bit;
GO

The possible values are NULL, 0 and 1 and conversion is not needed.

Keep it simple :-)