Oracle中如何将VARCHAR转为日期时间并筛选≤2022年9月14日的记录

需求说明 #

现有Oracle表MY_TEST_TABLE，其中DTEXT列以文本格式存储日期时间（格式示例：'16:22:38:0570 14 SEP 2022'），需要将该列解析为标准日期时间类型，并筛选出日期时间≤2022年9月14日的记录。

表结构：

CREATE TABLE MY_TEST_TABLE (
  DID number(10) NOT NULL,
  DTEXT varchar2 (50),
  CONSTRAINT id_pk PRIMARY KEY (DID)
);

测试数据：

DID  DTEXT
1    13:25:58:0570 15 SEP 2022
2    10:20:38:0270 15 SEP 2022
3    16:22:38:0570 14 SEP 2022
4    02:19:18:0370 14 SEP 2022
5    03:29:14:0330 13 SEP 2022

解决方案 #

1. 文本转标准日期时间类型 #

Oracle中使用TO_TIMESTAMP函数将文本格式的日期时间转换为TIMESTAMP类型（支持毫秒精度），格式掩码需与DTEXT的格式完全匹配：

• HH24：24小时制小时
• MI：分钟
• SS：秒
• FF4：4位毫秒值
• DD：日
• MON：英文月份缩写
• YYYY：四位年份

2. 筛选符合条件的记录 #

若要包含2022年9月14日全天的记录，可将解析后的时间与TIMESTAMP '2022-09-14 23:59:59.9999'比较；若只需日期部分≤2022-09-14，可使用TRUNC函数截断时间部分后与DATE '2022-09-14'比较。

最终查询SQL #

方式一：保留毫秒精度的完整时间比较

SELECT DID, DTEXT, TO_TIMESTAMP(DTEXT, 'HH24:MI:SS:FF4 DD MON YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS parsed_datetime
FROM MY_TEST_TABLE
WHERE TO_TIMESTAMP(DTEXT, 'HH24:MI:SS:FF4 DD MON YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') <= TIMESTAMP '2022-09-14 23:59:59.9999';

方式二：仅比较日期部分

SELECT DID, DTEXT, TRUNC(TO_TIMESTAMP(DTEXT, 'HH24:MI:SS:FF4 DD MON YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')) AS parsed_date
FROM MY_TEST_TABLE
WHERE TRUNC(TO_TIMESTAMP(DTEXT, 'HH24:MI:SS:FF4 DD MON YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')) <= DATE '2022-09-14';

结果说明 #

上述查询会返回以下记录：

DID  DTEXT                              parsed_datetime/parsed_date
3    16:22:38:0570 14 SEP 2022         2022-09-14 16:22:38.057000000 / 2022-09-14
4    02:19:18:0370 14 SEP 2022         2022-09-14 02:19:18.037000000 / 2022-09-14
5    03:29:14:0330 13 SEP 2022         2022-09-13 03:29:14.033000000 / 2022-09-13

注意事项 #

添加NLS_DATE_LANGUAGE=ENGLISH参数是为了避免数据库语言环境非英文时，无法解析SEP这类英文月份缩写的问题，确保解析兼容性。

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