InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 12:51:20 GMT20http://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxI'm closing this thread. It's gotten a little silly overall.Lennie, I would disagree that others have accused you of anything or been rude. They have tried to show you the implications of potential issues with the code and ask you to get other explanations. The fact that someone wouldn't give them to you is not the fault of people here.I feel we have a bit of a language barrier here. No one is "accusing" you of anything. They are asking questions or asking you to think about how the datatype could be used.As editor of these forums, I don't see that anyone owes you an apology.Mon, 13 Sep 2010 21:33:23 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxLynn,Lastly, this is a professional forum, please act accordingly. Treat others as you would like to be treated.Refer your above statement to others as well not to mistreat others as well. Also refer it to yourself of not be fair to me pointing your fingers at me and not at other who abused me. If I am an expert in SQL SERVER I won't be asking for help here.Mon, 13 Sep 2010 20:46:46 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/13/2010)[/b][hr][quote][b]WayneS (9/13/2010)[/b][hr](I think I'm going to regret getting drawn into this...)Lennie,It's not until these last two posts of yours that you mention that this field is set to NOT NULL. Also... if the Business Analyst specified to set 1/0 why are you asking us what they mean? You should have asked your Business Analyst.I, among many others, feel you owe many apologies based on your attitude and posting on this thread. Also, many of the others have privately black-listed you. I strongly suggest you swallow some crow, and apologize.[/quote]-----------------------------------------------------------I am asking is because of the datatype BIT 1 and 0. Prior to posting the problem here me and my other programmers office mates did asked the Business Analyst for explaination but he refused to informed us by saying that we are programmer we should know the meaning of it. He has mistaken that we are not DataBase Designer and neither are we familiar with SQL SERVER.I too feel like you that you should owe apology to me for accusing me. If your friend wants to black listed me that is fine. I will jolt down their names. As per your suggestion of swallow crow and apologise, you should refer it to yourself as well. If you are humble and not arrogant.[/quote]I have read every post in this thread and I find it hard to believe that you believe that you are owed an apology. Many knowledgeable people have tried to help you and provide you the information you need to understand the BIT data type in SQL Server.How you interpret the values in the column IS up to you (or the Business Analyst in your case). Just because he refused to answer your question is no reason to take it out on those who tried to assist you.If you aren't getting the answer you expect from your question try looking at your question instead of getting upset with those who tried to answer.Lastly, this is a professional forum, please act accordingly. Treat others as you would like to be treated.Mon, 13 Sep 2010 20:39:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]WayneS (9/13/2010)[/b][hr](I think I'm going to regret getting drawn into this...)Lennie,It's not until these last two posts of yours that you mention that this field is set to NOT NULL. Also... if the Business Analyst specified to set 1/0 why are you asking us what they mean? You should have asked your Business Analyst.I, among many others, feel you owe many apologies based on your attitude and posting on this thread. Also, many of the others have privately black-listed you. I strongly suggest you swallow some crow, and apologize.[/quote]-----------------------------------------------------------I am asking is because of the datatype BIT 1 and 0. Prior to posting the problem here me and my other programmers office mates did asked the Business Analyst for explaination but he refused to informed us by saying that we are programmer we should know the meaning of it. He has mistaken that we are not DataBase Designer and neither are we familiar with SQL SERVER.I too feel like you that you should owe apology to me for accusing me. If your friend wants to black listed me that is fine. I will jolt down their names. As per your suggestion of swallow crow and apologise, you should refer it to yourself as well. If you are humble and not arrogant.Mon, 13 Sep 2010 19:27:51 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxIt's good that people have this code working in some small facet. Don't ignore the warnings of the forum though about making absolute certain that the bit conditions meet business requirements and that the code used is in line with documented business use of those bit columns.Mon, 13 Sep 2010 19:26:43 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]WayneS (9/13/2010)[/b][hr](I think I'm going to regret getting drawn into this...)Lennie,It's not until these last two posts of yours that you mention that this field is set to NOT NULL. Also... if the Business Analyst specified to set 1/0 why are you asking us what they mean? You should have asked your Business Analyst.I, among many others, feel you owe many apologies based on your attitude and posting on this thread. Also, many of the others have privately black-listed you. I strongly suggest you swallow some crow, and apologize.[/quote]Mon, 13 Sep 2010 19:22:05 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxDear Lennie,Your sample SQL Coding that resolved your problem is also helping us. Thank you very much for posting the working SQL CODE here to share with us. You are very nice person. We are very impressed of you saying Thank you to Paul who showed you the sample coding and you work on it to suit your work environment.Your sincerely,Bloxham PeterMon, 13 Sep 2010 19:10:43 GMTPeterBloxhamhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxHullo Lennie,I feel so sad and so sorry for you that you have been abused by so called Helper here except Paul who have helped you and shared with you the SQL Coding while the ABUSER did not share with you instead they accused you instead of themself.Thank you very much for sharing with us the working SQL Code that you modifed based on PAUL suggestion to suit your company Business Analyst. We have used your verson of it as well in our applications for the CALL CENTERThanks to YOU and PAUL for sharing information and sample coding.Both of you are great in helping. My office other programmers have used your coding as well.RegardsAlfred CopermanMon, 13 Sep 2010 18:59:24 GMTAlfredCopermanhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx(I think I'm going to regret getting drawn into this...)Lennie,It's not until these last two posts of yours that you mention that this field is set to NOT NULL. Also... if the Business Analyst specified to set 1/0 why are you asking us what they mean? You should have asked your Business Analyst.I, among many others, feel you owe many apologies based on your attitude and posting on this thread. Also, many of the others have privately black-listed you. I strongly suggest you swallow some crow, and apologize.Mon, 13 Sep 2010 18:49:15 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]David Webb-200187 (9/13/2010)[/b][hr]And what happens if the Status is NULL? Does your code deliver the correct answer? That's all Jeff was trying to point out. He's trying to make sure you understand all the implications of the code you have proposed so you don't shoot yourself in the foot. At this point, the folks here have gone way beyond the call of duty to give you a hand. And I, for one, would still like to see a public apology to Gail.And just as another helpful hint. Shouting on this, and most other, forums flags you as a troll pretty quickly. Please tone down the caps and the large fonts.[And then again, I might be wrong ...David Webb/quote]Yes, you are wrong. I too like to see you make public apology as well. I am not wrong. The SQL SERVER 2000 table creation was by Database Administrator based on the Business Analyst specification of using datatype BIT value of 0 and I and under the column Header of Allow Null do not tick on it to allow NULL. and under the default value properties set it to 0. On the Data Entry FORM , textbox validation check for either 0 or 1.Mon, 13 Sep 2010 18:35:39 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxStefan krzywickiAs per your statement listed below and your critism you are wrong.You are very very wrong making false accusation of me.I did not create the table. It was the Database Administrator of created it based on the Business Analyst specification. The Database Administrator is using DataType BIT with value 1 and Zero and under the Allow Null column header it does not allow NULL. So whoever make the accusation is very very wrong.[b]Your statement Quotation:[/b]Did this Business Analyst create the field in the first place? First you said you wanted to know "what 0 and 1 meant", then if 0 was Yes and 1 was No, finally you say that 1 is Active and 0 (or NULL, but you don't seem to realize that) is NotActive. Then when responding to Jeff, despite that your code says Active and NotActive, you tell him it means Active and Inactive.Mon, 13 Sep 2010 18:26:29 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/13/2010)[/b][hr]Jeff,I am surprise that you said the coding list below is not working.Based on it the data export to Excel Spreadsheet display exactly what the Business Analyst Requested of 1 = Active and 0 = Inactive and the same data display on the VBNET2008 ListView is working very well too.This is the working coding that I posted to share with others and you said it's not working and was surprised that you did not show sample coding to prove that it's not working. :cool: [b]Select SupplierID, SupplierName, Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier [/b][/quote]Read his response again, he didn't say it isn't working. What he said, Gail said, I said and others have said is that you don't know if it IS working. Your question was very badly worded, your responses have been rude and don't show that you understand the problem. You never stated that a Business Analyst requested specific meanings for 1 and 0. You still haven't indicated if the column allows NULL values. Do you know what a NULL value is? In fact, you stated that the DBA created the field, not a Business Analyst and that you didn't understand what the DBA told you.Did this Business Analyst create the field in the first place? First you said you wanted to know "what 0 and 1 meant", then if 0 was Yes and 1 was No, finally you say that 1 is Active and 0 (or NULL, but you don't seem to realize that) is NotActive. Then when responding to Jeff, despite that your code says Active and NotActive, you tell him it means Active and Inactive.At the very best, you're a very sloppy programmer. At face value, you don't know what you're doing, don't understand the task and are taking the frustration resulting from your ignorance out on the people here. People who are trying to help you, for FREE, do a job you're apparently being paid to do. If you're very lucky, this code of yours will work as intended, through no fault of your own and you won't cause your company a disaster. I'd say there's still a good chance you're wrong and 0 means Active and 1 means Inactive. Since you don't understand the topic, there's probably a good chance NULLs are indeed allowed and you don't even know what that'll do.Mon, 13 Sep 2010 16:46:30 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxAnd what happens if the Status is NULL? Does your code deliver the correct answer? That's all Jeff was trying to point out. He's trying to make sure you understand all the implications of the code you have proposed so you don't shoot yourself in the foot. At this point, the folks here have gone way beyond the call of duty to give you a hand. And I, for one, would still like to see a public apology to Gail.And just as another helpful hint. Shouting on this, and most other, forums flags you as a troll pretty quickly. Please tone down the caps and the large fonts.Mon, 13 Sep 2010 15:57:19 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxJeff,I am surprise that you said the coding list below is not working.Based on it the data export to Excel Spreadsheet display exactly what the Business Analyst Requested of 1 = Active and 0 = Inactive and the same data display on the VBNET2008 ListView is working very well too.This is the working coding that I posted to share with others and you said it's not working and was surprised that you did not show sample coding to prove that it's not working. :cool: [b]Select SupplierID, SupplierName, Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier [/b]Mon, 13 Sep 2010 15:11:26 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/12/2010)[/b][hr]Hi Good Guys, :w00t: I have found the solution to my non Stored Procedure or TRansactSQL SQL String. It working now on the VBNET2008 Form and it retrieve and display the information as requested by the Business Analyst. :-PHere are the working coding that I want to share with Newbies who may have similar problems and also to Contributors of this AWESOME AND WONDERFUL FORM :-DHERE ARE THE SQL CODING (SQL SERVER 2000)[b] [size="3"]Select SupplierID, SupplierName, Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier [/size] [/b][/quote]BWAA-HAAA!!!! That won't work if Status = 1 is defined as "IsNotActive". :-P And wait until you run into the 3rd value a BIT can have (or not). Please read about the BIT datatype in BOL (ie: Books On Line ... the free help software that comes with SQL Server.. You and your 5 team mates will understand so much more.Like the others have been trying to tell you, the meaning of 0 or 1 is up to you. ;-)Mon, 13 Sep 2010 11:43:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/12/2010)[/b][hr][quote][b]GilaMonster (9/12/2010)[/b][hr]I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.[/quote][b]I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). [/b] If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.[/quote]And you need to stop being so rude to someone who's trying to assist you. You didn't explain to Gail what you were looking for, nor did you post a link to your previous thread where you got confused. In fact, some other poster had to read both threads and point out the issue to everyone. Therefore, you don't have a right to scream at her for giving you the answer to the question you did ask.We are not mind readers. We can only see the text you point on the screen. And your little "Thank you" shout does not in any way absolve you of how you treated one of the most respected members of this committee. If you can't remember a little thing called "manners," then you should perhaps consider not using forums at all.And you really should apologize to Gail for treating her so harshly when she did actually answer your question, whether you agree with the answer or not.Mon, 13 Sep 2010 04:47:04 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxHi Good Guys, :w00t: I have found the solution to my non Stored Procedure or TRansactSQL SQL String. It working now on the VBNET2008 Form and it retrieve and display the information as requested by the Business Analyst. :-PHere are the working coding that I want to share with Newbies who may have similar problems and also to Contributors of this AWESOME AND WONDERFUL FORM :-DHERE ARE THE SQL CODING (SQL SERVER 2000)[b] [size="3"]Select SupplierID, SupplierName, Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier [/size] [/b]Sun, 12 Sep 2010 18:11:08 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[b][size="6"]Hi Paul,Yaaa...Hooooo...............you are awesome and wonderful in sharing information.That's what I wanted......................Yeee...Haaa..........I am so glad to meet you here. I really appreciate your help very much and this FORUM is wonderful to have you here..........Yaaa...Hooo[/size][/b][b][size="5"]Thank you again, Have a Good Day.[/size][/b]Cheers,LennieSun, 12 Sep 2010 16:25:43 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/12/2010)[/b][hr]strSql = "Select SupplierID, SupplierName "strSql &= "IIF(ActiveStatus = 1, 'YES' , 'NO') as ActiveStatus "strSqk &= " From TblSupplier "[/quote]SQL Server does not have the IIF function, so you would need to write:[code="other"]strSQL = "SELECT SupplierID, SupplierName "strSQL &= "CASE WHEN ActiveStatus = 1 THEN 'YES' ELSE 'NO' END AS ActiveStatus "strSQL &= "FROM TblSupplier "[/code]Sun, 12 Sep 2010 15:54:36 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Alvin Ramard (9/12/2010)[/b][hr][quote][b]Lennie (9/11/2010)[/b][hr][quote][b]GilaMonster (9/11/2010)[/b][hr]A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.[/quote] :cool:I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE ?These are my question that I asked in my earlier posting. :-P[/quote]What do 0 and 1 mean? They mean whatever the database designer wanted them to represent. If the answers provided are not sufficient for you, then you should be asking the database designer, not us.[/quote]I am posting my problem here to seek the expert help because the DataBase Administrator he himself is very confused and coulld not explain logically. That's why the problem is posted here for help. Once I got it working I will post the solution here for others like you who doesn't know about it.Sun, 12 Sep 2010 15:25:49 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxHi Paulthank you very much for sharing the information with me. YOu are awesome and generous in sharing information.In a normal SQL String (non stored procedures or Trans.SQL) how to use the IIF statement to determine.Example:strSql = "Select SupplierID, SupplierName "strSql &= "IIF(ActiveStatus = 1, 'YES' , 'NO') as ActiveStatus "strSqk &= " From TblSupplier "Sun, 12 Sep 2010 15:21:43 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/12/2010)[/b][hr][quote][b]GilaMonster (9/12/2010)[/b][hr]I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.[/quote][b]I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). [/b] If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.[/quote]You need to ask the DBA what they represent! Only that DBA can answer!!! What's your problem? Are you scared to ask the DBA?Sun, 12 Sep 2010 15:20:50 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]GilaMonster (9/12/2010)[/b][hr]I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.[/quote][b]I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). [/b] If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.Sun, 12 Sep 2010 15:14:46 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/11/2010)[/b][hr][quote][b]GilaMonster (9/11/2010)[/b][hr]A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.[/quote] :cool:I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE ?These are my question that I asked in my earlier posting. :-P[/quote]What do 0 and 1 mean? They mean whatever the database designer wanted them to represent. If the answers provided are not sufficient for you, then you should be asking the database designer, not us.Sun, 12 Sep 2010 09:55:38 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]GilaMonster (9/12/2010)[/b][hr][quote][b]Lennie (9/11/2010)[/b][hr][quote][b]GilaMonster (9/11/2010)[/b][hr]A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.[/quote] :cool:I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE ?[/quote]Up to you. Intrinsically in SQL 0 means just 0 and 1 means just 1. It's a bit column, not a boolean. You can assign true/false meanings to the values as you like. It's not MS-Access where the boolean column has such meanings (0 true and 1 false)Typically, if people do assign such logical meanings to the 1 and 0, 1 is true and 0 false, but it's totally up to you.[/quote]Your point about Access is why it is so important to understand that while, as Paul White says[quote]You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0. (2005 and later only)By convention, a bit value of 1 is associated with a boolean 'true', and 0 with a boolean 'false'. As Gail points out, this isn't enforced by SQL Server (except by implication as noted in the string assignments above), but it is extremely common.Most people would see a bit value of 1 as implying 'true', 'on', 'yes' or some other equally 'positive' interpretation. A bit value of 0 is seen as implying 'false', 'off', or 'no'.[/quote]this won't always be the case. If you're dealing with a table that someone else designed and they were primarily an Access developer before creating this database or if the database was "upsized" to SQL Server from an Access database, then 0 could easily be "true" and 1 would be "false".This could also be the case if the specs for the database comes from a picky programmer or a business person with no tech experience.You also have to remember that a bit field can be declared as accepting NULL values. This can complicate matters if the original designer considers NULL a default position and instead of meaning "unknown" it could mean either "true" OR "false".Sun, 12 Sep 2010 09:49:32 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxjust my 2ct:Bit is context driven !If your column name doesn't declare it by nature, you should document it well !e.g. a column named "AreLightsOn" would by nature have a bit value 0 for NO (lights are not ON) and the other value (-1 or 1, depending on you sqlserver version) would be YES (lights are on)A column named "Gender" would have to be documented which gender code would be used for male and which code for female.Keep in mind a bit column can be NULL unless declared otherwise.Up to 7 bit columns can be stored in a byte, but off course the first bit column will add a byte to your row length.[u]btw: There is no reason to shout at any forum ![/u][b]In many cases if you don't get the answer you aimed for, or if the answers aren't what you would expect, chances are you malformed the question.[/b]Sun, 12 Sep 2010 09:09:55 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxNice work Paul. That could certainly explain some of it.Sun, 12 Sep 2010 08:44:11 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxHmm. If anyone is wondering why 'Lennie' [url=http://www.sqlservercentral.com/Forums/FindPost984359.aspx]mentioned[/url] CASE before - it's because (s)he asked the same question on [url=http://bytes.com/topic/sql-server/answers/895367-sql-server-2000-datatype-bit]another site[/url] and has become confused as to who said what where.It seems Lennie is actually asking how to use the IIF statement in SQL Server to 'decode' a bit column. The answer given on the other site was to use CASE. No example was provided there, so Lennie got a grump on.Lennie, instead of:[code="sql"]Select SupplierID, SupplierName, iif (Status = 1, 'Active', 'NotActive') as [Status]from TblSupplier[/code]Use:[code="sql"]SELECT SupplierID, SupplierName, [Status] = CASE WHEN [Status] = 1 THEN 'Active' WHEN [Status] = 0 THEN 'NotActive' ELSE NULL ENDFROM dbo.tblSupplier;[/code]Sun, 12 Sep 2010 08:40:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxThanks very much Ron! :-)Sun, 12 Sep 2010 08:30:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxPaul White - tried this in Query Analyzer - yup still have SQL 2000 on an older desktop - Ran the following 3 times .. same result each time[code="sql"]CREATE TABLE #Temp (b BIT NOT NULL);INSERT #Temp VALUES (1);INSERT #Temp VALUES ('True');INSERT #Temp VALUES (456);[/code]Result:[code="plain"](1 row(s) affected)Server: Msg 245, Level 16, State 1, Line 2Syntax error converting the varchar value 'True' to a column of data type bit.[/code]Sun, 12 Sep 2010 08:13:49 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]steve-893342 (9/12/2010)[/b][hr]I believe this feature was introduced in SQL Server 2005.[/quote]Thanks Steve - I couldn't remember whether it worked in SQL Server 2000 or not. If anyone has a 2000 install around to test this I'd be grateful - I have a nagging doubt that it worked in 2000 but was undocumented. I may well be wrong about that.Sun, 12 Sep 2010 08:11:56 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/12/2010)[/b][hr]A helper is supposed to assist by ...[/quote]Odd to instruct others on how you must respond in a forum, when you shout in the forums as you did with the following post:[quote][b]Lennie (9/12/2010)[/b][hr][size="6"]It's not up to me. I am just a logical users.It's up to the creator of the SQL SERVER and it's datatype defination.I am just a logically progammer and user.[/size] :-P[/quote]Sun, 12 Sep 2010 07:49:00 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxVery nicely explained by Gail and Paul.Sun, 12 Sep 2010 07:45:08 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Paul White NZ (9/12/2010)[/b][hr]3. You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0.[/quote]I believe this feature was introduced in SQL Server 2005.Nicely explained Paul and Gail:-)Sun, 12 Sep 2010 06:43:33 GMTsteve-893342http://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/11/2010)[/b][hr]I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE? These are my question that I asked in my earlier posting.[/quote]The bit data type is an integer type which can hold the values 0, 1, or NULL. See [url]http://msdn.microsoft.com/en-us/library/aa225961(v=SQL.80).aspx[/url]The bit data type is 'special' in a number of ways:1. If you assign any non-zero number to it, it is implicitly converted to 1.2. Storage is optimized: up to eight bit columns can be stored in a single byte of physical storage.3. You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0. [b](2005 and later only)[/b]By convention, a bit value of 1 is associated with a boolean 'true', and 0 with a boolean 'false'. As Gail points out, this isn't enforced by SQL Server (except by implication as noted in the string assignments above), but it is extremely common.Most people would see a bit value of 1 as implying 'true', 'on', 'yes' or some other equally 'positive' interpretation. A bit value of 0 is seen as implying 'false', 'off', or 'no'.Fundamentally, though, the definition of bit is that it holds integer values - which may be either 0 or 1.Here's an example to illustrate some of the behaviours of the bit data type:[code="sql"]CREATE TABLE #Temp (b BIT NOT NULL);INSERT #Temp VALUES (1);--INSERT #Temp VALUES ('True'); -- 2005 onwardINSERT #Temp VALUES (456);-- SuccessSELECT *FROM #TempWHERE b = 1;-- No rows-- 456 is interpreted as an integer-- Integer has a higher precedence than BIT-- So the BIT column value is converted to an integer-- to make the comparisonSELECT *FROM #TempWHERE b = 456;-- SuccessSELECT *FROM #TempWHERE b = CONVERT(BIT, 456);DROP TABLE #Temp;[/code]I'm afraid I don't have 2000 installed any more, so I have only been able to test the above on SQL Server 2005 and 2008.Pauledit: updated to reflect Ron's confirmation of behaviour in SQL 2000Sun, 12 Sep 2010 06:17:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxI don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.Sun, 12 Sep 2010 05:14:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxA helper is supposed to assist by providing sample coding instead of saying it's UP TO YOU and try not to help by providing intense information of proposed suggestion on using CASE without defining what sort of category it belongs toSun, 12 Sep 2010 04:12:01 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspxThere's no need to shout at me, and doing so is not going to get you the response that you apparently want! Yelling at someone because you don't like or don't agree with what they're saying is rude at best.Now, as I said before, there is NO special meaning to 0 or 1 in the bit type. There is no intrinsic definition of the two in the definition of the data type. If you want to declare a column called Colour as BIT and have 1 mean 'red' and 0 mean 'blue', that is totally up to you. The only meanings of 0 and 1 in the BIT data type are the mathematical ones.Sun, 12 Sep 2010 04:07:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[size="6"]It's not up to me. I am just a logical users.It's up to the creator of the SQL SERVER and it's datatype defination.I am just a logically progammer and user.[/size] :-PSun, 12 Sep 2010 02:52:20 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[quote][b]Lennie (9/11/2010)[/b][hr][quote][b]GilaMonster (9/11/2010)[/b][hr]A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.[/quote] :cool:I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE ?[/quote]Up to you. Intrinsically in SQL 0 means just 0 and 1 means just 1. It's a bit column, not a boolean. You can assign true/false meanings to the values as you like. It's not MS-Access where the boolean column has such meanings (0 true and 1 false)Typically, if people do assign such logical meanings to the 1 and 0, 1 is true and 0 false, but it's totally up to you.Sun, 12 Sep 2010 02:25:06 GMTGilaMonster