SQL Server datetime 空值插入陷阱：默認 1900-01-01 解決方案

問題現象

SQL Server 2005+ 中，datetime 類型字段插入空字符串 '' 時，不會報錯而是默認存儲為 1900-01-01 00:00:00.000。

根因分析

• 空字符串 '' 隱式轉換為 datetime 時，SQL Server 將其解析為基準日期（0 個 tick 對應 1900-01-01）
• 這與 NULL 語義完全不同，但應用層常誤用 '' 表示「無日期」

三種解決方案

方案一：查詢時過濾（推薦，無侵入）

-- 核心技巧：NULLIF 將 '' 轉為 NULL，再 CAST
SELECT 
    CAST('' AS datetime)                              AS raw_insert,      -- 1900-01-01
    CAST(NULLIF('', '') AS datetime)                  AS nullif_fix,      -- NULL
    ISNULL(CAST(NULLIF('', '') AS datetime), GETDATE()) AS with_default;   -- 當前時間

結果對比：

raw_insert          nullif_fix    with_default
------------------- ------------  -------------------
1900-01-01 00:00:00 NULL          2024-01-15 14:30:00

方案二：插入時攔截（應用層/存儲過程）

-- 存儲過程示例
CREATE PROC InsertData @dt VARCHAR(50) AS
BEGIN
    DECLARE @real_dt DATETIME = CASE 
        WHEN @dt = '' OR @dt IS NULL THEN NULL 
        ELSE CAST(@dt AS DATETIME) 
    END;
    INSERT INTO t(col) VALUES (@real_dt);
END

方案三：列默認約束（數據庫層）

ALTER TABLE t ADD CONSTRAINT DF_t_col DEFAULT NULL FOR col;
-- 或
ALTER TABLE t ALTER COLUMN col DATETIME NULL;  -- 確保允許NULL

最佳實踐總結

1. 應用層：日期字段為空時傳遞 NULL 而非 ''
2. 查詢層：使用 CAST(NULLIF(col, '') AS datetime) 統一輸出
3. 遷移數據：ETL 階段將 1900-01-01 批量更新為 NULL
4. 區分語義：NULL=未知/不適用，1900-01-01=業務真實日期（極少見）