原文链接:https://www.oracletutorial.com/oracle-date-functions/
- ADD_MONTHS(date,int) 将一个月数(n)添加到日期,然后返回相隔n个月的同一天。
SQL> select ADD_MONTHS(TO_DATE('2020-02-29', 'yyyy-mm-dd'), 1) from dual;
> 2020-03-31 00:00:00
SQL> SELECT ADD_MONTHS(TO_DATE('2020-03-01', 'yyyy-mm-dd'), 1) from dual;
> 2020-04-01 00:00:00
SQL> SELECT ADD_MONTHS(TO_DATE('2020-03-01', 'yyyy-mm-dd'), -1) from dual;
> 2020-02-01 00:00:00
- CURRENT_DATE/LOCALTIMESTAMP/SYSDATE返回会话时区中的当前日期和时间
SQL> SELECT CURRENT_DATE FROM dual; > 2020-02-29 21:35:05 SQL> SELECT LOCALTIMESTAMP FROM dual; > 2020-02-29 21:35:27.344333 SQL> SELECT SYSDATE FROM dual; > 2020-02-29 21:35:51
- CURRENT_TIMESTAMP/SYSTIMESTAMP 返回当前日期和时间以及会话时区中的时区
SQL> SELECT CURRENT_TIMESTAMP FROM dual > 2020-02-29 21:14:29.226492 +08:00 SQL> SELECT SYSTIMESTAMP FROM dual; > 2020-02-29 21:36:06.249039 +08:00
- EXTRACT 从日期时间值中提取日期时间字段的值,例如YEAR,MONTH,DAY,…。
SQL> SELECT EXTRACT(YEAR FROM SYSDATE)year,EXTRACT(MONTH FROM SYSDATE)month,EXTRACT(DAY FROM SYSDATE)day,EXTRACT(HOUR FROM SYSTIMESTAMP)hour,EXTRACT(MINUTE FROM SYSTIMESTAMP)minute,EXTRACT(SECOND FROM SYSTIMESTAMP)second FROM dual;
YEAR MONTH DAY HOUR MINUTE SECOND ---------- ---------- ---------- ---------- ---------- ---------- 2020 2 29 13 24 29.951594
- LAST_DAY(date) 获取指定日期的月份的最后一天。
SQL> SELECT LAST_DAY(TO_DATE('2020-02-01', 'yyyy-mm-dd')) FROM dual; > 2020-02-29 00:00:00
- MONTHS_BETWEEN(date1,date2) 返回两个日期之间的月数差
SQL> SELECT MONTHS_BETWEEN(TO_DATE('2020-07-01','yyyy-mm-dd'),TO_DATE('2020-04-01','yyyy-mm-dd')) FROM dual; > 3 SQL> SELECT MONTHS_BETWEEN(TO_DATE('2020-07-23','yyyy-mm-dd'),TO_DATE('2020-04-01','yyyy-mm-dd')) FROM dual; > 3.70967741935483870967741935483870967742
- NEXT_DAY( date, string) string是周一到周日的英文,返回当前日期下一个周几的时间
SQL> SELECT NEXT_DAY(TO_DATE('2020-02-29', 'yyyy-MM-dd'), 'Friday') FROM dual; --2月29日是周六,下个周五是3月6号 > 2020-03-06 00:00:00
- ROUND(date,fmt) 将日期date按照由fmt指定的格式进行四舍五入处理.如果没有给fmt则使用缺省设置'DD'
SQL> SELECT ROUND(TO_DATE('2020-02-15', 'yyyy-MM-dd'), 'yyyy') FROM dual; --如果位于上半年,则舍去,如果位于下半年,则+1年,以下同理 > 2020-01-01 00:00:00 SQL> SELECT ROUND(TO_DATE('2020-07-15', 'yyyy-MM-dd'), 'yyyy') FROM dual; > 2021-01-01 00:00:00 SQL> SELECT ROUND(TO_DATE('2020-07-15', 'yyyy-MM-dd'), 'MM') FROM dual; > 2020-07-01 00:00:00 SQL> SELECT ROUND(TO_DATE('2020-02-25', 'yyyy-MM-dd'), 'MM') FROM dual; > 2020-03-01 00:00:00 SQL> SELECT ROUND(TO_DATE('2020-02-25 15:00:00', 'yyyy-MM-dd hh24:mi:ss')) FROM dual; > 2020-02-26 00:00:00 SQL> SELECT ROUND(TO_DATE('2020-02-25 08:00:00', 'yyyy-MM-dd hh24:mi:ss')) FROM dual; > 2020-02-25 00:00:00
- TO_CHAR(date,str) 将时间转换为指定的格式
SQL> SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual; > 2020-03-01 00:06:48 SQL> SELECT TO_CHAR(SYSDATE) FROM dual; > 01-MAR-20
- TO_DATE(ch, fmt) 将字符串安装格式转换为时间
SQL> SELECT TO_DATE('2020-03-01','yyyy-MM-dd' ) FROM dual; > 2020-03-01 00:00:00 SQL> SELECT TO_DATE('2020-03-01 12:03:44','yyyy-MM-dd hh24:mi:ss' ) FROM dual; > 2020-03-01 12:03:44
更多地参考官方文档