如何将表中所有datetime值的毫秒、微秒、纳秒部分重置为.000

核心需求是将datetime类型值的毫秒、微秒、纳秒部分全部置0，最终时间值以.000结尾，不需要做字符串格式转换，直接使用数据库原生时间函数处理的效率和可靠性最高。

待处理的异常值示例：

2022-06-06 11:55:23.317 
2022-06-02 13:38:22.293 
2022-06-02 16:17:51.343 
2022-06-10 10:37:31.420

处理后的预期效果：

2022-06-06 11:55:23.000 
2022-06-02 13:38:22.000
2022-06-02 16:17:51.000 
2022-06-10 10:37:31.000

不同数据库的实现语句 #

执行更新前建议先在测试环境验证，或开启事务后执行，确认结果符合预期再提交，避免数据损坏。

• MySQL
  直接将字段转为精度为0的datetime类型，数据库会自动截断小数秒部分：

  -- 先查询待更新的记录，确认范围
  SELECT * FROM 你的表名
  WHERE 你的datetime字段 != CAST(你的datetime字段 AS DATETIME(0));
  
  -- 执行更新
  UPDATE 你的表名
  SET 你的datetime字段 = CAST(你的datetime字段 AS DATETIME(0))
  WHERE 你的datetime字段 != CAST(你的datetime字段 AS DATETIME(0));
  

• SQL Server
  通过日期差计算截断到整秒：

  -- 查询待更新记录
  SELECT * FROM 你的表名
  WHERE 你的datetime字段 != DATEADD(second, DATEDIFF(second, 0, 你的datetime字段), 0);
  
  -- 执行更新
  UPDATE 你的表名
  SET 你的datetime字段 = DATEADD(second, DATEDIFF(second, 0, 你的datetime字段), 0)
  WHERE 你的datetime字段 != DATEADD(second, DATEDIFF(second, 0, 你的datetime字段), 0);
  

• PostgreSQL
  使用date_trunc函数直接截断到秒级：

  -- 查询待更新记录
  SELECT * FROM 你的表名
  WHERE 你的datetime字段 != date_trunc('second', 你的datetime字段);
  
  -- 执行更新
  UPDATE 你的表名
  SET 你的datetime字段 = date_trunc('second', 你的datetime字段)
  WHERE 你的datetime字段 != date_trunc('second', 你的datetime字段);
  

不建议使用字符串截取、拼接的方式处理时间字段，这类方式容易受数据库时间格式配置影响，出现解析错误、值异常的问题。

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