Is there a way to get a boolean without casting in SQL Server?

I've found it very weird that simple code like this is not valid:

select * from table where field=true

The alternative apparently being

select * from table where field='true'

Ok, guess I can live with that. For one reason or another I needed to do something like this recently:

select true as somefield,...

The alternative to get types and everything right was much more ugly though:

select cast('true' as bit) as somefield,...

Am I missing something? Is there actually some built in way to get a true or false value as a boolean without casting?

Bits are the datatype most commonly used to represent a boolean in T-SQL. I typically do something like this:

select CAST(1 as bit) as somefield

The values 'TRUE' and 'FALSE' are special strings that can be implicitly converted to variables of type bit. This extract is from the MSDN documentation for bit.

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

Note that these values appear to be case insensitive.

This is why the where clause in your second code snippet works (I assume that field is defined as a bit).

select * from table where field='true'

Without specifying a target type of bit in any way, 'TRUE' and 'FALSE' are no longer treated as special values and remain simple string values. This is why the cast was required in your third snippet.

select cast('true' as bit) as somefield,...

MSDN states that bit literals (or constants as they are referred to there) are the numbers 0 and 1.

bit constants are represented by the numbers 0 or 1, and are not enclosed in quotation marks. If a number larger than one is used, it is converted to one.

This information may help in some cases but be aware that the literal values 0 and 1 are interpreted as ints rather than bits. The following statements both return 'int' which demonstrates this.

select sql_variant_property(0, 'BaseType')
select sql_variant_property(1, 'BaseType')

Sql Server doesn't have a boolean datatype you can store in a table, but it does contain a bit data type you can store.

However, the true keyword you see IS a boolean value. Sql Server only uses the boolean data type for results for its Comparison Operators, which can return three values, TRUE, FALSE, and UNKNOWN.

It is important to know the distinction between the two.

SELECT *
FROM Table
WHERE Field = 1 --Field = true

To store boolean values in a table, use the bit datatype. Use 1 for true, 0 for false, and Null for unknown.

I want to add to all current answers only one more method to get a boolean without casting in SQL Server:

DECLARE @myTrue bit = 1;
-- or two lines for old version of SQL Server
-- DECLARE @myTrue bit;
-- SET @myTrue = 1;

SELECT @myTrue AS somefield

Use 1 or 0, assuming your field is of type bit.

select * from table where field=1

select * from table where field=0

Not really an answer but...

Just ran into an issue where I was forced to use BIT conversion. Well, 'forced' is a 'bit' strong a word here... Instead I preferred to use a BIT conversion.

I built a SQL query inside of a c# method, using a case statement like:

case when foo = faa then 1 else 0 end as outcome

and when the results were feed to a linq query, the resulting type resolved to INT32. So this failed:

outcome = s.Field<bool>("outcome")

I wanted the result to resolve to a BIT so instead I used:

cast(case when foo = faa then 1 else 0 end as bit) as outcome

Point being, while TSQL might assume it is a BIT, instead ADO did not.

Overall I guess I'm saying, don't rely on the framework (.NET or TSQL) to do yer dirty work... If you want a BIT, cast it as a BIT :)

CASE WHEN ISNULL(Attribute1,'0')='1' THEN 'true' Else 'false' End Attribute1