CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012booleanbit
Laughing Vergil
Laughing Vergil

Reputation: 3756

SQL Server - Bit data type matches String 'True' and 'False'

I'm not sure if something special has been done with the database that I am working with, but while optimizing old code, I came across the following (sanitized) query:

SELECT Code
FROM GovernmentThing
WHERE IsGovernment = 'True'

I checked the data type for IsGovernment, assuming that it was a varchar(5) or something similar, only to discover that it was a bit field.

I then assumed that the code was bad, and checked by running a query returning the IsGovernment field. To my great surprise, I discovered that the query was returning only rows where IsGovernment was set to 1! Since I then wondered what a check against a string literal 'False' would return, I tested, just to find that only zero values were returned!!

It is possible that I missed something somewhere in the TSQL updates, or that there is some tricky configuration that makes this work, but... I've never heard of this before.

Can someone please enlighten me - is this documented somewhere, or ???

SQL Server 2012

Upvotes: 1

Views: 693

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Here's the excerpt from the bit data type documentation that describes this behavior:

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Upvotes: 2

Related Questions