Bit fields in T-SQL Queries?

I'm creating a query that filters on a bit field that I'm using to represent a boolean value. My query looks something like this:

SELECT * FROM MYTABLE WHERE ACTIVEORDER = 1

And that query works okay--I get all records where 'active order' is true.

Here's my problem--I'd like to make this query more readable by using TRUE or FALSE, instead of 1 or 0. I thought that I had read in BOL that T-SQL can convert the string values TRUE and FALSE to 1 and 0. But I can't get that to work in my query.

Is there a way to use TRUE and FALSE as the filter value for a bit field in a T-SQL query, rather than 1 and o? If so, what does the query look like? Thanks.

AFAIR no, it's not possible. Moreover, I highly recommend you not to mix different languages - it is only confusing. In other words, your query may look more readable for you, but it may look completely enigmatic for another one at the same time.|||Thanks--that makes sense. Just for my own curiousity, any idea why T-SQL doesn't have a boolean type?|||Why, it has - a bit type is perfectly suitable for such a role
For example, there may be some strictly defined things in ANSI SQL which MSFT has to follow. Also, the SEQUEL language (ancestor for current SQL) was developed in the past when every byte in the listing was thrice-counted, so no wonder they decided to use short 1/0 instead of literal constants.|||

--Sure there's a way to make it more readable.

--Just put this at the top of the query:

declare @.True bit,@.False bit

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

--Sorry, I fat-fingered this query. The below is corrected:

declare @.True bit,@.False bit

select @.True = 1, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

So, this works when BOTH True and False are equal to zero?

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

Ennor wrote:

there may be some strictly defined things in ANSI SQL which MSFT has to follow.

None of the SQL Standard says BOOLEAN is one of the datatype in your database.

It says,

1. The value True/False are reserved words as per ANSI Standard

2. Boolean is not a valid datatype for your Table/SQL quries

3. Boolean/Truth values should be TRUE | FALSE | UNKNOWN

4. You can't use the TRUE/FALSE on your query.

|||

You can try

select case when activeorder=1 then 'TRUE'

case when activeorder=0 then FALSE'

end

from mytable

|||

Sorry, I fat fingered that one:

select @.True = 1, @.False = 0