Search code examples
sqlcasesql-null

Setting DateTime variable as NULL in CASE expression

I have two DateTime columns Date1 and Date2. If Date1 is populated, Date2 carries the same value, otherwise Date2 carries its own unique value.

What I am trying to do is, if Date1<>NULL, Date2=NULL. I am using a CASE statement within a SELECT Statement that fetches other values as well. But its not solving my issue.

CASE    
WHEN (DATE1<>NULL AND DATE2<>NULL)
THEN DATE2=NULL
ELSE DATE2
END

Help appreciated! Thanks

Solution

  • You should use IS NULL / IS NOT NULL for comparison

    and in case when don't use assignment as DATE2 = NULL .. use just NULL 

    CASE WHEN (DATE1 IS NOT NULL AND DATE2 IS NOT NULL) THEN  NULL ELSE DATE2 END