I have a stored procedure to select columns of tables like this
CREATE PROCEDURE spSelect AS BEGIN SELECT ID, CheckIn, LapsedDay = CASE WHEN Checkout = NULL THEN DATEDIFF(DAY, CheckIn, GETDATE ()) ELSE DATEDIFF(DAY, CheckIn, CheckOut) END, CheckOut FROM TblGuest END
whenever I am executing this query, it isn’t showing any error but it’s not calculating the the datediff in LapsedDay column in ms SQL. Any help related to this will be greatly appreciated.
Advertisement
Answer
= NULL
is not correct. Comparisons to NULL
almost always return NULL
.
Just simplify the logic using COALESCE()
:
SELECT ID, CheckIn, DATEDIFF(DAY, CheckIn, COALESCE(CheckOut, GETDATE()) AS LapsedDay, CheckOut FROM TblGuest