Convert from boolean to bit

_{Monday, July 27, 2009 4:04 PM}

Is there a simple way to convert a boolien value to a bit value so that it can be returned?

I have used "CASE WHEN {boolean expression} THEN 1 ELSE 0 END" but that somehow seems inelegant to me and certainly detracts from readability if there are a lot of such values in a query.

Jacob

_{Saturday, August 8, 2009 6:28 PM ✅Answered}

I would then say that the "short answer" to your question is "no"; you need to write out your case statements and toggle between 0 and 1 based on the results of your case statements.  Yes, it will make your code more long-winded; it is what it is.

Kent

_{Monday, July 27, 2009 4:51 PM}

Is this intended to start a discussion or are you asking for an alternative?

_{Monday, July 27, 2009 5:25 PM}

Is this intended to start a discussion or are you asking for an alternative?

I am looking for an alternative.

Jacob

_{Monday, July 27, 2009 5:27 PM}

Yes. And No.

It depends on the boolean expression. There is no such thing as CBOOL() in T-SQL.

For simpler boolean expressions, such as CASE WHEN X > 0 THEN
there is no alternative. Rewriting as SIGN(x) = 1 will gain nothing.

So is there a way for more complex expressions?

_{Monday, July 27, 2009 6:06 PM}

There are no bits in SQL. Most often, it's temporal data (is_approved vs date_approved)

_{Tuesday, July 28, 2009 12:05 PM}

in sql server we have bit column that return 1 or 0 only.

you can use query like this

select (case when 'TRUE' then 1 else 0 ) boolean result.

this might helps. I also take a look of alternative. if we have?

Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Tuesday, July 28, 2009 12:13 PM}

About how many lines is your {boolean expression}?  I am assuming from the post that this expression is long-winded or very long-winded -- is that the case?  It will help if you post what you are dealing with so that we can quit our guessing game.

_{Tuesday, July 28, 2009 12:14 PM}

Is there a simple way to convert a boolien value to a bit value so that it can be returned?

I have used "CASE WHEN {boolean expression} THEN 1 ELSE 0 END" but that somehow seems inelegant to me and certainly detracts from readability if there are a lot of such values in a query.

Jacob

Your CASE expression and similar solutions will convert a {boolean expression} that results in "UNKNOWN" to 0. If you want to return a NULL for the UNKNOWN case then use

CASE WHEN {boolean expression} THEN 1 WHEN NOT ({boolean expression}) THEN 0 END

_{Tuesday, July 28, 2009 1:05 PM}

Jacob,

You want the ol' evaluation syntax "(expression)" so you can do something like:

SELECT (Age > 17) AS [IsLegalAge]
     , LastName
     , FirstName
  FROM Applicant
GO

...producing output...

IsLegalAge LastName FirstName

1  Crazy  Adam
0  Grader  Second

JET has the IIF() function:

SELECT IIF(Age > 17, 1, 0) AS [IsLegalAge]
     , LastName
     , LastName
  FROM Applicant
GO

However, SQL Server doesn't provide a comparable method (as far as I know). Let me frame that answer. With-in and IF or statment, you can have an expression:

IF (1=1) PRINT 'Equal';

However, the expression won't evaluate in a SELECT unless it is in a CASE statement (as you noted).

SELECT (1=1); --Won't compile

SQL does not default "1" to boolean TRUE or "0" to boolean FALSE.

IF (1) PRINT 'Equal';  --Won't compile

So, an expression that returns 1 or 0 will not return true or false respectively.

You're stuck with CASE statements; although, you can create your own UDF and call it in a similar fashion as IIF.

Here is a link to the T-SQL page on expressions. You can examine it for unfound hacks

http://msdn.microsoft.com/en-us/library/ms190286.aspx

Cheers,
Crazy Adam

_{Saturday, August 1, 2009 11:07 PM}

I don't have a specific piece of code I am working on right now. but, this is an issue I have faced many times and I hope that there is a better way to do it.

_{Monday, August 10, 2009 12:43 PM}

It depends on the expression and on what you consider elegant.

_{Monday, August 10, 2009 1:32 PM}

It depends on the expression and on what you consider elegant.

Yup