CODEWITHSUNDEEP
t-sqlsql-server-2012
UserSN
UserSN

Reputation: 1013

Conversion error using CASE on a varchar column

I'm trying to grab information to send to Google Charts for a graph I'm looking at the column Active which can be true/false but is set to varchar in my database.

Using this query:

SELECT
    SUM(CASE WHEN CONVERT(int, Active) Active = 1 THEN 1 ELSE 0 END) AS Active,
    SUM(CASE WHEN CONVERT(int, Active) Active = 0 THEN 1 ELSE 0 END) AS Inactive
FROM 
    Events

throws this error:

Conversion failed when converting the varchar value 'True' to data type int.`

Upvotes: 0

Views: 565

Answers (1)

Milney
Milney

Reputation: 6427

Ints are not booleans.... they are called bit in SQL Server. try;

SELECT
SUM(CASE WHEN CONVERT(bit, Active) = 1 THEN 1 ELSE 0 END) AS Active,
SUM(CASE WHEN CONVERT(bit, Active) = 0 THEN 1 ELSE 0 END) AS Inactive
FROM Events

Or just test for your String

SELECT
SUM(CASE WHEN Active = 'True' THEN 1 ELSE 0 END) AS Active,
SUM(CASE WHEN Active = 'False' THEN 1 ELSE 0 END) AS Inactive
FROM Events

P.S. Storing Boolean values as String columns is silly... Consider changing the column to be a bit column so you don't even need to convert it

Upvotes: 2

Related Questions