Convert Varchar to Bit

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

     

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

     

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

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply