Ms Access中如何编写SQL将22.02.2020格式短文本日期更新至日期型列

场景说明 #

待操作的表包含两类字段：

• 短文本（Short Text）类型的日期存储列，存放内容为dd.mm.yyyy格式，例如22.02.2020
• 原生日期/时间（Date/Time）类型的目标列，需要将转换后的合法日期值更新到该列

常见踩坑点 #

直接用CDate(短文本列)做类型转换会报错，或者出现月日识别颠倒的问题：这种日.月.年的点分隔格式，仅在欧盟区域设置的Windows环境下会被默认正确识别，在中文、英文等默认按“月/日/年”逻辑识别的系统环境中，会把开头的2位数字当成月份，碰到数值大于12的情况会直接抛出“类型不匹配”错误。

正式可用的更新SQL语句 #

执行前请将语句中的表名、列名替换为你实际的数据库对象名：

UPDATE 你的实际表名
SET 目标日期列 = CDate(
    Mid(Trim(原短文本列), 7, 4) & "-" & 
    Mid(Trim(原短文本列), 4, 2) & "-" & 
    Left(Trim(原短文本列), 2)
)
WHERE 原短文本列 IS NOT NULL 
  AND 原短文本列 LIKE "##.##.####"

语句逻辑说明 #

• 固定格式拆分逻辑：针对dd.mm.yyyy的标准格式，按字符位置精准取值：
  • 用Left(...,2)提取开头的2位日值
  • 用Mid(...,4,2)从第4位字符开始提取2位月值（跳过第3位的点分隔符）
  • 用Mid(...,7,4)提取从第7位开始的4位年值
  • 用短横线把三个值拼接成yyyy-mm-dd的ISO标准格式，该格式不受系统区域设置影响，CDate()可以100%稳定识别
• 数据过滤逻辑：
  • 套Trim()函数去掉原文本前后多余的空格，避免因空格偏移字符串提取位置
  • 通配符匹配LIKE "##.##.####"是Access专属语法，#代表匹配单个数字，用来筛出完全符合“2位数字.2位数字.4位数字”格式的有效文本，跳过格式错乱的脏数据，避免单条无效值导致整个更新查询中断

执行前校验步骤 #

正式运行更新查询前，建议先执行同逻辑的SELECT查询，确认所有转换后的日期值完全正确，避免出现月日颠倒的批量错误：

SELECT 
  原短文本列,
  CDate(
      Mid(Trim(原短文本列), 7, 4) & "-" & 
      Mid(Trim(原短文本列), 4, 2) & "-" & 
      Left(Trim(原短文本列), 2)
  ) AS 转换后的临时日期值
FROM 你的实际表名
WHERE 原短文本列 LIKE "##.##.####"

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