Convert Varchar to Bit

• yan_aung

  SSC-Addicted

  Points: 480

  More actions

  January 29, 2006 at 9:57 pm

  #110716

  I have a varchar field with true and false contains in them..

  Doing this:

  Declare @MyValue as bit

  set @MyValue = convert(bit, 'True')

  Give me the following error message:

  Server: Msg 245, Level 16, State 1, Line 2

  Syntax error converting the varchar value 'True' to a column of data type bit.

  I can do the case statement with when on it like this:

  select Deleted = CASE Deleted WHEN 'False' THEN '1' Else '0' END

  I want to know why convert function does not work?

   

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  January 30, 2006 at 4:17 pm

  #617848

  In SQL, bits are represented by 0 or 1.  It doesn't translate the string 'TRUE' to a 1.  If the source data is a string 'TRUE', then a case statement has to be used.

   

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  January 31, 2006 at 12:03 am

  #617883

  Bits are always 0 and 1. SQL Server does not have a boolean datatype, instead we use bit to represent them. Naturally it is impossible to convert a string to a number, so the convert from varchar to bit fails.

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• Giles McArdell

  SSC Eights!

  Points: 875

  More actions

  January 31, 2006 at 7:21 am

  #617923

  Quick and dirty fix:

  DECLARE @Word varchar(5)

  DECLARE @MyValue as bit

  SET @Word = 'True'

  select @MyValue = CASE @Word WHEN 'true' THEN 1 ELSE 0 END

   

  ---

  "Don`t try to engage my enthusiasm, I don`t have one."

  (Marvin)