Getting a boolean from a date compare in t-sql select

Question

I am wondering if something along the lines of the following is possible in ms-sql (2005)

SELECT (expiry < getdate()) AS Expired FROM MyTable WHERE (ID = 1)

I basically want to evaluate the date compare to a boolean, is that possible in the select part of the statement?

Answer 1

Not directly. You have to use CASE, the CAST means it's interpreted as boolean by client code

SELECT
    CAST(CASE WHEN expiry < getdate() THEN 1 ELSE 0 END AS bit) AS Expired
FROM
    MyTable WHERE (ID = 1)

Another solution where one or zero rows are expected:

SELECT
    CAST(COUNT(*) AS bit) AS Expired   
FROM
    MyTable
WHERE
    ID = 1 AND expiry < getdate()

Answer 2

SELECT CASE WHEN expiry < getdate() THEN 'true' ELSE 'false' END AS Expired FROM MyTable WHERE (ID = 1)