Booleans in SQL Server:


I want to query a SQL Server database with my VB6 program, using an SQL statement. I want to make a selection on a boolean (WHERE myBoolean = TRUE), but this statement always gives me a syntax error. I guess I need to add some kind of brackets or something in order to show SQL it's a boolean value, like ' ' for strings, { } for dates, ... Can you help me out? Been looking all over the Web for an answer.

Does your database already exist? What is the datatype of the myBoolean column? The reason I ask is because SQL Server does not support a boolean datatype. The closest it comes is the BIT datatype, although most people use TINYINT, SMALLINT, or INTEGER instead, because these integer datatypes offer more flexibility when constructing queries. Consider the following SQL, which creates and populates a table with a BIT column: create table test_bit ( id tinyint not null identity , yesno bit not null ) insert into test_bit (yesno) values (1) insert into test_bit (yesno) values (1) insert into test_bit (yesno) values (0) insert into test_bit (yesno) values (1) insert into test_bit (yesno) values (0) select * from test_bit id yesno ---- ----- 1 1 2 1 3 0 4 1 5 0 So far, so good, right? Okay, now try a query: select sum(yesno) from test_bit Server: Msg 409, Level 16, State 2, Line 1 The sum or average aggregate operation cannot take a bit data type as an argument. Well, that sort of makes sense, if you accept that BIT is not actually a numeric datatype, even though you must specify values for it using 1 and 0. Try another query: select yesno, count(*) from test_bit group by yesno Server: Msg 252, Level 16, State 2, Line 1 Cannot group by a bit column. That makes no sense to me. That is, I understand what the error means, but not why SQL Server won't let you do it. You can get around these limitations by using CAST(yesno as INTEGER) instead. But then why not define the column using INTEGER to begin with? The only time BIT might make sense is if you were storing a yes/no attribute in a tremendously large table and were concerned about total table size.