locked
How to create a BIT datatype column in a table RRS feed

  • Question

  • Hi Friends

    I want to create a table with a column datatype Boolean (1=Yes, 0=No, Null=Not Known)

    how to write a create query for this, how does sql understand that 1=Yes, 0=No, Null=Not Known


    Royal Thomas

    Wednesday, December 5, 2012 4:54 PM

Answers

  • Yes it is.

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    • Marked as answer by Royal Thomas Wednesday, December 5, 2012 5:26 PM
    Wednesday, December 5, 2012 5:18 PM
  • Here is an exampl:

    declare @test table (a bit);
    insert into @test
    select null union all select 0 union all select 1
    ;
    select
      a,
      coalesce(substring('YesNo', 4 - 3*a, 3), 'Not Known'
      ) as logical_Value
    from @test;
    /* -------- Output: --------
    a     logical_Value
    ----- -------------
    NULL  Not Known
    0     No
    1     Yes
    */

    • Marked as answer by Kent Waldrop Friday, December 14, 2012 6:45 PM
    Wednesday, December 5, 2012 5:27 PM

All replies

  • select *, cast(null as bit) as [BitColumn]

    from myTable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, December 5, 2012 4:56 PM
  • I'm asking how to create a column in a table with BIT type

    is it just like 

    create table tabl_name

    (

    column0 varchar (10),

    column1 BIT

    )


    Royal Thomas

    Wednesday, December 5, 2012 5:14 PM
  • Yes it is.

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    • Marked as answer by Royal Thomas Wednesday, December 5, 2012 5:26 PM
    Wednesday, December 5, 2012 5:18 PM
  • Here is an exampl:

    declare @test table (a bit);
    insert into @test
    select null union all select 0 union all select 1
    ;
    select
      a,
      coalesce(substring('YesNo', 4 - 3*a, 3), 'Not Known'
      ) as logical_Value
    from @test;
    /* -------- Output: --------
    a     logical_Value
    ----- -------------
    NULL  Not Known
    0     No
    1     Yes
    */

    • Marked as answer by Kent Waldrop Friday, December 14, 2012 6:45 PM
    Wednesday, December 5, 2012 5:27 PM
  • Yes.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Pieter Geerkens Wednesday, December 5, 2012 5:31 PM
    Wednesday, December 5, 2012 5:27 PM
  • Here is a few thousand words on why we do not ever use bit flags in SQL.  READ IT before you code again. 

    http://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, December 5, 2012 8:31 PM