SQL Server Case Statement when IS NULL

Question

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

User · Answer

In this situation you can use ISNULL   function instead of CASE expression   ISNULL B  STAT   C  EVENT DATE  10  AS  DATE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use IIF  I think from SQL Server 2012   SELECT IIF B  STAT  IS NULL  C  EVENT DATE  10   -   AS  DATE

[sql] SQL Server Case Statement when IS NULL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008