CODEWITHSUNDEEP
sqlcasesql-null
Tejinder Sharma
Tejinder Sharma

Reputation: 31

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

Upvotes: 2

Views: 540

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272246

Use COALESCE function, it returns the first not-null argument which is what you're trying to do:

SELECT COALESCE(DATE1, DATE2) AS DATE2

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 4

Related Questions