Conversion failed when converting the NVARCHAR to BIT

Question

Trying to get some clarification as to why this error is occurring. I think it may have something to do with my input data having NULL values but every example I've searched for doesn't really suit my needs.

This is the error I am receiving:

Conversion failed when converting the nvarchar value 'Y' to data type bit.

This is my code:

 CREATE TABLE table (
    ID INT IDENTITY(1, 1) PRIMARY KEY
        .
        .
        .
    ,CFD BIT 
)
;
INSERT INTO table ([CFD]
                  .
                  .
                  .
                  )
SELECT
        stg.[Flag] AS [CFD]
        .
        .
        .
FROM staging stg
;

The column [Flag] being pulled from the staging table contains values 'Y' or NULL. Basically, I am trying to rename the [Flag] column as [CFD] and change values from 'Y' to 1 and NULL to 0.

I have attempted to implement a CASE WHEN statement with little success.

*I have attempted to implement a CASE WHEN statement with little success*. What did you try? Something like `case stg.[Flag] when 'Y' then 1 else 0 end as [CFD]` should do the work. — GMB, Mar 22 '23 at 14:34

score 2 · Accepted Answer · answered Mar 22 '23 at 14:37

2

You mention that you have tried the case statement but as you have not provided it I am unable to check if it is correct. I would do it something like this:

INSERT INTO table ([CFD])
SELECT
    CASE
        WHEN stg.[Flag] = 'Y' THEN 1
        ELSE 0
    END AS [CFD]
FROM staging stg;

answered Mar 22 '23 at 14:37

Datertec

163
4

1

Sorry I typed the question up pretty quickly I actually had exactly this case statement minus the 'END AS..' I did not know that you can put AS after the END – Nick D'Agostino Mar 22 '23 at 14:41

Conversion failed when converting the NVARCHAR to BIT

1 Answers1