Oracle 数字区间、日期边界问题分类总结（日期比较大小）

Oracle中不同函数对数字区间的处理方式不同FOR循环闭区间[m,n]包含结束值如1..3包含3SUBSTR从起始位置开始截取指定长度字符非结束位置特殊规则0视为第1位负数表示从末尾倒数INSTR返回子串出现的位置从1计数其他函数ROUND/TRUNC处理小数位MOD取余数POWER计算幂次关键区别FOR循环关注起止值包含性SUBSTR关注起始位置和长度INSTR返回位置索引日期处理BETWEEN对日期包含结束日00:00:00不包含时间部分推荐使用开始 AND 结束日1的方式确保包含完整时间段TRUNC函数去除时间部分LAST_DAY获取月末时间段查询应使用不等号确保边界完整关键区别数字区间关注起止值包含性日期处理需特别注意时间部分的边界问题。 Oracle 数字区间问题分类总结功能/函数语法起始位置是否包含结束位置边界规则说明示例结果FOR 循环FOR i IN m..n LOOP从 m 开始✅包含 n闭区间[m, n]i 会取到 nFOR i IN 1..3 LOOPi 1,2,3SUBSTR 截取SUBSTR(str, m, n)从第 m 位开始✅包含截取n 个字符不是到第 n 位SUBSTR(ABCDE, 2, 3)BCD位置2,3,4SUBSTR 简写SUBSTR(str, m)从第 m 位开始✅包含截取到字符串末尾SUBSTR(ABCDE, 3)CDEINSTR 查找INSTR(str, sub, m, n)从第 m 位开始搜索N/A返回值是位置返回第 n 次出现的位置从1计数INSTR(ABABA, B, 1, 2)4第2个B的位置ROUND 四舍五入ROUND(num, m)第 m 位小数N/A不是区间问题m 为正小数点后m 为负小数点前ROUND(123.456, 1)123.5TRUNC 截断TRUNC(num, m)第 m 位小数N/A不是区间问题直接截断不四舍五入TRUNC(123.456, 1)123.4MOD 取模MOD(m, n)N/AN/A返回 m 除以 n 的余数MOD(10, 3)1POWER 幂运算POWER(m, n)N/AN/A返回 m 的 n 次方POWER(2, 3)8 容易混淆的重点对比1️⃣ FOR 循环 vs SUBSTR 的结束概念对比项FOR 循环SUBSTR区间类型位置区间[m, n]长度区间从 m 开始取 n 个参数含义结束值截取长度是否包含包含结束值包含起始位置取 n 个字符sql-- FOR 循环包含 5 FOR i IN 1..5 LOOP -- i 1,2,3,4,5 -- SUBSTR第2个参数是长度不是结束位置 SUBSTR(ABCDE, 2, 3) -- 从位置2开始取3个 → BCD位置2,3,42️⃣ SUBSTR 的起始位置规则特殊起始位置行为示例字符串ABCDE结果0视为第 1 位SUBSTR(ABCDE, 0, 2)AB1正常从第 1 位开始SUBSTR(ABCDE, 1, 2)AB正数 n从第 n 位开始SUBSTR(ABCDE, 3, 2)CD负数 n从末尾倒数第 n 位开始SUBSTR(ABCDE, -2, 2)DE3️⃣ INSTR 的位置返回值sql-- INSTR 返回的是位置编号从1开始 INSTR(ABCDE, C) -- 返回 3 INSTR(ABABA, B, 1, 2) -- 从位置1开始找第2个B → 返回 4✅ 快速记忆口诀场景口诀FOR 循环两点之间两头都算SUBSTR起始位置 取几个不是到哪INSTR找到后告诉你它在第几个位置负数索引SUBSTR 倒数开始INSTR 不动示例--练习 -- 1,开发函数 ELIM函数 实现功能 ELIM(ABBBBBCD,B) 返回 -- ELIM(ABBBBCD,B) -- 干掉 第一次出现 第二个入参的内容 -- ELIM(A-B-C,-)返回 AB-C create or replace function ELIM(str varchar2,s varchar2) return varchar2 is v_res varchar2(100); v_start number; begin if instr(str,s)0 then return str; else v_start:instr(str,s,1,1); --拼接字符串 v_res:substr(str,1,v_start-1)||substr(str,v_start1); dbms_output.put_line(v_res); return v_res; end if; end; --单独执行 select 不要选中 自定义的函数 一起执行 select ELIM(A-B-C,-)as ELIM from dual; Oracle 日期边界问题分类总结一、BETWEEN...AND 的日期边界场景语法示例是否包含结束日期实际等价条件说明日期类型无时间WHERE date_col BETWEEN DATE 2024-01-01 AND DATE 2024-01-31✅包含date_col DATE 2024-01-01 AND date_col DATE 2024-01-31包含结束日期当天 00:00:00日期类型有时间WHERE date_col BETWEEN DATE 2024-01-01 AND DATE 2024-01-31⚠️不包含31日的时间部分实际只到2024-01-31 00:00:00常见陷阱2024-01-31 14:30:00不会被查到时间戳类型WHERE ts_col BETWEEN TIMESTAMP 2024-01-01 00:00:00 AND TIMESTAMP 2024-01-31 23:59:59✅完全包含明确指定了时间边界需要精确到秒或毫秒二、常见的日期边界处理方式需求场景错误写法陷阱正确写法说明查询某一天的数据WHERE date_col DATE 2024-01-31WHERE date_col DATE 2024-01-31 AND date_col DATE 2024-02-01等于只查当天 00:00:00漏掉有时间的数据BETWEEN 查整月WHERE date_col BETWEEN DATE 2024-01-01 AND DATE 2024-01-31WHERE date_col DATE 2024-01-01 AND date_col DATE 2024-02-01BETWEEN 不包含1月31日的时间部分查询本月至今WHERE date_col BETWEEN TRUNC(SYSDATE, MM) AND SYSDATEWHERE date_col TRUNC(SYSDATE, MM) AND date_col SYSDATE 1BETWEEN 可能漏掉今天的部分时间三、日期函数边界规则函数语法边界规则示例以 2024-01-31 14:30:45 为例结果TRUNCTRUNC(date, unit)截断到指定单位的开始TRUNC(SYSDATE, MM)2024-01-01 00:00:00TRUNCTRUNC(date, DD)截断到当天的开始TRUNC(SYSDATE)2024-01-31 00:00:00LAST_DAYLAST_DAY(date)返回月份最后一天的 00:00:00LAST_DAY(DATE 2024-01-31)2024-01-31 00:00:00ADD_MONTHSADD_MONTHS(date, n)月份边界自动处理ADD_MONTHS(DATE 2024-01-31, 1)2024-02-29 00:00:00闰年NEXT_DAYNEXT_DAY(date, weekday)返回下一个指定星期几NEXT_DAY(DATE 2024-01-31, MONDAY)下一个周一 00:00:00四、常见的时间段区间写法最佳实践时间段包含边界写法说明某一天date_col DATE 2024-01-31 AND date_col DATE 2024-02-01✅ 推荐包含当天所有时间某一月date_col DATE 2024-01-01 AND date_col DATE 2024-02-01✅ 推荐包含整月某一小时date_col TIMESTAMP 2024-01-31 14:00:00 AND date_col TIMESTAMP 2024-01-31 15:00:00✅ 包含14:00:00到14:59:59本周date_col TRUNC(SYSDATE, IW) AND date_col TRUNC(SYSDATE, IW) 7周一到周日ISO标准本月date_col TRUNC(SYSDATE, MM) AND date_col ADD_MONTHS(TRUNC(SYSDATE, MM), 1)整月本季度date_col TRUNC(SYSDATE, Q) AND date_col ADD_MONTHS(TRUNC(SYSDATE, Q), 3)整季度本年date_col TRUNC(SYSDATE, YY) AND date_col ADD_MONTHS(TRUNC(SYSDATE, YY), 12)整年五、BETWEEN 与日期使用的具体示例❌ 错误示例常见陷阱sql-- 查询 2024-01-31 的所有订单 SELECT * FROM orders WHERE order_date BETWEEN DATE 2024-01-31 AND DATE 2024-01-31; -- 结果只查出 order_date 2024-01-31 00:00:00 的订单 -- 漏掉2024-01-31 14:30:00 的订单✅ 正确示例sql-- 方法1使用 和 推荐 SELECT * FROM orders WHERE order_date DATE 2024-01-31 AND order_date DATE 2024-02-01; -- 方法2使用 TRUNC但会索引失效 SELECT * FROM orders WHERE TRUNC(order_date) DATE 2024-01-31; -- 方法3精确到秒的 BETWEEN SELECT * FROM orders WHERE order_date BETWEEN TIMESTAMP 2024-01-31 00:00:00 AND TIMESTAMP 2024-01-31 23:59:59;六、日期边界核心要点速查表问题答案BETWEEN 包含结束日期吗✅ 包含但只包含结束日期的00:00:00如何包含某一天的所有时间使用 当天 AND 明天如何包含某一整月使用 当月1号 AND 下月1号DATE 类型有时间部分吗✅ 有Oracle DATE 包含时分秒TRUNC 作用是什么去掉时间部分归零到 00:00:00判断是否同一天TRUNC(date1) TRUNC(date2)✅ 记忆口诀BETWEEN 虽包含只到零点不算妙要查整天用不等大于等于小于明天好月份区间也同理上月1号到下月1号TRUNC 归零去时间比较日期最可靠日期比较大小在 Oracle 中比较日期大小有多种方式取决于你的具体需求。下面整理了几种常见的方法1. 基本比较运算符直接使用,,,,,比较DATE类型sql-- 查询某天之后的记录 SELECT * FROM orders WHERE order_date DATE 2024-01-01; -- 查询某天之前的记录 SELECT * FROM orders WHERE order_date SYSDATE; -- 查询某天当天的记录 SELECT * FROM orders WHERE order_date DATE 2024-03-15; -- 注意DATE 包含时间等于判断需要精确到秒 SELECT * FROM orders WHERE order_date TO_DATE(2024-03-15 14:30:00, YYYY-MM-DD HH24:MI:SS);2. 忽略时间部分只比较日期如果字段包含时间只想比较年月日sql-- 方法1TRUNC 函数 SELECT * FROM orders WHERE TRUNC(order_date) DATE 2024-03-15; -- 方法2TO_CHAR 格式化 SELECT * FROM orders WHERE TO_CHAR(order_date, YYYY-MM-DD) 2024-03-15; -- 方法3日期范围性能较好 SELECT * FROM orders WHERE order_date DATE 2024-03-15 AND order_date DATE 2024-03-16;3. 在查询条件中比较sql-- 获取过去7天的数据 SELECT * FROM orders WHERE order_date SYSDATE - 7; -- 获取本月数据 SELECT * FROM orders WHERE order_date TRUNC(SYSDATE, MM) AND order_date ADD_MONTHS(TRUNC(SYSDATE, MM), 1); -- 获取今年数据 SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) EXTRACT(YEAR FROM SYSDATE);4. 在 CASE / DECODE 中比较sqlSELECT order_id, order_date, CASE WHEN order_date SYSDATE THEN Future WHEN order_date TRUNC(SYSDATE) THEN Today ELSE Past END AS status FROM orders;5. 注意事项问题说明建议OracleDATE包含年、月、日、时、分、秒比较必须完全匹配用范围查询或TRUNC索引使用TRUNC(col)会使索引失效用col BETWEEN start AND end时区TIMESTAMP WITH TIME ZONE需转换用CAST或AT TIME ZONE空值任何NULL比较返回UNKNOWN加上IS NULL检查6. 完整示例sql-- 创建测试表 CREATE TABLE test_dates ( id NUMBER, event_date DATE ); INSERT INTO test_dates VALUES (1, DATE 2024-01-15); INSERT INTO test_dates VALUES (2, TO_DATE(2024-03-20 08:30:00, YYYY-MM-DD HH24:MI:SS)); INSERT INTO test_dates VALUES (3, SYSDATE); -- 查询示例 SELECT * FROM test_dates WHERE event_date DATE 2024-02-01; SELECT * FROM test_dates WHERE TRUNC(event_date) DATE 2024-01-15; SELECT * FROM test_dates WHERE event_date BETWEEN DATE 2024-01-01 AND DATE 2024-01-31;