Return Bit Value as 1/0 and Not True/False in SQL Server

Is SQL Management Studio expected to automatically display bit as true/false without manually conversion?

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.

bit data type returns -1

When you get the data for a bit column, you are actually getting a 32-bit number, not just a single bit. In that 32-bit number, either EVERY bit is set to 1 or every bit is set to 0. The value 11111111 11111111 11111111 11111111 when converted to an Integer using two's complement is -1, because the first bit is the sign bit.

The problem is that you should not be using Integer in your VB code. The ONLY time you should be using 1 or 0 to represent this data is literal values in SQL code. In your VB code, you should be using Boolean to represent this data because that what the data is. 1 represents True and 0 represents False and you will see that if you view the data in SQL Server Management Studio.

That means that here:

sqlCmd.CommandText = "SELECT myColumn FROM myTable"

sqlRdr = sqlCmd.ExecuteReader()

While sqlRdr.Read
      myColumn = sqlRdr(0)
End While

that myColumn variable should be type Boolean. That code should not compile at all anyway because it requires Option Strict Off and you should pretty much always have Option Strict On. You should not be assigning the Object reference you get from the data reader to a variable of type Integer or Boolean. You should use the data reader to get the data as it's actual type. If you really were getting an Integer:

myColumn = sqlRdr.GetInt32(0)

but, as you will doing it properly and using Boolean:

myColumn = sqlRdr.GetBoolean(0)

If you ever wanted to save data then you would generally be using parameters, e.g.

sqlCmd.CommandText = "UPDATE myTable SET myColumn = @myColumn"
sqlCmd.Parameters.Add("@myColumn", SqlDbType.Bit).Value = True
sqlCmd.ExecuteNonQuery()

How to select BIT columns as TRUE

BIT values are 1/0 and they correspond to TRUE/FALSE accordingly.

By the comments I assume you want to view TRUE / FALSE when selecting this column, so you can simply use a CASE EXPRESSION for this:

SELECT <Column1>,<Column2>...,
       CASE WHEN IsActive = 1 THEN 'TRUE' ELSE 'FALSE' END as IsActive
FROM YourTable

If IsActive = 1 - display TRUE , else , display FALSE.

Imply bit with constant 1 or 0 in SQL Server

cast (
  case
    when FC.CourseId is not null then 1 else 0
  end
as bit)

The CAST spec is "CAST (expression AS type)". The CASE is an expression in this context.

If you have multiple such expressions, I'd declare bit vars @true and @false and use them. Or use UDFs if you really wanted...

DECLARE @True bit, @False bit;
SELECT @True = 1, @False = 0;  --can be combined with declare in SQL 2008

SELECT
    case when FC.CourseId is not null then @True ELSE @False END AS ...

How to check if bit variable is true or false in sql server?

A bit variable in SQL Server can have three values. 0, 1 and NULL.

The strings 'true' and 'false' map to 1 and 0 respectively.

Your code does not take account of the third possible value. If mytable is empty then the variable will not be initialised and have the value NULL.

SELECT CASE @CappedIFCheck
         WHEN 'True' THEN 'true'
         WHEN 'False' THEN 'false'
         ELSE 'unknown'
       END 

I'm not sure exactly what your code is trying to do but that is a very inefficient way of going about things. You should use EXISTS instead.

sql boolean 0/1 To Represent true or False

Try to use case

select case when col = 1 then 'true'
            when col = 0 then 'false'
       else 'NN'
       end as val  

Laravel boolean returns 1/0 instead of true/false in query

When you're defining the following:

protected $casts = [
  'status' => 'boolean',
];

You're defining it on the model. However, when you initiate your query using DB::table(), you're not using the model, so the binding doesn't actually apply. Initiate your query using your model:

return Visit::leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'dbo.visits.id')
->select('R.id','R.status'); // status is the boolean

Note: Had to adjust query to dbo.visits.id from V.id due to aliasing not being available at that point.

TSQL to return NO or YES instead TRUE or FALSE

If varchar or bit, handling NULLs

case
    when BASIC = 'FALSE' then 'NO'
    when BASIC <> 'FALSE' then 'YES'
    else 'UNDEFINED'
end

or if just bit

case
    when BASIC = 1 then 'YES'
    when BASIC = 0 then 'NO'
    else 'UNDEFINED'
end

Edit:

SELECT 
    TipoImovel_Id AS TII_SEQ,
    Descricao AS TII_DSC, 
    Sigla AS TII_DSC_SIGLA,
    -- choose which one you want from the answers here
    case
        when BASIC = 1 then 'YES'
        when BASIC = 0 then 'NO'
        else 'UNDEFINED'
    end AS SomeColumnName
FROM San_TipoImovel";

---

Related Topics