none
Creating a Yes/No field in SQL Server

    Question

  • hi,

    i need to create a yes/no field in a SQL Server database table. I know that i could previously do this in Microsoft Access and i believe that SQL Server now uses Boolean(0 or 1) for this.  Does anyone have any ideas as to how to convert these 0 or 1 to yes or no with a default of yes?

    I am taking the output from this table and displaying in Excel as yes or no not 0 or 1.

    Thanks

    Monday, July 03, 2006 5:21 AM

Answers

  • If you want yes/no you either have to transform the data at the SQL Server side first, converting the data into VARCHAR columns with the static text yes/no or you have to use a expression in Excel which evaluates the value and displays the appopiate text.

    The default value can be change by putting a default constraint on a table, like:

    ALTER TABLE TableName
    ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR ColumnName


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Monday, July 03, 2006 7:10 AM

All replies

  • If you are using a SQL Server BIT datatype and you import data into excel, the provider will tell Excel to convert this into Yes / No Value, or the appropiate language equivalent for the machine.

    HTH, Jens Süßmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, July 03, 2006 6:31 AM
  • hi,

     

    thanks for the reply...., excel converts this into true/false instead of yes/no.....can i amend this?

     

    also can i change the default value in sql server from 0 to 1?

     

    thanks

     

     

    Monday, July 03, 2006 7:01 AM
  • If you want yes/no you either have to transform the data at the SQL Server side first, converting the data into VARCHAR columns with the static text yes/no or you have to use a expression in Excel which evaluates the value and displays the appopiate text.

    The default value can be change by putting a default constraint on a table, like:

    ALTER TABLE TableName
    ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR ColumnName


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Monday, July 03, 2006 7:10 AM
  • Hi

     

    where do i add the default constraint, is it Properties, Check Constraints?

     

    when i added that text there an error validating came up.

     

    Thanks

    Tuesday, July 04, 2006 1:19 AM
  • Hi,

    via GUI you can do that by navigating to the column properties and the default constaint, per script you can do that either during creation of the table (Although you will get a automatic name assigned to the default constraint) or by altering the table:

    CREATE TABLE #TempTable

    (

    SOMEColumn BIT DEFAULT 1

    )

    GO

    DROP TABLE #TempTable

    GO

    CREATE TABLE #TempTable

    (

    SOMEColumn BIT

    )

    GO

    ALTER TABLE #TempTable

    ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR SOMEColumn

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, July 04, 2006 5:34 AM
  •  When you select the field in design mode - the Columns properties tab has a field entitled "Default Value" - that might also be an option that you can set the default value to 1. 

    Wednesday, October 08, 2008 11:35 PM