Setting DateTime variable as NULL in CASE expression

Question

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

Are you trying to *change* the value of Date2? – avery_larry Nov 19 '19 at 20:17 — avery_larry, Nov 19 '19 at 20:17
Case _expression_, not statement. – jarlh Nov 19 '19 at 20:42 — jarlh, Nov 19 '19 at 20:42

score 4 · Accepted Answer · answered Nov 19 '19 at 19:59

4

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

answered Nov 19 '19 at 19:59

ScaisEdge

131,976
10
91
107

Based on the information in the question, I don't think this needs `date2 is not null`. I believe it can just be `case when DATE1 is not null then null else DATE2 end as DATE2` – avery_larry Nov 19 '19 at 20:40

score 2 · Answer 2 · answered Nov 19 '19 at 20:03

2

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

SELECT COALESCE(DATE1, DATE2) AS DATE2

answered Nov 19 '19 at 20:03

Salman A

262,204
82
430
521

Setting DateTime variable as NULL in CASE expression

2 Answers2