Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert Varchar to Bit Expand / Collapse
Author
Message
Posted Sunday, January 29, 2006 9:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 11, 2010 6:48 PM
Points: 48, Visits: 27

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?

 




Post #254336
Posted Monday, January 30, 2006 4:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

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.

 

Post #254570
Posted Tuesday, January 31, 2006 12:03 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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/
Post #254623
Posted Tuesday, January 31, 2006 7:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 28, 2007 8:31 AM
Points: 61, Visits: 1

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)
Post #254677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse