Menu
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?
719
SQL Server does not support a Boolean type e.g. SELECT WHEN CAST(1 AS BIT) THEN 'YES' END AS result -- results in an error i.e. CAST(1 AS BIT) is not the same logical TRUE.
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
SQL Server is in fact documented to have a Boolean data type. It just can not be returned from a SELECT or used as the type of a column or variable. For example, the type of a WHERE expression has to be Boolean, not bit . The = comparison operator returns a Boolean, for example.
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()
SELECT CASE WHEN expiry < getdate() THEN 'true' ELSE 'false' END AS Expired FROM MyTable WHERE (ID = 1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
