Oracle SQL中将日期字段设置为指定日期的实现问题

你之前的写法存在两处问题：一是依赖会话的NLS日期参数的月份缩写写法不稳定，二是只实现了第一个字段的更新逻辑，缺少第二个字段的处理。Oracle标准DATE字面量格式为DATE 'YYYY-MM-DD'，不受会话参数影响，推荐优先使用。

可选实现方案 #

方案1：分两条UPDATE执行（适合大表场景，减少不必要的行锁定） #

更新第一个字段：

UPDATE 你的实际表名
SET field1 = DATE '2017-01-01'
WHERE field1 <= DATE '2017-01-01';

更新第二个字段：

UPDATE 你的实际表名
SET field2 = DATE '2017-12-31'
WHERE field2 >= DATE '2017-12-31';

执行完成确认数据无误后提交事务：

COMMIT;

方案2：单条UPDATE执行（适合小表场景，保证两次修改的原子性） #

UPDATE 你的实际表名
SET 
  field1 = CASE WHEN field1 <= DATE '2017-01-01' THEN DATE '2017-01-01' ELSE field1 END,
  field2 = CASE WHEN field2 >= DATE '2017-12-31' THEN DATE '2017-12-31' ELSE field2 END
WHERE 
  field1 <= DATE '2017-01-01'
  OR field2 >= DATE '2017-12-31';

COMMIT;

注意事项 #

• 执行更新前建议先查询验证匹配行数，避免误修改：

-- 统计第一个字段符合修改条件的行数
SELECT COUNT(*) FROM 你的实际表名 WHERE field1 <= DATE '2017-01-01';
-- 统计第二个字段符合修改条件的行数
SELECT COUNT(*) FROM 你的实际表名 WHERE field2 >= DATE '2017-12-31';

• 如果日期字段存储时分秒，DATE '2017-12-31'默认对应当天0点，若需要匹配2017-12-31全天的记录，可将第二个字段的过滤条件调整为field2 >= DATE '2017-12-31' AND field2 < DATE '2018-01-01'，性能优于TRUNC函数转换。

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