1

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

Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28

1 Answers1

2

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • @JohnCappelletti, right, I would expect the string conversion to bit to work either implicitly or explicitly using `CAST` or `CONVERT`. – Dan Guzman Jun 28 '18 at 23:46