3

Update:

No one doesn't seem to understand my question.

In Return Bit Value as 1/0 and NOT True/False in SQL Server, the person says

I have a Table in SQL Server 2000 with BitValue Column. But, it is being displayed as True/False in SQL Server Management Studio.

In How do you create a yes/no boolean field in SQL server?, a user says

In SQL you use 0 and 1 to set a bit field (just as a yes/no field in Access). In Management Studio it displays as a false/true value (at least in recent versions).

All these seem to indicate Management Studio displays bit as a false/true value, ie. select cast(1 as bit) will display as true.

But it doesn't happen for me.

Does anyone know why?

select cast(1 as bit) returns 1, not true

Gqqnbig
  • 5,845
  • 10
  • 45
  • 86
  • Database does not have boolean data type. `BIT`is the closest data type to boolean. – Eric Aug 01 '17 at 17:28
  • 1
    you will have to use case expression – TheGameiswar Aug 01 '17 at 17:31
  • 2
    On the contrary....each person that has posted a response has answered your question exactly as you have asked it. It seems that perhaps your question is something else entirely. What is your actual question?? – Sean Lange Aug 01 '17 at 18:11
  • @SeanLange The initial post conveys the same meaning as the current one, but It's my bad that I didn't word it clearly. Current question is my actual question. – Gqqnbig Aug 01 '17 at 18:13
  • 2
    Your "current" question is still "Why does sql server not display a bit as True/False" and every answer posted here addresses that exact question. – Sean Lange Aug 01 '17 at 18:16
  • @SeanLange there is still some gap in understanding. I want to know "Is SQL Management Studio expected to automatically display bit as true/false without manually conversion?" – Gqqnbig Aug 01 '17 at 18:20
  • 1
    NO. As explained quite clearly by Dan Guzman below it is NOT true or false. It isn't a two valued datatype. It can be 1, 0 or NULL. How you treat those values is entirely up to you. It is simply that a bit can only contain any of those values. Typically 1 means True and 0 means False but sql server doesn't have a boolean datatype. – Sean Lange Aug 01 '17 at 18:24
  • @SeanLange I totally agree with you. How could [Guffa](https://stackoverflow.com/a/1777277/746461) say "Management Studio it **displays as a false/true value** (at least in recent versions)"? How could [Butters](https://stackoverflow.com/questions/17451413/return-bit-value-as-1-0-and-not-true-false-in-sql-server) experience the issue that "I have a Table in SQL Server 2000 with BitValue Column. But, **it is being displayed as True/False in SQL Server Management Studio**."? – Gqqnbig Aug 01 '17 at 18:28
  • 1
    I sort of vaguely remember they tried to get tricky with SSMS in 2005 and displayed the string True/False. But that was only in the application, the actual values were still 1 and 0. Then in the first update they made that go away. And the sql 2000 one is totally irrelevant today, the DBMS has grown so much since then. In 2000 there wasnt even SSMS yet, it was still query analyzer. – Sean Lange Aug 01 '17 at 18:43
  • @SeanLange Thanks for the clarification! I understand bit is stored as 1/0. Correct me if I'm wrong, so the displaying behavior only exists in early versions of SSMS. In current version, at least 2014, SSMS no longer does the implicit conversion and display bit as 1/0. – Gqqnbig Aug 01 '17 at 18:48
  • 1
    This is in the edit table window not the query result window IIRC. If you right click the table and choose Edit Top 200 rows it displays as true and false. – Martin Smith Aug 01 '17 at 18:49
  • 1
    @MartinSmith no wonder I didn't know....I heard people actually use that window. :) – Sean Lange Aug 01 '17 at 18:49
  • @MartinSmith YOU ARE THE HERO!! Thanks so much!!!! – Gqqnbig Aug 01 '17 at 18:53

5 Answers5

4

This is a feature of the visual query designer results pane.

Not the general SSMS results grid.

The query designer is encountered for example when using the UI to create a view or selecting the "Edit Top 200 rows" shortcut menu option on a table.

enter image description here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

A bit could represent Yes/No, True/False, On/Off, IsSomething, etc. SSMS doesn't know the business meaning behind the bit value so it displays NULL, 0, or 1.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
2

https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql

then you can use case to return what you want

CASE WHEN <column> = 1 THEN 'TRUE' ELSE 'FALSE' END AS <alias>

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

Thiago Loureiro
  • 1,041
  • 13
  • 24
1

You must to use CASE expression...

select cast('false' as bit) --> this return 0

but you can use a CASE WHEN expression...

SELECT 
       CASE WHEN (value = 1) THEN 'true'
       ELSE 'false'
       END

"value" will be your "bit" field.

Hope this help!

M84
  • 727
  • 6
  • 14
1

That is display logic which you are responsible for. The value is stored as a 0 or 1, how you display it is up to your / your query. You could use CASE but you can also use IIF. 

SELECT IIF(YourField = 1, 'True', 'False') AS MyBoolean
FROM YourTable

If you want to return true/false you are actually returning a string/varchar in the query. If this is for an application I recommend doing the conversion in the presentation layer, as late as possible in the call stack, and not in the query.

Igor
  • 60,821
  • 10
  • 100
  • 175