SQL Server Case Statement when IS NULL

I'm trying to do an IF statement type function in SQL server.

Where there is a NULL in the field, I want it to take a field from one of the tables and add 10 days to it.

And if possible create another column and add the 30 days.

SELECT DISTINCT
    B.[ID],
    MAX(A.[START DATE]),
    B.[STAT],
    C.[POST DATE],
    CASE
	      WHEN (C.[POST DATE] BETWEEN C.[EVENT DATE]+10 AND C.[EVENT DATE]+30) THEN 'GOOD'
          END AS [BETTER VISIT],
    CASE
	      WHEN B.[STAT] IS NULL THEN (C.[EVENT DATE]+10)
	      ELSE '-'
          END AS [DATE]
FROM 
    #TEMP1 A
    FULL OUTER JOIN #TEMP2 B
    ON A.[ID]=B.[ID]
    FULL OUTER JOIN #TEMP3 C
    ON A.[ID]=C.[ID]
GROUP BY
    B.[ID],
    B.[STAT],
    C.[POST DATE],
    C.[EVENT DATE]
ORDER BY
    A.[START DATE] DESC

The result would look sort of like:

    ID  START DATE   STAT    POST DATE    BETTER VISIT    DATE         DATE2
    ---------------------------------------------------------------------------
    1   2013-01-01   GOOD    2013-11-01   GOOD            -            -
    2   2013-03-01   NULL    NULL         NULL            2013-03-11   2013-03-31

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.

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.

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

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

  case isnull(B.[stat],0)
  when 0 then dateadd(dd,10,(c.[Eventdate]))
  end

you can add in else statement if you want to add 30 days to the same .

In this situation you can use ISNULL() function instead of CASE expression

ISNULL(B.[STAT], C.[EVENT DATE]+10) AS [DATE]

Your hyphen in your ELSE statement isn't accepted in the column which is being defined under the datetime data type. You could either:

a) Wrap a CAST around your [stat] field to convert it to a varchar representation of a date

b) Use a datetime like 9999-12-31 for your ELSE value.

Take a look at the ISNULL function. It helps you replace NULL values for other values. http://msdn.microsoft.com/en-us/library/ms184325.aspx

Slight variation on Mahesh solution for mssql:

case when isnull(B.[stat],0) = 0 
     then dateadd(dd,10,(c.[Eventdate]))  
end