I have a varchar field with true and false contains in them..
Declare @MyValue as bitset @MyValue = convert(bit, 'True')
Give me the following error message:
Server: Msg 245, Level 16, State 1, Line 2Syntax 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.
Quick and dirty fix:
DECLARE @Word varchar(5)DECLARE @MyValue as bitSET @Word = 'True'select @MyValue = CASE @Word WHEN 'true' THEN 1 ELSE 0 END