How to perform Case statement inside a select statement?

I wanted to put ‘No record’ on the column instead of NULL if the datediff function returns a null value.

SELECT concat(e.firstname ,e.lastname) as Fullname,c.shiftcode as Shift, cast(c.datecheckinout as date) Date,datename(month, c.datecheckinout) as RecordMonth,c.timein , c.timeout,
CAST( 
    CASE
    WHEN (datediff(HOUR,c.timein,c.timeout)  IS NULL) 
    THEN 'No record'
    END 
    ), FROM tblCheckInOutDetail c  RIGHT JOIN tblEmployee e on e.IdEmployee = c.IdEmployee   WHERE e.IdEmployee = 55

So far this code only throws Incorrect syntax near ‘CAST’, expected ‘AS’. but I don’t know what data type should I put in the CAST parameter , since if there’s a record it will show the datetime .

Answer

Strictly answering question (though I don’t understand why you need a CASE expression if you have working versions of the query), you can easily translate this to a CASE expression:

ISNULL(CAST(datediff(HOUR,c.timein,c.timeout) as varchar),'No Record')

ISNULL really is just nice, convenient shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END, so:

CASE WHEN DATEDIFF(HOUR, c.timein, c.timeout) IS NOT NULL
  THEN CAST(datediff(HOUR,c.timein,c.timeout) as varchar(11))
  ELSE 'No Record' END

As you can see, a downside is that if you really really really want a CASE expression, you have to repeat at least the DATEDIFF to cover both the case where the outer row doesn’t exist and the case where the outer row exists but one of the values is NULL.

Also note that you should always specify a length for variable types like varchar, even in cases where you think you’re safe with the default.