SQL Server将varchar类型日期列转为DATE类型时提示转换失败

SQL Server 对字符串转日期的隐式解析逻辑，默认绑定当前会话的 DATEFORMAT 配置。你列中存储的日期格式为 dd/MM/yyyy（日/月/年，例：18/05/2022 代表2022年5月18日），如果当前会话默认配置为美式格式 mdy（月/日/年），解析时会把字符串第一段的18识别为月份，超出月份1-12的合法取值范围，直接触发转换失败。
直接执行ALTER COLUMN修改列类型时，SQL Server会按当前会话的日期格式，对列内所有存量字符串值做隐式转换，只要存在任意一个值不符合当前格式的解析规则，整个修改操作就会回滚并抛出错误。

操作前先确认原列是否允许为NULL、是否存在依赖的索引/约束/触发器，避免修改过程中破坏原有表结构。

• 第一步：先排查列内的非法脏数据
  用显式指定格式的转换函数排查所有无法按dd/MM/yyyy规则转成日期的值，提前修正脏数据：

  SELECT [DateReceived]
  FROM Correspondence
  WHERE TRY_CONVERT(DATE, [DateReceived], 103) IS NULL;
  

  其中格式代码103对应dd/MM/yyyy的日期标准，转换逻辑不受会话DATEFORMAT配置影响。如果上述查询返回结果，先把这些非法值修正为合法的dd/MM/yyyy格式日期，再执行后续操作。

• 第二步：选择对应方案修改列类型

  • 轻量方案（适合无脏数据的测试/小体量场景）：临时调整当前会话的日期解析规则，再执行列修改

    -- 仅对当前连接生效，指定按日/月/年顺序解析日期
    SET DATEFORMAT dmy;
    -- 执行类型修改，注意如果原列是NOT NULL约束，这里要显式写NOT NULL，否则会默认改成允许NULL
    ALTER TABLE Correspondence ALTER COLUMN [DateReceived] DATE NOT NULL;
    

  • 稳妥方案（生产环境推荐）：通过中间列过渡，完全规避隐式转换的不确定性

    -- 新增临时日期列
    ALTER TABLE Correspondence ADD DateReceived_Temp DATE NULL;
    
    -- 显式指定格式，把原列数据转换后写入临时列
    UPDATE Correspondence
    SET DateReceived_Temp = CONVERT(DATE, [DateReceived], 103);
    
    -- 校验临时列数据无误后，删除原字符串列
    ALTER TABLE Correspondence DROP COLUMN [DateReceived];
    
    -- 把临时列重命名为原列名
    EXEC sp_rename 'Correspondence.DateReceived_Temp', 'DateReceived', 'COLUMN';
    

    如果原列存在依赖的索引、约束等对象，需要在删原列前先移除依赖，新列重命名完成后再重新创建对应依赖对象。

注意：日期类数据强烈建议直接使用DATE/DATETIME类型存储，不要用字符串类型存储，避免后续查询、计算时反复出现格式转换问题。

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