SQL- using CAST to convert from text to bit

I need help with an error message that pops up when I execute the query below. I am using SQL Server

INSERT INTO table (code, Description, V21, V22, V23, V05,PYMNT, C_PYMNT, V22_PYMNT, V23_PYMNT, Rx_PYMNT, DatasetYear)
    SELECT Code, Description, V21, V22, null, V05, PYMNT, C_PYMNT, null, null, RxPYMNT, 2018 as DatasetYear
    FROM table a
    UNION ALL
    SELECT Code, Description, V21, V22, V23, V05, PYMNT, null, V22_2019PYMNT, V23_2019PYMNT, Rx_2019PYMNT, 2019 as DatasetYear
    FROM table b

The error message say

Operand type clash: text is incompatible with bit.

The columns that are in the "text" datatype are PYMNT, C_PYMNT, RxPYMNT, V22_2019PYMNT, V23_2019PYMNT, RxPYMNT.

How can I use "case when" to convert the datatypes from text to bit?

1 answer

  • answered 2018-10-09 16:47 dutchboy

    I'm not sure what values you get into Text field but I hope this will help

    SELECT CASE variable WHEN 'true' THEN 1 ELSE 0 END AS variable