31

I have a Table in SQL Server 2000 with BitValue Column. But, it is being displayed as True/False in SQL Server Management Studio. When I do a Select * from Tablename it returns the BitValue Column values as True/False. How do I force it to return the value as bits (1/0) instead of True/False? Any Help will be really appreciated?

Butters
  • 947
  • 5
  • 16
  • 25
  • 1
    Why are you concerned about the display format of a boolean value in SSMS? – Tim Schmelter Jul 03 '13 at 15:00
  • I'm using those values, doing some work and inserting the values again to that table. But I cannot insert the values as True/False because it throws an error saying something like bit value only accepts 1 or 0. – Butters Jul 03 '13 at 15:02
  • 1
    `True` is just how SSMS displays a column of type `bit` with value `1`. A C# client would receive a value `SqlBoolean.True`. – Andomar Jul 03 '13 at 15:04
  • Possible duplicate [TSQL to return NO or YES instead TRUE or FALSE](http://stackoverflow.com/a/8051839/1563878). I think that Sql Server not returns TRUE or FALSE, please see this [reference](http://msdn.microsoft.com/en-us/library/ms177603.aspx). – Gaston Flores Jul 03 '13 at 15:04
  • This Fails: Update Table Set BitField=True Where ID=1 But this works: Update Table Set BitField=1 Where ID=1 – Butters Jul 03 '13 at 15:04

5 Answers5

42

Try with this script, maybe will be useful:

SELECT CAST('TRUE' as bit) -- RETURN 1
SELECT CAST('FALSE' as bit) --RETURN 0

Anyway I always would use a value of 1 or 0 (not TRUE or FALSE). Following your example, the update script would be:

Update Table Set BitField=CAST('TRUE' as bit) Where ID=1
Gaston Flores
  • 2,457
  • 3
  • 23
  • 42
23

Modify your query to generate the output that you want.

Try casting them to int:

select cast(bitFlag as int)

Or, if you like, use case:

select (case when bitFlag = 0 then 0 else 1 end)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
6

This can be changed to 0/1 through using CASE WHEN like this example:

SELECT 
 CASE WHEN SchemaName.TableName.BitFieldName = 'true' THEN 1 ELSE 0 END AS 'bit Value' 
 FROM SchemaName.TableName
Ashraf Sada
  • 4,527
  • 2
  • 44
  • 48
-1

just you pass this things in your select query. using CASE

SELECT
    CASE
        WHEN gender=0 THEN 'Female' 
        WHEN gender=1 THEN 'Male'
    END 
as Gendership from Tablename;
Vishal Parmar
  • 524
  • 7
  • 27
-1
 Try this:- SELECT Case WHEN COLUMNNAME=0 THEN 'sex'
              ELSE WHEN COLUMNNAME=1 THEN 'Female' END AS YOURGRIDCOLUMNNAME FROM YOURTABLENAME

in your query for only true or false column

Dipak Rathod
  • 129
  • 13