21验证TRUNC()函数
Oracle
SQL> select TRUNC(789.652) 截取小数, TRUNC(789.652,2) 截取两位小数,TRUNC(789.652,-2) 取整 from dual;
截取小数 截取两位小数 取整
---------- ------------ ----------
789 789.65 700PPAS
scott=# select TRUNC(789.652) 截取小数, TRUNC(789.652,2) 截取两位小数,TRUNC(789.652,-2) 取整 from dual;
截取小数 | 截取两位小数 | 取整
----------+--------------+------
789 | 789.65 | 700
(1 row)22验证MOD()函数
Oracle
SQL> select MOD(10,3) from dual;
MOD(10,3)
----------
1PPAS
scott=# select MOD(10,3) from dual;
mod
-----
1
(1 row)日期函数
23取得当前的系统时间
Oracle
SQL> select sysdate from dual;
SYSDATE
------------
20-JUN-16PPAS
scott=# select sysdate from dual;
sysdate
--------------------
20-JUN-16 15:28:18
(1 row)24修改日期显示格式
Oracle
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-06-20 23:39:47PPAS
scott=# alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
ALTER SESSION
scott=# select sysdate from dual;
sysdate
---------------------
2016-06-20 15:38:51
(1 row)25查询距离今天为止3天之后及3天之前的日期
Oracle
SQL> select SYSDATE + 3 三天之后的日期,SYSDATE -3 三天之前的日期 from dual;
三天之后的日期 三天之前的日期
------------------- -------------------
2016-06-23 23:45:26 2016-06-17 23:45:26PPAS
scott=# select SYSDATE + 3 三天之后的日期,SYSDATE -3 三天之前的日期 from dual;
三天之后的日期 | 三天之前的日期
---------------------+---------------------
2016-06-23 15:42:35 | 2016-06-17 15:42:35
(1 row)26查询出每个雇员到今天为止的雇佣天数，以及十天前每个雇员的雇佣天数
Oracle
SQL> select empno 雇员编号,ename 雇员姓名,SYSDATE-hiredate 雇佣天数,
2 (SYSDATE-10)-hiredate 十天前雇佣天数 from emp;
雇员编号 雇员姓名 雇佣天数 十天前雇佣天数
---------- -------------------- ---------- --------------
7369 SMITH 12969.9935 12959.9935
7499 ALLEN 12904.9935 12894.9935
7521 WARD 12902.9935 12892.9935
7566 JONES 12863.9935 12853.9935
7654 MARTIN 12684.9935 12674.9935
7698 BLAKE 12834.9935 12824.9935
7782 CLARK 12795.9935 12785.9935
7788 SCOTT 10655.9935 10645.9935
7839 KING 12634.9935 12624.9935
7844 TURNER 12704.9935 12694.9935
7876 ADAMS 10621.9935 10611.9935
雇员编号 雇员姓名 雇佣天数 十天前雇佣天数
---------- -------------------- ---------- --------------
7900 JAMES 12618.9935 12608.9935
7902 FORD 12618.9935 12608.9935
7934 MILLER 12567.9935 12557.9935
14 rows selected.PPAS
scott=# select empno 雇员编号,ename 雇员姓名,SYSDATE-hiredate 雇佣天数,
scott-# (SYSDATE-10)-hiredate 十天前雇佣天数 from emp;
雇员编号 | 雇员姓名 | 雇佣天数 | 十天前雇佣天数
----------+----------+---------------------+---------------------
7369 | SMITH | 12969 days 15:49:54 | 12959 days 15:49:54
7499 | ALLEN | 12904 days 15:49:54 | 12894 days 15:49:54
7521 | WARD | 12902 days 15:49:54 | 12892 days 15:49:54
7566 | JONES | 12863 days 15:49:54 | 12853 days 15:49:54
7654 | MARTIN | 12684 days 15:49:54 | 12674 days 15:49:54
7698 | BLAKE | 12834 days 15:49:54 | 12824 days 15:49:54
7782 | CLARK | 12795 days 15:49:54 | 12785 days 15:49:54
7788 | SCOTT | 10655 days 15:49:54 | 10645 days 15:49:54
7839 | KING | 12634 days 15:49:54 | 12624 days 15:49:54
7844 | TURNER | 12704 days 15:49:54 | 12694 days 15:49:54
7876 | ADAMS | 10621 days 15:49:54 | 10611 days 15:49:54
7900 | JAMES | 12618 days 15:49:54 | 12608 days 15:49:54
7902 | FORD | 12618 days 15:49:54 | 12608 days 15:49:54
7934 | MILLER | 12567 days 15:49:54 | 12557 days 15:49:54
(14 rows)27在查询结果中使用TRUNC()函数完成将小数点之后的内容全部清除
Oracle
SQL> select empno 雇员编号,ename 雇员姓名,
2 TRUNC(SYSDATE-hiredate) 雇佣天数,
3 TRUNC((SYSDATE-10)-hiredate) 十天前雇佣天数
4 from emp;
雇员编号 雇员姓名 雇佣天数 十天前雇佣天数
---------- -------------------- ---------- --------------
7369 SMITH 12970 12960
7499 ALLEN 12905 12895
7521 WARD 12903 12893
7566 JONES 12864 12854
7654 MARTIN 12685 12675
7698 BLAKE 12835 12825
7782 CLARK 12796 12786
7788 SCOTT 10656 10646
7839 KING 12635 12625
7844 TURNER 12705 12695
7876 ADAMS 10622 10612
雇员编号 雇员姓名 雇佣天数 十天前雇佣天数
---------- -------------------- ---------- --------------
7900 JAMES 12619 12609
7902 FORD 12619 12609
7934 MILLER 12568 12558
14 rows selected.PPAS
Oracle迁移PPAS(PostgreSQL)改造点
scott=# select empno 雇员编号,ename 雇员姓名,
scott-# EXTRACT(DAY FROM SYSDATE-hiredate) 雇佣天数,
scott-# EXTRACT(DAY FROM (SYSDATE-10)-hiredate) 十天前雇佣天数
scott-# from emp;
雇员编号 | 雇员姓名 | 雇佣天数 | 十天前雇佣天数
----------+----------+----------+----------------
7369 | SMITH | 12970 | 12960
7499 | ALLEN | 12905 | 12895
7521 | WARD | 12903 | 12893
7566 | JONES | 12864 | 12854
7654 | MARTIN | 12685 | 12675
7698 | BLAKE | 12835 | 12825
7782 | CLARK | 12796 | 12786
7788 | SCOTT | 10656 | 10646
7839 | KING | 12635 | 12625
7844 | TURNER | 12705 | 12695
7876 | ADAMS | 10622 | 10612
7900 | JAMES | 12619 | 12609
7902 | FORD | 12619 | 12609
7934 | MILLER | 12568 | 12558
(14 rows)28验证ADD_MONTHS()函数
Oracle
SQL> select SYSDATE,
2 ADD_MONTHS(SYSDATE,3) 三个月之后的日期,
3 ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,
4 ADD_MONTHS(SYSDATE,60) 六十日之后的日期
5 from dual;
SYSDATE 三个月之后的日期 三个月之前的日期 六十日之后的日期
------------------- ------------------- ------------------- -------------------
2016-06-21 00:24:08 2016-09-21 00:24:08 2016-03-21 00:24:08 2021-06-21 00:24:08PPAS
scott=# select SYSDATE,
scott-# ADD_MONTHS(SYSDATE,3) 三个月之后的日期,
scott-# ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,
scott-# ADD_MONTHS(SYSDATE,60) 六十日之后的日期
scott-# from dual;
sysdate | 三个月之后的日期 | 三个月之前的日期 | 六十日之后的日期
---------------------+---------------------+---------------------+---------------------
2016-06-20 16:22:42 | 2016-09-20 16:22:42 | 2016-03-20 16:22:42 | 2021-06-20 16:22:42
(1 row)29要求显示所有雇员在被雇佣三个月之后的日期
Oracle
SQL> select empno,ename,job,sal,hiredate,
2 ADD_MONTHS(hiredate,3) from emp;
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
HIREDATE ADD_MONTHS(HIREDATE
------------------- -------------------
7369 SMITH CLERK 800
1980-12-17 00:00:00 1981-03-17 00:00:00
7499 ALLEN SALESMAN 1600
1981-02-20 00:00:00 1981-05-20 00:00:00
7521 WARD SALESMAN 1250
1981-02-22 00:00:00 1981-05-22 00:00:00
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
HIREDATE ADD_MONTHS(HIREDATE
------------------- -------------------
7566 JONES MANAGER 2975
1981-04-02 00:00:00 1981-07-02 00:00:00
7654 MARTIN SALESMAN 1250
1981-09-28 00:00:00 1981-12-28 00:00:00
7698 BLAKE MANAGER 2850
1981-05-01 00:00:00 1981-08-01 00:00:00
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
HIREDATE ADD_MONTHS(HIREDATE
------------------- -------------------
7782 CLARK MANAGER 2450
1981-06-09 00:00:00 1981-09-09 00:00:00
7788 SCOTT ANALYST 3000
1987-04-19 00:00:00 1987-07-19 00:00:00
7839 KING PRESIDENT 5000
1981-11-17 00:00:00 1982-02-17 00:00:00
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
HIREDATE ADD_MONTHS(HIREDATE
------------------- -------------------
7844 TURNER SALESMAN 1500
1981-09-08 00:00:00 1981-12-08 00:00:00
7876 ADAMS CLERK 1100
1987-05-23 00:00:00 1987-08-23 00:00:00
7900 JAMES CLERK 950
1981-12-03 00:00:00 1982-03-03 00:00:00
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
HIREDATE ADD_MONTHS(HIREDATE
------------------- -------------------
7902 FORD ANALYST 3000
1981-12-03 00:00:00 1982-03-03 00:00:00
7934 MILLER CLERK 1300
1982-01-23 00:00:00 1982-04-23 00:00:00
14 rows selected.PPAS
scott=# select empno,ename,job,sal,hiredate,
scott-# ADD_MONTHS(hiredate,3) from emp;
empno | ename | job | sal | hiredate | add_months
-------+--------+-----------+---------+---------------------+---------------------
7369 | SMITH | CLERK | 800.00 | 1980-12-17 00:00:00 | 1981-03-17 00:00:00
7499 | ALLEN | SALESMAN | 1600.00 | 1981-02-20 00:00:00 | 1981-05-20 00:00:00
7521 | WARD | SALESMAN | 1250.00 | 1981-02-22 00:00:00 | 1981-05-22 00:00:00
7566 | JONES | MANAGER | 2975.00 | 1981-04-02 00:00:00 | 1981-07-02 00:00:00
7654 | MARTIN | SALESMAN | 1250.00 | 1981-09-28 00:00:00 | 1981-12-28 00:00:00
7698 | BLAKE | MANAGER | 2850.00 | 1981-05-01 00:00:00 | 1981-08-01 00:00:00
7782 | CLARK | MANAGER | 2450.00 | 1981-06-09 00:00:00 | 1981-09-09 00:00:00
7788 | SCOTT | ANALYST | 3000.00 | 1987-04-19 00:00:00 | 1987-07-19 00:00:00
7839 | KING | PRESIDENT | 5000.00 | 1981-11-17 00:00:00 | 1982-02-17 00:00:00
7844 | TURNER | SALESMAN | 1500.00 | 1981-09-08 00:00:00 | 1981-12-08 00:00:00
7876 | ADAMS | CLERK | 1100.00 | 1987-05-23 00:00:00 | 1987-08-23 00:00:00
7900 | JAMES | CLERK | 950.00 | 1981-12-03 00:00:00 | 1982-03-03 00:00:00
7902 | FORD | ANALYST | 3000.00 | 1981-12-03 00:00:00 | 1982-03-03 00:00:00
7934 | MILLER | CLERK | 1300.00 | 1982-01-23 00:00:00 | 1982-04-23 00:00:00
(14 rows)30验证NEXT_DAY()函数
Oracle
SQL> select SYSDATE,NEXT_DAY(SYSDATE,'Sunday') 下一个星期日,
2 NEXT_DAY(SYSDATE,'Monday') 下一个星期一
3 from dual;
SYSDATE 下一个星期日 下一个星期一
------------------- ------------------- -------------------
2016-06-21 00:40:37 2016-06-26 00:40:37 2016-06-27 00:40:37PPAS
scott=# select SYSDATE,NEXT_DAY(SYSDATE,'Sunday') 下一个星期日,
scott-# NEXT_DAY(SYSDATE,'Monday') 下一个星期一
scott-# from dual;
sysdate | 下一个星期日 | 下一个星期一
--------------------+--------------------+--------------------
20-JUN-16 16:39:23 | 26-JUN-16 16:39:23 | 27-JUN-16 16:39:23
(1 row)31验证LAST_DAY()函数
Oracle
SQL> select SYSDATE,LAST_DAY(SYSDATE) from dual;
SYSDATE LAST_DAY(SYSDATE)
------------------- -------------------
2016-06-21 00:45:38 2016-06-30 00:45:38PPAS
scott=# select SYSDATE,LAST_DAY(SYSDATE) from dual;
sysdate | last_day
--------------------+--------------------
20-JUN-16 16:44:03 | 30-JUN-16 16:44:03
(1 row)32查询所有是在其雇佣所在月的倒数第三天被公司雇佣的完整雇员信息
Oracle
SQL> select empno,ename,job,hiredate,LAST_DAY(hiredate)
2 from emp
3 where LAST_DAY(hiredate)-2 = hiredate;
EMPNO ENAME JOB HIREDATE
---------- -------------------- ------------------ -------------------
LAST_DAY(HIREDATE)
-------------------
7654 MARTIN SALESMAN 1981-09-28 00:00:00
1981-09-30 00:00:00PPAS
scott=# select empno,ename,job,hiredate,LAST_DAY(hiredate)
scott-# from emp
scott-# where LAST_DAY(hiredate)-2 = hiredate;
empno | ename | job | hiredate | last_day
-------+--------+----------+--------------------+--------------------
7654 | MARTIN | SALESMAN | 28-SEP-81 00:00:00 | 30-SEP-81 00:00:00
(1 row)33查询出每个雇员的编号，姓名，雇佣日期，雇佣的月数，及年份
Oracle
SQL> select empno 雇员编号,
2 ename 雇员姓名,
3 hiredate 雇佣日期,
4 TRUNC(MONTHS_BETWEEN(sysdate,hiredate)) 雇佣总月数,
5 TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) 雇佣总年份
6 from emp;
雇员编号 雇员姓名 雇佣日期 雇佣总月数 雇佣总年份
---------- -------------------- ------------------- ---------- ----------
7369 SMITH 1980-12-17 00:00:00 426 35
7499 ALLEN 1981-02-20 00:00:00 424 35
7521 WARD 1981-02-22 00:00:00 423 35
7566 JONES 1981-04-02 00:00:00 422 35
7654 MARTIN 1981-09-28 00:00:00 416 34
7698 BLAKE 1981-05-01 00:00:00 421 35
7782 CLARK 1981-06-09 00:00:00 420 35
7788 SCOTT 1987-04-19 00:00:00 350 29
7839 KING 1981-11-17 00:00:00 415 34
7844 TURNER 1981-09-08 00:00:00 417 34
7876 ADAMS 1987-05-23 00:00:00 348 29
雇员编号 雇员姓名 雇佣日期 雇佣总月数 雇佣总年份
---------- -------------------- ------------------- ---------- ----------
7900 JAMES 1981-12-03 00:00:00 414 34
7902 FORD 1981-12-03 00:00:00 414 34
7934 MILLER 1982-01-23 00:00:00 412 34
14 rows selected.PPAS
scott=# select empno 雇员编号,
scott-# ename 雇员姓名,
scott-# hiredate 雇佣日期,
scott-# TRUNC(MONTHS_BETWEEN(sysdate,hiredate)) 雇佣总月数,
scott-# TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) 雇佣总年份
scott-# from emp;
雇员编号 | 雇员姓名 | 雇佣日期 | 雇佣总月数 | 雇佣总年份
----------+----------+--------------------+------------+------------
7369 | SMITH | 17-DEC-80 00:00:00 | 426 | 35
7499 | ALLEN | 20-FEB-81 00:00:00 | 424 | 35
7521 | WARD | 22-FEB-81 00:00:00 | 423 | 35
7566 | JONES | 02-APR-81 00:00:00 | 422 | 35
7654 | MARTIN | 28-SEP-81 00:00:00 | 416 | 34
7698 | BLAKE | 01-MAY-81 00:00:00 | 421 | 35
7782 | CLARK | 09-JUN-81 00:00:00 | 420 | 35
7788 | SCOTT | 19-APR-87 00:00:00 | 350 | 29
7839 | KING | 17-NOV-81 00:00:00 | 415 | 34
7844 | TURNER | 08-SEP-81 00:00:00 | 417 | 34
7876 | ADAMS | 23-MAY-87 00:00:00 | 348 | 29
7900 | JAMES | 03-DEC-81 00:00:00 | 414 | 34
7902 | FORD | 03-DEC-81 00:00:00 | 414 | 34
7934 | MILLER | 23-JAN-82 00:00:00 | 412 | 34
(14 rows)34查询出每个雇员的编号，姓名，雇佣日期，已雇佣的年数、月数、天数
Oracle
SQL> select empno 雇员编号,
2 ename 雇员姓名,
3 hiredate 雇佣日期,
4 TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年数,
5 TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 已雇佣月数,
6 TRUNC(sysdate-ADD_MONTHS(hiredate,MONTHS_BETWEEN(sysdate,hiredate))) 已雇佣天数
7 from emp;
雇员编号 雇员姓名 雇佣日期 已雇佣年数 已雇佣月数 已雇佣天数
---------- -------------------- ------------ ---------- ---------- ----------
7369 SMITH 17-DEC-80 35 6 4
7499 ALLEN 20-FEB-81 35 4 1
7521 WARD 22-FEB-81 35 3 30
7566 JONES 02-APR-81 35 2 19
7654 MARTIN 28-SEP-81 34 8 24
7698 BLAKE 01-MAY-81 35 1 20
7782 CLARK 09-JUN-81 35 0 12
7788 SCOTT 19-APR-87 29 2 2
7839 KING 17-NOV-81 34 7 4
7844 TURNER 08-SEP-81 34 9 13
7876 ADAMS 23-MAY-87 29 0 29
雇员编号 雇员姓名 雇佣日期 已雇佣年数 已雇佣月数 已雇佣天数
---------- -------------------- ------------ ---------- ---------- ----------
7900 JAMES 03-DEC-81 34 6 18
7902 FORD 03-DEC-81 34 6 18
7934 MILLER 23-JAN-82 34 4 29
14 rows selected.PPAS
Oracle迁移PPAS(PostgreSQL)改造点
scott=# select empno 雇员编号,
scott-# ename 雇员姓名,
scott-# hiredate 雇佣日期,
scott-# trunc(months_between(sysdate,hiredate)/12) 雇佣年数,
scott-# trunc(months_between(sysdate,hiredate)) 雇佣月数,
scott-# EXTRACT(DAY FROM SYSDATE-hiredate) 雇佣天数
scott-# from emp;
雇员编号 | 雇员姓名 | 雇佣日期 | 雇佣年数 | 雇佣月数 | 雇佣天数
----------+----------+--------------------+----------+----------+----------
7369 | SMITH | 17-DEC-80 00:00:00 | 35 | 426 | 12970
7499 | ALLEN | 20-FEB-81 00:00:00 | 35 | 424 | 12905
7521 | WARD | 22-FEB-81 00:00:00 | 35 | 423 | 12903
7566 | JONES | 02-APR-81 00:00:00 | 35 | 422 | 12864
7654 | MARTIN | 28-SEP-81 00:00:00 | 34 | 416 | 12685
7698 | BLAKE | 01-MAY-81 00:00:00 | 35 | 421 | 12835
7782 | CLARK | 09-JUN-81 00:00:00 | 35 | 420 | 12796
7788 | SCOTT | 19-APR-87 00:00:00 | 29 | 350 | 10656
7839 | KING | 17-NOV-81 00:00:00 | 34 | 415 | 12635
7844 | TURNER | 08-SEP-81 00:00:00 | 34 | 417 | 12705
7876 | ADAMS | 23-MAY-87 00:00:00 | 29 | 348 | 10622
7900 | JAMES | 03-DEC-81 00:00:00 | 34 | 414 | 12619
7902 | FORD | 03-DEC-81 00:00:00 | 34 | 414 | 12619
7934 | MILLER | 23-JAN-82 00:00:00 | 34 | 412 | 12568
(14 rows)35从日期时间中取出年、月、日数据
Oracle
SQL> select EXTRACT(YEAR FROM DATE '2016-06-21')years,
2 EXTRACT(MONTH FROM DATE '2016-06-21')months,
3 EXTRACT(DAY FROM DATE '2016-06-21')days
4 FROM dual;
YEARS MONTHS DAYS
---------- ---------- ----------
2016 6 21PPAS
scott=# select EXTRACT(YEAR FROM DATE '2016-06-21')years,
scott-# EXTRACT(MONTH FROM DATE '2016-06-21')months,
scott-# EXTRACT(DAY FROM DATE '2016-06-21')days
scott-# FROM dual;
years | months | days
-------+--------+------
2016 | 6 | 21
(1 row)36从时间戳中取出年、月、日、时、分、秒
Oracle
SQL> select EXTRACT(YEAR FROM SYSTIMESTAMP)years,
2 EXTRACT(MONTH FROM SYSTIMESTAMP)months,
3 EXTRACT(DAY FROM SYSTIMESTAMP)days,
4 EXTRACT(HOUR FROM SYSTIMESTAMP)hours,
5 EXTRACT(MINUTE FROM SYSTIMESTAMP)minutes,
6 EXTRACT(SECOND FROM SYSTIMESTAMP)seconds
7 from dual;
YEARS MONTHS DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ---------- ---------- ----------
2016 6 21 9 25 42.665018PPAS
scott=# select EXTRACT(YEAR FROM SYSTIMESTAMP)years,
scott-# EXTRACT(MONTH FROM SYSTIMESTAMP)months,
scott-# EXTRACT(DAY FROM SYSTIMESTAMP)days,
scott-# EXTRACT(HOUR FROM SYSTIMESTAMP)hours,
scott-# EXTRACT(MINUTE FROM SYSTIMESTAMP)minutes,
scott-# EXTRACT(SECOND FROM SYSTIMESTAMP)seconds
scott-# from dual;
years | months | days | hours | minutes | seconds
-------+--------+------+-------+---------+-----------
2016 | 6 | 21 | 9 | 23 | 21.422939
(1 row)37取得时间间隔
Oracle
SQL> SELECT EXTRACT(DAY FROM
2 TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
3 - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days ,
4 EXTRACT(HOUR FROM datetime_one - datetime_two) hours ,
5 EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes ,
6 EXTRACT(SECOND FROM datetime_one - datetime_two) seconds
7 FROM (
8 SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one ,
9 TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
10 FROM dual) ;
DAYS ``HOURS MINUTES SECONDS 320 3 9 24PPASscott=# SELECT EXTRACT(DAY FROM
scott(# TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
scott(# - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days ,
scott-# EXTRACT(HOUR FROM datetime_one - datetime_two) hours ,
scott-# EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes ,
scott-# EXTRACT(SECOND FROM datetime_one - datetime_two) seconds
scott-# FROM (
scott(# SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one ,
scott(# TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
scott(# FROM dual) ;
| days | hours | minutes | seconds |
|---|---|---|---|
| 320 | 3 | 9 | 24 |
(1 row)
38复杂计算时间间隔（天数）
OracleSQL> SELECT
2 EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
3 - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days
4 FROM dual ;
DAYS 320PPASscott=# SELECT
scott-# EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
scott(# - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days
scott-# FROM dual ;
days
320
(1 row)
39格式化当前的日期时间
OracleSQL> select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,'YYYY-MM-DD') 格式化日期,
2 TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') 格式化日期时间,
3 TO_CHAR(SYSDATE,'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的日期时间
4 from dual;
当前系统时间 格式化日期 格式化日期时间
------------ -------------------- --------------------------------------
去掉前导0的日期时间
21-JUN-16 2016-06-21 2016-06-21 17:52:44
2016-6-21 17:52:44
PPASscott=# select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,'YYYY-MM-DD') 格式化日期,
scott-# TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') 格式化日期时间,
scott-# TO_CHAR(SYSDATE,'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的日期时间
scott-# from dual;
当前系统时间| 格式化日期 | 格式化日期时间 | 去掉前导0的日期时间 | 21-JUN-16 09:50:48 | 2016-06-21 | 2016-06-21 09:50:48 | 2016-6-21 9:50:48 |
(1 row)
40使用其它的方法格式化年、月、日
OracleSQL> select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,'YEAR-MONTH-DY') 格式化日期
2 from dual;
当前系统时间
格式化日期
21-JUN-16
TWENTY SIXTEEN-JUNE -TUE
PPASscott=# select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,'YEAR-MONTH-DY') 格式化日期
scott-# from dual;
当前系统时间 | 格式化日期 | 21-JUN-16 10:05:55 | TWENTY SIXTEEN-JUNE -TUE |
(1 row)