SQL Server Case Statement when IS NULL

Solution 1:

CASE WHEN B.[STAT] IS NULL THEN (C.[EVENT DATE]+10)   -- Type DATETIME
     ELSE '-'                                         -- Type VARCHAR
     END AS [DATE]

You need to select one type or the other for the field, the field type can't vary by row. The simplest is to remove the ELSE '-' and let it implicitly get the value NULL instead for the second case.

Solution 2:

I agree with Joachim that you should replace the hyphen with NULL. But, if you really do want a hyphen, convert the date to a string:

(CASE WHEN B.[STAT] IS NULL
      THEN convert(varchar(10), C.[EVENT DATE]+10, 121)
      ELSE '-'
 END) AS [DATE]

Also, the distinct is unnecessary in your select statement. The group by already does this for you.

Solution 3:

You can use IIF (I think from SQL Server 2012)

SELECT IIF(B.[STAT] IS NULL, C.[EVENT DATE]+10, '-') AS [DATE]