Specify data type with column alias in SQL Server 2008

Question

Imagine I have the following SELECT statement in a view (SQL Server 2008):

SELECT (SELECT CASE 
                 WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y' THEN 1 
                 ELSE 0 
               END) AS OOS 
  FROM HISTORY

The column OOS ends up being of type int, but I'd like it to be of type bit. How can I accomplish this?

Solution 1

Use CAST/CONVERT to explicitly define the data type:

SELECT CAST (CASE 
               WHEN LOWER(h.outofserv) = 'y' THEN 1 
               ELSE 0 
             END AS BIT) AS OOS 
 FROM HISTORY h

Solution 2

you should just use CAST(1 as bit) and CAST(0 as bit) instead of 1 and 0:

SELECT (CASE WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y'
             THEN CAST(1 AS bit) 
             ELSE CAST(0 AS bit) 
        END) AS OOS 
FROM HISTORY

Solution 3

Here's a way to get the desired BIT datatype output, and with a little cleaner code using:

• upper()

• cast()

  SELECT CAST(CASE
  WHEN UPPER(History.OutofServ) = 'Y' THEN 1
  ELSE 0 END AS BIT) AS OOS FROM History

I hope that helps.