Oralce 日期型函数使用

---

一常用函数

---

1.sysdate获取系统时间

SQL> --获取当前时间 yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;
 
SYSDATE
-----------
2022/9/21 16:47:52

2.to_date函数将字符转换为日期

TO_DATE格式常用格式YYYY-MM-DD HH24:MI:SS

SQL> --to_date函数将字符转换为日期
SQL> SELECT TO_DATE('2014-07-11 13:45:23', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
 
TO_DATE('2014-07-1113:45:23','
------------------------------
2014/7/11 13:45:23

3.to_char函数，将时间转换为字符

   格式：to_char(date) ，该函数将一个日期型数据转换成一个字符型数据  

SQL> --日期转化为字符串 
SQL> select to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') from dual;
 
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2022-09-21 16:21:49

2.其他常用的to_char转换

SQL> --获取时间的年   
SQL> select to_char(sysdate,'yyyy')from dual;
 
TO_CHAR(SYSDATE,'YYYY')
-----------------------
2022
SQL> --获取时间的月   
SQL> select to_char(sysdate,'MM') from dual;
 
TO_CHAR(SYSDATE,'MM')
---------------------
09
SQL> --获取时间的日   
SQL> select to_char(sysdate,'dd') from dual;
 
TO_CHAR(SYSDATE,'DD')
---------------------
21
SQL> --获取时间的时     
SQL> select to_char(sysdate,'hh24') from dual;
 
TO_CHAR(SYSDATE,'HH24')
-----------------------
16
SQL> --获取时间的分   
SQL> select to_char(sysdate,'mi') from dual;
 
TO_CHAR(SYSDATE,'MI')
---------------------
21
SQL> --获取时间的秒 
SQL> select to_char(sysdate,'ss') from dual;
 
TO_CHAR(SYSDATE,'SS')
---------------------
50

4.trunc函数获取某天0点0分0秒

   格式：trunc(date)

SQL> --trunc获取某天0点0分0秒
SQL> select trunc(sysdate) from dual;
 
TRUNC(SYSDATE)
--------------
2022/9/21

5.add_month函数，增加N个月

  格式：add_month(date,integer) ,integer表示要加的月份，如果是负值就表示要减的月份

SQL> --获取下N个月 yyyy-mm-dd hh24:mi:ss
SQL> select add_months(sysdate,1) from dual;
 
ADD_MONTHS(SYSDATE,1)
---------------------
2022/10/21 16:30:48
SQL> --获取前N个月 yyyy-mm-dd hh24:mi:ss
SQL> select add_months(sysdate,-1) from dual;
 
ADD_MONTHS(SYSDATE,-1)
----------------------
2022/8/21 16:30:48
SQL> --获取下N个月第一天0点 yyyy-mm-dd
SQL> select trunc(add_months(sysdate,1)) from dual;
 
TRUNC(ADD_MONTHS(SYSDATE,1))
----------------------------
2022/10/21
SQL> --获取下N个月最后1天 yyyy-mm-dd hh24:mi:ss
SQL> select last_day(add_months(sysdate,1)) from dual;
 
LAST_DAY(ADD_MONTHS(SYSDATE,1)
------------------------------
2022/10/31 16:30:48

6.last_day函数获取月份最后1天

  格式：last_day(date)

SQL> --获取某月最后1天
SQL> SELECT LAST_DAY(SYSDATE) FROM dual;
 
LAST_DAY(SYSDATE)
-----------------
2022/9/30 16:50:0

7.next_day获取指定日期后一周的日期 

  格式：next_day(date,char), char表示星期几

SQL> select sysdate, next_day(sysdate,'星期一') from dual;
 
SYSDATE     NEXT_DAY(SYSDATE,'星期一')
----------- --------------------------
2022/9/21 1 2022/9/26 17:18:36

8.floor函数求两个时间间隔天数

SQL> --当前时间:2022/9/21 17:03:04
SQL> select floor(sysdate-to_date('20220921','yyyymmdd')) from dual;
 
FLOOR(SYSDATE-TO_DATE('2022092
------------------------------
                             0
SQL> select floor(sysdate-to_date('20220920','yyyymmdd')) from dual;
 
FLOOR(SYSDATE-TO_DATE('2022092
------------------------------
                             1

9.months_between计算月份差

SQL> --计算月份差
SQL> select months_between(to_date('2022-01-01','yyyy-mm-dd') ,to_date('2022-10-01','yyyy-mm-dd') )from dual;
 
MONTHS_BETWEEN(TO_DATE('2022-0
------------------------------
                            -9

---

二常见问题

---

1.Oracle使用mi代替分钟，如果使用mm格式报错ORA-01810

 SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟,如果分使用mm会出现ORA-01810错误

SQL> --错误格式
SQL> select to_date('2015-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;
 
select to_date('2015-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual
 
ORA-01810: format code appears twice
 

SQL> --正确格式
SQL> select to_date('2015-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
 
TO_DATE('2015-01-0113:14:20','
------------------------------
2015/1/1 13:14:20

2. 转换日期：26-9月 -17 06.46.00.000000000 下午

SQL> --将26-9月 -17 06.46.00.000000000 下午 转换成yyyy-mm-dd hh24:mi:ss
SQL> select to_char(cast(cast('26-9月 -17 06.46.00.000000000 下午' as timestamp) as date),'yyyy-mm-dd hh24:mi:ss') dateStr from dual;
 
DATESTR
-------------------
2017-09-26 18:46:00


SQL> --将yyyy-mm-dd hh24:mi:ss 转换成 26-9月 -17 06.46.00.000000000 下午
SQL> select to_timestamp('2017-09-26 18:46:00','yyyy-mm-dd hh24:mi:ss') from dual;
 
TO_TIMESTAMP('2017-09-2618:46:
--------------------------------------------------------------------------------
26-9月 -17 06.46.00.000000000 下午

                          

---

前一篇：Oracle死锁问题： enq: TX - row lock contention

---