Best way to convert INT to BIT

Question

I have column type of INT, I want to select 1 if it contains 1 and 0 otherwise. I know only way to do it using CASE:

CASE WHEN val=1 THEN 1 ELSE 0

What other approaches there is to achieve the same result?

Answer 1

SQL SERVER 2012:

SELECT CAST(IIF ( field = 1, 1, 0 ) AS BIT) FROM table

Otherwise:

SELECT CAST(CASE field WHEN 1 THEN 1 ELSE 0 END AS BIT) From table

Answer 2

All you have to do is cast/convert the int to a bit.

DECLARE
     @val INT = 42;
    ,@bitVal BIT;

SET @bitVal = CAST(@val AS BIT); -- equals 1

Any non-zero number will be converted to true.

https://technet.microsoft.com/en-us/library/ms191530(v=sql.105).aspx#_bit

Answer 3

To convert is simple, as long as it is 1 or 0 you can just assign it, no need to cast anything

you do the same when you declare a variable right?

@declare @myBit bit =1;

however when you want to do something more useful with the integer like bitmap comparison things get more interesting. you can compare two integers and return a bit based on the way the defined.
Let me try and visualise

    00000001=1
    00000010=2
    00000011=3
==============
    FFFFFFTT

so if you have a 1 the value becomes True,

Actually it is a bitmap comparison with 2 values is like mapping a raster, you can have several or even group them using 0+1 at a byte level.

Above you can see that 1 "is in" 3 as well as that 2 "is in" 3

look at the TSQL unit test example below

declare @notSet     int =0
      , @CanView    int =1 
      , @CanEdit    int =2 
      , @CanSubmit  int =4 
      , @CanApprove int =8
      , @CanDelete  int =16; 

declare @contributor int = @CanView | @CanEdit | @CanSubmit     --> Can't delete
       , @moderator  int = @CanView | @CanEdit | @CanDelete     --> not allowed to sumbit
       , @admin      int = @CanView | @CanEdit | @CanSubmit | @CanApprove | @CanDelete;--> can do all

SELECT TEST='A admin can Submit'            , RESULT= iif(@admin     & @CanSubmit = @CanSubmit,'TRUE','FALSE')
UNION ALL
SELECT TEST='A Moderator may not Submit'    , RESULT= iif(@moderator & @CanSubmit = @CanSubmit,'FALSE','TRUE')
UNION ALL
SELECT TEST='A Contributer may not delete'  , RESULT= iif(@contributor & @CanDelete = @CanDelete,'FALSE','TRUE')
UNION ALL
SELECT TEST='A Moderator may delete'        , RESULT= iif(@moderator & @CanDelete = @CanDelete,'TRUE','FALSE')

You can combine the values together using the bit operator | like this 1|2 = 3, and 1|1 =1, do not mix up "|" with a "+" here as it will not always work well ;-)

An example, the bug would be @CanView + @CanView would be @CanEdit,

when you expect @CanView | @canView will still be @canView

Try it in SQL

SELECT (1|1), (1+1) 

Below some C#, Hope it helps those that like to save and work with enums in code and database.

Say you have an Enum and a class like this: [Flags] public enum Rights { notSet =0 , CanView =1 , CanEdit =2 , CanSubmit =4 , CanApprove =8 , CanDelete =16 }

public class User
{
  public Rights Permission {get;set}
}

...
// user can change his own posts
var user = new User();
user.Permission = Rights.CanView | Rights.CanEdit | Rights.CanDelete ;

You can now store the permission in the database and read the value using one of the below methods where you compare one or compare several

var result = user.Permission & Rights.CanView  == Rights.CanView;
var canChange = ((user.Permission & (Rights.CanView  | Rights.CanEdit | Rights.CanDelete)) != 0);

So, Round Tripped to the database and back, hope you have what you are looking for

Happy coding,

Walter