SQL 中日期的特殊性总结

SQL日期处理要点总结：

1. 日期数据类型特性：

• 内部存储为数字（天数/秒数）
• 支持加减运算和比较操作

1. 日期与字符串转换：

• TO_DATE()将字符串转为日期
• TO_CHAR()将日期转为字符串
• 需注意格式符（YYYY/MM/DD等）

1. 日期比较注意事项：

• 避免直接比较字符串和日期
• 范围查询应使用左闭右开区间
• 注意边界值问题（BETWEEN可能丢失数据）

1. 日期运算函数：

• 支持加减天数、月份计算等
• Oracle和MySQL函数存在差异

1. 最佳实践：

• 使用显式类型转换
• 避免在WHERE左侧使用转换函数（影响索引）
• 注意时区问题

---

特别提醒：日期处理时要特别注意格式转换和边界条件，不当操作可能导致查询结果不准确或性能问题。

SQL中日期的特殊性总结

在SQL中，日期是一种特殊的数据类型，既有数值的特性，又有字符串的表现形式，使用时有诸多需要注意的地方。

一、日期数据类型的特点

二、日期与字符串的转换（最重要）

核心函数

常用日期格式元素

示例代码

sql

-- TO_DATE：字符串转日期 TO_DATE('1981-05-01', 'YYYY-MM-DD') -- 返回日期：1981年5月1日 TO_DATE('19810501', 'YYYYMMDD') -- 返回日期：1981年5月1日 TO_DATE('1981-05', 'YYYY-MM') -- 返回日期：1981年5月1日（默认当月1号） -- TO_CHAR：日期转字符串 TO_CHAR(HIREDATE, 'YYYY-MM-DD') -- '1981-05-01' TO_CHAR(HIREDATE, 'YYYYMM') -- '198105' TO_CHAR(HIREDATE, 'MON DD, YYYY') -- '5月 01, 1981'

三、日期比较的特殊性

1. 不能直接用字符串比较日期

sql

-- ❌ 错误：字符串 '1981' 和日期类型不能直接比较 SELECT * FROM EMP WHERE HIREDATE = '1981'; -- ✅ 正确方式1：转换日期为字符串比较 SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981'; -- ✅ 正确方式2：字符串转日期比较 SELECT * FROM EMP WHERE HIREDATE >= TO_DATE('1981-01-01', 'YYYY-MM-DD') AND HIREDATE < TO_DATE('1982-01-01', 'YYYY-MM-DD');

2. 日期比较的边界问题（重要⚠️）

sql

-- 查询1981年入职的员工（错误写法） SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = 1981; -- ✅ 可行，但效率低 -- 查询1981年入职的员工（正确写法 - 使用范围） SELECT * FROM EMP WHERE HIREDATE >= TO_DATE('1981-01-01', 'YYYY-MM-DD') AND HIREDATE < TO_DATE('1982-01-01', 'YYYY-MM-DD'); -- 查询1981年5月入职（错误写法） WHERE HIREDATE BETWEEN TO_DATE('1981-05-01', 'YYYY-MM-DD') AND TO_DATE('1981-05-31', 'YYYY-MM-DD'); -- ⚠️ 漏掉了5月31日23:59:59之后的数据 -- 查询1981年5月入职（正确写法） WHERE HIREDATE >= TO_DATE('1981-05-01', 'YYYY-MM-DD') AND HIREDATE < TO_DATE('1981-06-01', 'YYYY-MM-DD');

四、日期的加减运算

示例代码

sql

-- 计算员工入职天数 SELECT ENAME, SYSDATE - HIREDATE AS 工作天数 FROM EMP; -- 计算员工入职月数 SELECT ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE) AS 工作月数 FROM EMP; -- 查询入职超过30年的员工 SELECT * FROM EMP WHERE ADD_MONTHS(HIREDATE, 30*12) < SYSDATE;

五、日期函数对比（Oracle vs MySQL）

六、常见陷阱与最佳实践

❌ 常见错误

sql

-- 1. 直接比较字符串和日期 WHERE HIREDATE = '1981-05-01' -- 隐式转换可能失败 -- 2. 使用 BETWEEN 包含结束日期（会丢失当天23:59:59后的数据） WHERE HIREDATE BETWEEN '1981-05-01' AND '1981-05-31' -- 3. TO_CHAR 写在 WHERE 条件的左边（无法使用索引） WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981' -- 4. 忽略时区问题 WHERE CREATE_TIME = '2026-04-23' -- 可能漏掉带时分秒的记录

✅ 最佳实践

sql

-- 1. 始终使用显式转换 WHERE HIREDATE >= TO_DATE('1981-05-01', 'YYYY-MM-DD') AND HIREDATE < TO_DATE('1981-06-01', 'YYYY-MM-DD') -- 2. 范围查询使用左闭右开区间 WHERE HIREDATE >= TRUNC(SYSDATE - 30) -- 30天前零点 AND HIREDATE < TRUNC(SYSDATE) -- 今天零点 -- 3. 让函数作用在常量上，保持索引有效 WHERE HIREDATE >= TO_DATE('1981-01-01', 'YYYY-MM-DD') -- ✅ 索引有效 WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981' -- ❌ 索引失效 -- 4. 使用 TRUNC 去掉时间部分 WHERE TRUNC(HIREDATE) = TO_DATE('1981-05-01', 'YYYY-MM-DD')

七、快速参考卡片

八、你在作业中的日期问题总结

sql

-- 第3题 ✅ 正确 WHERE TO_CHAR(HIREDATE, 'YYYYMM') < 198210 -- 第5题 ⚠️ 缺少括号（结果正确但不规范） WHERE DEPTNO=20 AND TO_CHAR(HIREDATE,'YYYY')<1982 OR DEPTNO=30 AND TO_CHAR(HIREDATE,'YYYY')<1985 -- 第11题 ❌ 完全遗漏WHERE条件 -- 应该加：WHERE TO_CHAR(HIREDATE, 'YYYY') > 1981

核心要点：

1. 日期比较时，优先使用范围查询（左闭右开）

2. TO_CHAR会让索引失效，大数据量时慎用

3. 始终用显式转型，不要依赖隐式转换

4. 注意边界值，BETWEEN 可能丢失最后一天的末尾时间