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 :-)


About Peter Lalovsky

I am Microsoft SQL Server certified professional, creating with T-SQL, SSRS, SSIS, ASP.NET/C#, Azure, Python, PowerShell and more on a daily basis since year 2006. In 2016 i wrote a book for beginner and intermediate T-SQL programmers which you can download here. This blog is something like my personal programming documentation. When i am not in front of a computer, i am around my paper car – Trabant 601.

Leave a comment

Your email address will not be published.