Creating a Yes/No field in SQL Server

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

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|||

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

|||

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

|||

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

|||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