3

Need to convert gender field has data type as bit.

I used this syntax:

Select case when CAST([GenderIsMale] AS bit)=1 then 'Male' 
when CAST([GenderIsMale] AS bit)=0 then 'Female' 
else  CAST([GenderIsMale] AS bit) 
end

Returns the error:

Conversion failed when converting the varchar value 'Male' to data type bit.

  • 5
    If "gender field has data type as bit" why are you casting it to bit? – Martin Smith Oct 28 '16 at 18:42
  • 3
    What is the type of GenderIsMale? – ypercubeᵀᴹ Oct 28 '16 at 18:43
  • 1
    Your question was about type conversion, but it is worth asking: is a bit field the best way to store "gender"? SQL does allow NULLs in a bit field, so that at least gives you a third value, perhaps for "unknown" or "declined to state". Not sure exactly what your DB is for, but you might need to also allow for people who identify as "non-binary", i.e., neither male or female. There are a variety of ways people might identify, including genderqueer, – BradC Oct 30 '16 at 0:47
  • @BradC this would easily include the Pakistani/Indian concept of Hijra/Aravani/Aruvani/Jagappa en.wikipedia.org/wiki/Third_gender. What the OP needs to consider is that the proper field is "biological sex" or "legally recognized sex" or "chromosomal sex" which ARE STILL not binary choices. There's also XXY, XXX, etc. This should be a text field, which makes the issue that the question asker is trying a lot easier to define. Also, this sounds like it is best left to application logic, not database logic. – jcolebrand Oct 30 '16 at 3:31
7

The problem is that you have two different return types, depending on the value of [GenderIsMale]; the two WHEN conditions return a string while the ELSE returns a BIT. It is easier to see if you format the code nicely:

Select case
    when CAST([GenderIsMale] AS bit)=1 then 'Male'   -- return type = VARCHAR
    when CAST([GenderIsMale] AS bit)=0 then 'Female' -- return type = VARCHAR
    else CAST([GenderIsMale] AS bit)                 -- return type = BIT
end

You can test this as follows:

DECLARE @GenderIsMale INT = 1;

Select case
    when CAST(@GenderIsMale AS bit)=1 then 'Male'
    when CAST(@GenderIsMale AS bit)=0 then 'Female'
    else CAST(@GenderIsMale AS bit)
end

Returns:

Msg 245, Level 16, State 1, Line 28
Conversion failed when converting the varchar value 'Male' to data type bit.

You can fix this by returning a string (or literal NULL) in the ELSE condition (pick one of those ELSE conditions, depending on what you are trying to accomplish):

SELECT CASE CAST([GenderIsMale] AS BIT)
    WHEN 1 THEN 'Male'
    WHEN 0 THEN 'Female'
    ELSE 'other string'
    --ELSE CONVERT(VARCHAR(50), [GenderIsMale]) -- if column is a numeric type
    --ELSE [GenderIsMale] -- if column is a string type
    --ELSE NULL
END;

Please note:

  • Depending on the datatype of the GenderIsMale column and the data in it, it is possible that there is an additional source of the error: the CAST of the value into BIT. For example, if the datatype is VARCHAR, then values of empty string, 'true', 'false', and strings of just digits 0 - 9 (no spaces, commas, periods, or minus sign) will convert, but anything else will error. Since you are using SQL Server 2012, you can switch to using TRY_CAST (or even TRY_CONVERT) to get around that.

  • The commented-out ELSE conditions are mainly to show the possible variations for getting the resulting expression to be consistent across all branches of the CASE statement. But in the scenario here of having a BIT / boolean value, if the datatype of the GenderIsMale column is a number, then the only value that could fall through to the ELSE is a NULL, hence you would use ELSE NULL. But if the datatype is a string, then you would use ELSE [GenderIsMale].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.