SQL CASE语句空日期返回1900-01-01的问题如何解决？

你的问题出在ELSE子句返回空字符串''：因为目标列是日期类型，SQL会自动将空字符串隐式转换为日期类型的默认值1900-01-01，而非你期望的空白或NULL。

解决方案1：返回NULL（推荐） #

直接在ELSE里返回NULL，NULL是日期类型的合法值，不会触发隐式转换：

CASE WHEN game_startA IS NOT NULL THEN game_startA 
     WHEN game_startB IS NOT NULL THEN game_startB 
     ELSE NULL 
     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 NULL 
     END AS 'Game End Date'

还可以用COALESCE简化写法，效果完全一致：

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

解决方案2：返回空白字符串（需显式类型转换） #

如果业务要求必须返回空白而非NULL，需要将整个CASE表达式的结果转换为字符串类型（以SQL Server为例，用CAST或CONVERT）：

CAST(
    CASE WHEN game_startA IS NOT NULL THEN game_startA 
         WHEN game_startB IS NOT NULL THEN game_startB 
         ELSE NULL 
    END AS VARCHAR(10)
) AS 'Game Start Date'
,
CAST(
    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 NULL 
    END AS VARCHAR(10)
) AS 'Game End Date'

简化版：

CAST(COALESCE(game_startA, game_startB) AS VARCHAR(10)) AS 'Game Start Date',
CAST(COALESCE(game_endA, game_endB, game_endC, game_endD) AS VARCHAR(10)) AS 'Game End Date'

注意：如果用CONVERT可以指定日期格式，比如CONVERT(VARCHAR(10), COALESCE(...), 23)对应YYYY-MM-DD格式。

内容的提问来源于stack exchange，提问作者SkyeBoniwell