日期的截取
ROUND()函数与TRUNC()函数除了可以作用在数值上,也可以作用在日期上,对于日期也同样可以四舍五入或截断
ROUND(参数1,参数2)/TRUNC(参数1,参数2)
参数1:需要处理的数据
参数2:将数据按照参数2的格式进行处理
格式:YYYY MM DD HH24/HH12 MI SS DAY/IW
一.ROUND()四舍五入
1.对年份处理 以七月为界 七月之前返回当前年份第一天,七月之后返回下个年份第一天
示例:
SELECT TO_DATE('2020/04/26','YYYY-MM-DD') FROM DUAL;
SELECT ROUND(TO_DATE('2020/04/26','YYYY-MM-DD'),'YYYY') FROM DUAL;--2020/01/01
SELECT ROUND(TO_DATE('2020/07/26','YYYY-MM-DD'),'YYYY') FROM DUAL;--2021/01/01
SELECT ROUND(TO_DATE('2020/06/30 23:59:59','YYYY-MM-DD HH24:MI:SS'),'YYYY') FROM DUAL;--2020/01/01
SELECT ROUND(TO_DATE('2020/07/01 00:00:00','YYYY-MM-DD HH24:MI:SS'),'YYYY') FROM DUAL;--2021/01/01
2.对月份处理 以16为界 16之前返回当前月份第一天,16之后返回下个月份第一天
示例:
SELECT TO_DATE('2020/04/26','YYYY-MM-DD') FROM DUAL;
SELECT ROUND(TO_DATE('2020/04/26','YYYY-MM-DD'),'MM') FROM DUAL;--2020/05/01
SELECT ROUND(TO_DATE('2020/04/15','YYYY-MM-DD'),'MM') FROM DUAL;--2020/04/01
SELECT ROUND(TO_DATE('2020/06/15 23:59:59','YYYY-MM-DD HH24:MI:SS'),'MM') FROM DUAL;--2020/06/01
SELECT ROUND(TO_DATE('2020/06/16 00:00:00','YYYY-MM-DD HH24:MI:SS'),'MM') FROM DUAL;--2020/07/01
3.对日处理 以12点为界 12点之前返回当前日期,12之后返回下一天日期
示例:
SELECT TO_DATE('2020/04/26','YYYY-MM-DD') FROM DUAL;
SELECT ROUND(TO_DATE('2020/04/26 11:59:59','YYYY-MM-DD HH24:MI:SS'),'DD') FROM DUAL;--2020/04/26
SELECT ROUND(TO_DATE('2020/04/26 12:00:00','YYYY-MM-DD HH24:MI:SS'),'DD') FROM DUAL;--2020/04/27
4.对小时处理 以30分为界 30之前返回当前时间,30之后返回下一小时时间
示例:
SELECT TO_DATE('2020/04/26','YYYY-MM-DD') FROM DUAL;
SELECT ROUND(TO_DATE('2020/04/26 12:29:59','YYYY-MM-DD HH24:MI:SS'),'HH24') FROM DUAL;--2020/04/26 12:00:00
SELECT ROUND(TO_DATE('2020/04/26 12:30:00','YYYY-MM-DD HH24:MI:SS'),'HH24') FROM DUAL;--2020/04/26 13:00:00
–分钟 秒均以此原理
5.DAY 以周三的12点为界 12点之前返回本周第一天 12点之后返回下一周的第一天 (第一天以周日开始)
示例:
SELECT ROUND(TO_DATE('2021/01/20 11:59:59','YYYY-MM-DD HH24:MI:SS'),'DAY') FROM DUAL;--2021/01/17
SELECT ROUND(TO_DATE('2021/01/20 12:00:00','YYYY-MM-DD HH24:MI:SS'),'DAY') FROM DUAL;--2021/01/24
6.IW 以周四的12点为界 12点之前返回本周第一天 12点之后返回下一周的第一天 (第一天以周一开始)
示例:
SELECT ROUND(TO_DATE('2021/01/20 11:59:59','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/18
SELECT ROUND(TO_DATE('2021/01/20 12:00:00','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/18
SELECT ROUND(TO_DATE('2021/01/21 11:59:59','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/18
SELECT ROUND(TO_DATE('2021/01/21 12:00:00','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/25
SELECT ROUND(TO_DATE('2021/01/21 11:59:59','YYYY-MM-DD HH12:MI:SS'),'IW') FROM DUAL;--2021/01/18
SELECT ROUND(TO_DATE('2021/01/21 01:00:00 下午','YYYY-MM-DD HH12:MI:SS PM'),'IW') FROM DUAL;--2021/01/25
二.TRUNC() 截取
只返回当前精度的初始时间,用于查找本周、当年、本月第一天的日期
示例:
SELECT TRUNC(TO_DATE('2020/04/26','YYYY-MM-DD'),'YYYY') FROM DUAL;--2020/01/01
SELECT TRUNC(TO_DATE('2020/07/26','YYYY-MM-DD'),'YYYY') FROM DUAL;--2020/01/01
SELECT TRUNC(TO_DATE('2020/06/15 23:59:59','YYYY-MM-DD HH24:MI:SS'),'MM') FROM DUAL;--2020/06/01
SELECT TRUNC(TO_DATE('2020/06/16 00:00:00','YYYY-MM-DD HH24:MI:SS'),'MM') FROM DUAL;--2020/06/01
SELECT TRUNC(TO_DATE('2021/01/20 11:59:59','YYYY-MM-DD HH24:MI:SS'),'DAY') FROM DUAL;--2021/01/17
SELECT TRUNC(TO_DATE('2021/01/20 12:00:00','YYYY-MM-DD HH24:MI:SS'),'DAY') FROM DUAL;--2021/01/17
SELECT TRUNC(TO_DATE('2021/01/21 11:59:59','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/18
SELECT TRUNC(TO_DATE('2021/01/21 12:00:00','YYYY-MM-DD HH24:MI:SS'),'IW') FROM DUAL;--2021/01/18
思考:假设有一张销售业绩表,查询上个周的业绩总额(时间以今天为基)
SELECT SUM(AMOUNT)
FROM SALES
WHERE STIME BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND
TRUNC(SYSDATE, 'IW') - 1;