SQL 中日期的特殊性总结

SQL日期处理要点总结日期数据类型特性内部存储为数字天数/秒数支持加减运算和比较操作日期与字符串转换TO_DATE()将字符串转为日期TO_CHAR()将日期转为字符串需注意格式符YYYY/MM/DD等日期比较注意事项避免直接比较字符串和日期范围查询应使用左闭右开区间注意边界值问题BETWEEN可能丢失数据日期运算函数支持加减天数、月份计算等Oracle和MySQL函数存在差异最佳实践使用显式类型转换避免在WHERE左侧使用转换函数影响索引注意时区问题特别提醒日期处理时要特别注意格式转换和边界条件不当操作可能导致查询结果不准确或性能问题。SQL中日期的特殊性总结在SQL中日期是一种特殊的数据类型既有数值的特性又有字符串的表现形式使用时有诸多需要注意的地方。一、日期数据类型的特点特性说明示例存储格式内部存储为数字从某个基准日期开始的天数/秒数Oracle: 4712-01-01 起的天数显示格式由数据库参数控制不一定是输入时的格式Oracle:17-12月-80运算能力支持加减运算天数/月数/年数HIREDATE 3030天后比较能力支持, , , BETWEEN等比较操作HIREDATE TO_DATE(1981-01-01)二、日期与字符串的转换最重要核心函数函数方向用途TO_DATE(字符串, 格式)字符串 → 日期将字符串按指定格式解析为日期类型TO_CHAR(日期, 格式)日期 → 字符串将日期按指定格式转换为字符串常用日期格式元素格式符含义示例YYYY四位年份1981YY两位年份81MM两位月份05MON月份缩写中文环境为5月5月MONTH月份全称5月DD两位日期01DAY星期几星期三HH2424小时制14MI分钟30SS秒钟45示例代码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);四、日期的加减运算运算含义示例日期 数字增加天数HIREDATE 3030天后日期 - 数字减少天数HIREDATE - 77天前日期1 - 日期2相差天数SYSDATE - HIREDATE入职天数ADD_MONTHS(日期, 数字)增加月份ADD_MONTHS(HIREDATE, 6)6个月后MONTHS_BETWEEN(日期1, 日期2)相差月数MONTHS_BETWEEN(SYSDATE, HIREDATE)示例代码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功能OracleMySQL当前日期时间SYSDATENOW()/CURDATE()提取年份TO_CHAR(date, YYYY)YEAR(date)提取月份TO_CHAR(date, MM)MONTH(date)日期加减天数date 10DATE_ADD(date, INTERVAL 10 DAY)日期差天数date1 - date2DATEDIFF(date1, date2)增加月份ADD_MONTHS(date, 6)DATE_ADD(date, INTERVAL 6 MONTH)六、常见陷阱与最佳实践❌ 常见错误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写法当前系统日期SYSDATEOracle/CURDATE()MySQL年月日格式YYYY-MM-DD字符串→日期TO_DATE(1981-05-01, YYYY-MM-DD)日期→字符串TO_CHAR(HIREDATE, YYYY-MM-DD)提取年份TO_CHAR(HIREDATE, YYYY)提取年月TO_CHAR(HIREDATE, YYYYMM)某月第一天TRUNC(HIREDATE, MM)某年第一天TRUNC(HIREDATE, YYYY)月底最后一天LAST_DAY(HIREDATE)下个月同一天ADD_MONTHS(HIREDATE, 1)八、你在作业中的日期问题总结sql-- 第3题 ✅ 正确 WHERE TO_CHAR(HIREDATE, YYYYMM) 198210 -- 第5题 ⚠️ 缺少括号结果正确但不规范 WHERE DEPTNO20 AND TO_CHAR(HIREDATE,YYYY)1982 OR DEPTNO30 AND TO_CHAR(HIREDATE,YYYY)1985 -- 第11题 ❌ 完全遗漏WHERE条件 -- 应该加WHERE TO_CHAR(HIREDATE, YYYY) 1981核心要点日期比较时优先使用范围查询左闭右开TO_CHAR会让索引失效大数据量时慎用始终用显式转型不要依赖隐式转换注意边界值BETWEEN 可能丢失最后一天的末尾时间