提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  工具

当前栏目

《卸甲笔记》-单行函数对比之二

笔记 函数 对比 之二 单行
2023-09-14 09:03:17 时间

21验证TRUNC()函数
Oracle

SQL select TRUNC(789.652) 截取小数, TRUNC(789.652,2) 截取两位小数,TRUNC(789.652,-2) 取整 from dual;

 截取小数 截取两位小数 取整

---------- ------------ ----------

 789 789.65 700

PPAS

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)

----------

 1

PPAS

scott=# select MOD(10,3) from dual; 

 mod 

-----

(1 row)

日期函数
23取得当前的系统时间
Oracle

SQL select sysdate from dual;

SYSDATE

------------

20-JUN-16

PPAS

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:47

PPAS

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:26

PPAS

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:08

PPAS

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


---------- -------------------- ------------------ ----------

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


---------- -------------------- ------------------ ----------

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


---------- -------------------- ------------------ ----------

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


---------- -------------------- ------------------ ----------

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


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:37



PPAS



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:38



PPAS



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:00



PPAS



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 21



PPAS



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.665018



PPAS



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 24



PPAS



scott=# 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) ;



Oracle

SQL 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
 320
PPAS

scott=# 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 ;

320
(1 row)

39格式化当前的日期时间

Oracle

SQL 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

PPAS

scott=# 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使用其它的方法格式化年、月、日

Oracle

SQL select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,YEAR-MONTH-DY) 格式化日期
2 from dual;

当前系统时间 格式化日期

21-JUN-16
TWENTY SIXTEEN-JUNE -TUE

PPAS

scott=# select SYSDATE 当前系统时间,TO_CHAR(SYSDATE,YEAR-MONTH-DY) 格式化日期
scott-# from dual;

当前系统时间 | 格式化日期
21-JUN-16 10:05:55 TWENTY SIXTEEN-JUNE -TUE

(1 row)


《道德经》云：“有物混成，先天地生。寂兮寥兮，独立而不改，周行而不殆，可以为天下母。”认为构成世界的原初物质是形而上者的“道”。 《黄帝内经》受这些学说的影响，也认为“气”是宇宙万物的本原，“太虚寥廓，肇基化元，万物资始，五运终天”。
第五章 Python函数你知多少 函数作用：把一些复杂的代码封装起来，函数一般都是一个功能，用的时候才调用，提高重复利用率和简化程序结构。

相关文章