How can I check if bit value is greater than 1 with TRY_CONVERT sql function

Question

I'm using sql server managment studio 2012

What I'm trying to do is check if string value is a bit if it is then I want to check if this value is greater than 1. here is my code:

(TRY_CONVERT(bit, '2') IS NULL OR TRY_CONVERT(bit, '2') > 1)

For this sample purpose I hardcoded there a string with value.

Anyone can tell me why this doesn't work I mean second condition.

A `bit` can never be greater than `1`. `cast(2 as bit)` just gives `1` — Martin Smith, Aug 09 '13 at 12:37
Bit can never be greater then 1. Any positive int = 1 when converted to bit. — Nenad Zivkovic, Aug 09 '13 at 12:38
Ok @MartinSmith Thanks your comment was enough to answer this question:) Thanks — Harry89pl, Aug 09 '13 at 12:47

score 2 · Answer 1 · answered Aug 09 '13 at 12:40

bit fields in SQL Server can only have one of three values: 1, 0 or NULL therefore checking for values greater than 1 is invalid.

So to resolve your issue I would consider this method

SELECT Convert(bit, CASE WHEN your_field IN ('1', '0') THEN your_field END) As valid_bit

1 Answers1