Boolean bit field Which is True False

I have a field that I need to query for true and false.
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:

> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:

> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>