CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
bsivel
bsivel

Reputation: 2949

Querying Sql Server 2005 with bit data type column

This doesn't work with sql server 2005

select * from [mytablename] where [mybitcolumn] <> 1

and I had to use...

select * from [mytablename] where (([mybitcolumn] is null) or ([mybitcolumn]=0))

Is there alternative syntax that that would get rows where mybitcolum <> 1?

Upvotes: 0

Views: 772

Answers (2)

Yuck
Yuck

Reputation: 50855

You could do this:

select * from [mytablename] where isnull([mybitcolumn], 0) = 0

Although it's only marginally better.

Alternatively, and I'm hesitant to suggest it, you turn off ANSI NULLS. For instance:

set ansi_nulls on
go

if (1 != null)
    print 'This will not print equal because NULL is a third boolean value.'

set ansi_nulls off
go

if (1 != null)
    print 'Not equal since we told SQL Server to treat null as any other value'

Also have a look at Three-valued logic.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Since 1 is considered as true; With where [mybitcolumn] <> 1 you are trying to get false scenario. what if you try like below

select * from [mytablename] where [mybitcolumn] = 'false'

If in case mybitcolumn contains NULL then you can use ISNULL function

select * from [mytablename] where ISNULL([mybitcolumn],0) = 'false'

Upvotes: 1

Related Questions