IS NULL AND IS NOT NULL

Question

Post reply

IS NULL AND IS NOT NULL

SQLPain

Hall of Fame

Points: 3522
More actions
April 5, 2016 at 4:10 pm

#309649

What would be the best way of writing:
First use TBS.Datetime if NULL then TBS1.Datetime, if both NULL then APL.DateAppEntryStart
below doesn't seem to work and still gives me some NULL values
CASE WHEN CONVERT(varchar(20),TBS.[DateTime], 101) IS NOT NULL THEN CONVERT(varchar(20),TBS.[DateTime], 101)
WHEN CONVERT(varchar(20),TBS.[DateTime], 101) IS NULL THEN CONVERT(varchar(100),TBS1.[DateTime], 101)
ELSE APL.DateAppEntryStart
END AS [Checking]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

I'd use COALESCE.

CREATE TABLE #temp (date1 date, date2 date, date3 date);

INSERT INTO #temp VALUES

('20150101', NULL,'20150203'),

(NULL,'20160401','20120303'),

(NULL,NULL,'20110101');

SELECT COALESCE(date1,date2,date3) FROM #temp;

First, is it possible for all 3 columns to have NULL values? What would be the desired result in that case?

Second, your case statement currently doesn't quite implement the logic you want.

If the first datetime is not null, it returns that datetime. So far so good.

If the first datetime is null, then it automatically returns the second datetime, whether that second datetime is null or not.

For a CASE statement you'd need an additional check for whether the second datetime was null; it would go something like this:

CREATE TABLE #temp (date1 date, date2 date, date3 date);

INSERT INTO #temp VALUES

('20150101', NULL,'20150203'),

(NULL,'20160401','20120303'),

(NULL,NULL,'20110101');

SELECT CASE WHEN date1 IS NOT NULL THEN date1

WHEN date1 IS NULL AND date2 IS NOT NULL THEN date2

ELSE date3

END

FROM #temp;

You could also do it with nested ISNULLs, but I prefer the COALESCE for the cleaner syntax.

Cheers!

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

Thanks, I am using the following in the select statement but getting an error:

COALESCE (CONVERT(varchar(20),TBS.[DateTime], 101), CONVERT(varchar(100),TBS1.[DateTime], 101), CONVERT(varchar(20),APL.DateAppEntryStart, 101) AS [Checking]

Msg 156, Level 15, State 1, Line 23

Incorrect syntax near the keyword 'AS'.

Msg 156, Level 15, State 1, Line 58

Incorrect syntax near the keyword 'AND'.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Missing your closing parenthesis for the COALESCE.

Cheers!