transforming from 'Y' or 'N' to bit

Question

I have a table which has a column called Direct of type char(1). It's values are either 'Y' or 'N' or NULL. I am creating a view and I want the value to be transformed to either 0 or 1 of type bit. Right now it's of type INT. How do I go about doing this?

Following is the code:

CASE WHEN Direct = 'Y' THEN (SELECT 1)
WHEN Direct <> 'Y' THEN (SELECT 0) END AS DirectDebit

EDIT: How can I make sure the column type is of type BIT?

Answer 1

This will get you your bit..

CAST(CASE WHEN Direct = 'Y' THEN 1 ELSE 0 END AS BIT) AS DirectDebit

Answer 2

See if this works:

SELECT CASE WHEN Direct = 'Y' THEN 1 ELSE 0 END FROM YOURTABLE

Answer 3

SELECT CASE Direct
WHEN 'Y' THEN '1' 
WHEN 'N' THEN '0' 
ELSE '0'
END as DirectDebit
FROM TableName

... should work.