Skip to content
Advertisement

How to apply datediff function in select statement using case statement

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 
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement