December 17, 2014 at 10:08 pm
Comments posted to this topic are about the item Bit Casting
December 17, 2014 at 10:10 pm
This was removed by the editor as SPAM
December 17, 2014 at 10:32 pm
Arrggghhh....
Got lost in my 1's and 0's and pressed the wrong option. Knew the right answer, just picked the wrong 'un.
December 18, 2014 at 1:15 am
Easy one, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2014 at 2:09 am
Might have been a bit trickier if it didn't come down to knowing how -5 was treated
December 18, 2014 at 3:59 am
Nice one, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
December 18, 2014 at 6:52 am
Good reminder thanks.
December 18, 2014 at 7:30 am
Good question. Let's not forget that this is NOT a real representation of a boolean. A bit in sql server can have 3 values with NULL being the third.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2014 at 7:56 am
Nice Question, Thanks
December 18, 2014 at 8:16 am
Nice question, Gabe, although it would have been better if you had included more options so finding the solution didn't just boil down to "how is -5 converted". One possibility would be 0,0,0,1,0,1,0, where only '1' and 1 are converted to a 1. You could have also included converting -1 and/or '-1' to a bit, because some languages (VB6 comes to mind)treat Boolean data types as either 0 or -1, because -1 is 11111111111111111111111111111111 in binary.
December 18, 2014 at 8:16 am
Interesting that the text 'false' evaluates to 0. I would have guessed that any non-empty string would evaluate to 1.
Don Simpson
December 18, 2014 at 8:56 am
Stewart "Arturius" Campbell (12/17/2014)
Nice one, thanks Gabe.Have used this before, where the values were stored as true / false strings in the mainframe.
No. I actually found out the "true"/"false" functionality after the fact (thank god). I just thought it warranted mentioning. I ran into this when I added a primary key on a table variable I was doing some testing with, which had a bit column as the PK. Instead of overflowing when I added a third value, it did a PK violation rather than an overflow
December 18, 2014 at 8:59 am
DonlSimpson (12/18/2014)
Interesting that the text 'false' evaluates to 0. I would have guessed that any non-empty string would evaluate to 1.
Just to add insult to injury, if you try to cast any string other than "true" or "false" to a bit, you'll get a conversion failure.
select cast('Hello' as bit)
There are a lot of really good reasons NOT to use those strings when assigning bit values. But it's interesting to know, none the less.
December 18, 2014 at 9:12 am
antony-688446 (12/17/2014)
Arrggghhh....Got lost in my 1's and 0's and pressed the wrong option. Knew the right answer, just picked the wrong 'un.
I've done that before. 😛
December 18, 2014 at 5:25 pm
Sean Lange (12/18/2014)
Good question. Let's not forget that this is NOT a real representation of a boolean. A bit in sql server can have 3 values with NULL being the third.
It actually is a real representation of a boolean. Being able to be NULL instead of having a value is an inherent property of booleans in the real world (and also in the world of mathematics).
As far as booleans are concerned there are two values that they can have. It's also possible that for a particular boolean you don't know the value. That is represented n SQL by an SQL value not constrained not to be null. Of course there are times when one wants to refer to a boolean value where the value, whatever it may be, will always be known. This can of course be expressed perfectly well an attribute or a row in a table, using the bit type qualified by NOT NULL , but can't be expressed for an SQL local variable (local variablies are a bit of a pigs breakfast in SQL semantics for that reason amongst others).
So SQL has two different sorts of bits: both sorts have two values, and only two values, so as far as their values are concerned they are real representations of a boolean value. One of those two sorts is such that it is always known what its value is, so is what one might call an ideal boolean. The other is a the real world boolean, which admits the possiblility that at any given time the value may not be known.
So I don't think that it's realistic to say that bit is not a real representation of a boolean.
There are some rather nice examples of boolean values that are unknown in mathematics: for example suppose F is a partial recursive function, P is the proposition "if F is applied to 0 at 1.00 am using a computer capable of 10**10 Mflop/s the computation will terminate by 5pm this afternoon". The tructh value for proposition is a boolean, it is either true or false, but it may be impossible to determine which it is before someone has tried the experiment. So until the question has been resolved by experiment, the value is unknown and SQL will correctly represent that situation by responding NULL instead of delivering a 1 or 0 value to a request for the value of P until such time as the experiment has been carried out and the result inserted into the database. And there are nastier examples: suppose the proporsition is "if F is applied to 0 the computation will eventually terminate with a valid result provided enough computational power is supplied for enough time"; it is a proven fact of computational theory (since over three quarters of a century ago) that for some values of F the value of the corresponding proposition P can never be known unless P is true, and it can only be known that it's true if someone has actualy carried out the computation and it terminated with a valid result, and it is impossible in general to determine whether any particular F is or is not such an F (independent proofs were made by Gödel - the incompleteness theorem - and Turing - the halting problem - and by others), so a proper representation of a boolean must allow it to be NULL instead of having a value unless there are very solid grounds for claiming that its value is always known.
Tom
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply