My case statement is returning 1900-01-01 for empty dates

Question

I am trying to return a result for 2 date columns based on different values like this:

CASE WHEN game_startA IS NOT NULL THEN game_startA 
     WHEN game_startB IS NOT NULL THEN game_startB 
     ELSE '' 
     END AS 'Game Start Date'
,
CASE WHEN game_endA IS NOT NULL THEN game_endA 
     WHEN game_endB IS NOT NULL THEN game_endB 
     WHEN COALESCE(game_endC, game_endD) IS NOT NULL THEN COALESCE(game_endC, game_endD)
     ELSE '' 
     END AS 'Game End Date'

The problem is, if the value is NULL, it is returning 1900-01-01 instead of saying NULL or just blank.

Is there a way to fix that?

Answer 1

This logic could be vastly simplified (unless there is more to it than you've shared):

COALESCE(game_startA, game_startB) AS [Game Start Date],
COALESCE(game_endA, game_endB, game_endC, game_endD) AS [Game End Date]

I don't see any need for the CASE expressions or any ELSE that tries to turn a date into an empty string. If COALESCE() gets to the end of its list and still doesn't find a non-NULL value, the output is what you want: NULL.