【實驗】【總結】基礎日期函式
日期類常用操作函式實驗(sysdate,round,trunc,months_between,add_months,next_day,last_day)
1.sysdate函式及日期顯示格式
1).顯示當前時間
sys@ora10g> select sysdate from dual;
SYSDATE
---------
07-MAR-09
2).檢視當前session中日期的顯示格式
sys@ora10g> select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
3).修改session級別的日期顯示格式
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sys@ora10g> select sysdate from dual;
SYSDATE
-------------------
2009-03-06 10:17:07
2.round和trunc取整函式在日期中的應用
sec@ora10g> select sysdate, hiredate, (sysdate - hiredate) , round(sysdate - hiredate) days from emp;
SYSDATE HIREDATE (SYSDATE-HIREDATE) DAYS
------------------- ------------------- ------------------ ----------
2009-03-06 10:21:29 1980-12-17 00:00:00 10306.4316 10306
2009-03-06 10:21:29 1981-02-20 00:00:00 10241.4316 10241
2009-03-06 10:21:29 1981-02-22 00:00:00 10239.4316 10239
2009-03-06 10:21:29 1981-04-02 00:00:00 10200.4316 10200
2009-03-06 10:21:29 1981-09-28 00:00:00 10021.4316 10021
2009-03-06 10:21:29 1981-05-01 00:00:00 10171.4316 10171
2009-03-06 10:21:29 1981-06-09 00:00:00 10132.4316 10132
2009-03-06 10:21:29 1987-04-19 00:00:00 7992.43159 7992
2009-03-06 10:21:29 1981-11-17 00:00:00 9971.43159 9971
2009-03-06 10:21:29 1981-09-08 00:00:00 10041.4316 10041
2009-03-06 10:21:29 1987-05-23 00:00:00 7958.43159 7958
2009-03-06 10:21:29 1981-12-03 00:00:00 9955.43159 9955
2009-03-06 10:21:29 1981-12-03 00:00:00 9955.43159 9955
2009-03-06 10:21:29 1982-01-23 00:00:00 9904.43159 9904
sec@ora10g> select sysdate, round(sysdate,'mm') round_mm, round(sysdate,'month') round_month from dual;
SYSDATE ROUND_MM ROUND_MONTH
------------------- ------------------- -------------------
2009-03-06 11:21:14 2009-03-01 00:00:00 2009-03-01 00:00:00
sec@ora10g> select sysdate, round(sysdate,'yyyy') round_yyyy, round(sysdate,'year') round_year from dual;
SYSDATE ROUND_YYYY ROUND_YEAR
------------------- ------------------- -------------------
2009-03-06 11:21:21 2009-01-01 00:00:00 2009-01-01 00:00:00
sec@ora10g> select sysdate, trunc(sysdate,'mm') trunc_mm, trunc(sysdate,'month') trunc_month from dual;
SYSDATE TRUNC_MM TRUNC_MONTH
------------------- ------------------- -------------------
2009-03-06 11:21:25 2009-03-01 00:00:00 2009-03-01 00:00:00
sec@ora10g> select sysdate, trunc(sysdate,'yyyy') trunc_yyyy, trunc(sysdate,'year') trunc_year from dual;
SYSDATE TRUNC_YYYY TRUNC_YEAR
------------------- ------------------- -------------------
2009-03-06 11:21:30 2009-01-01 00:00:00 2009-01-01 00:00:00
14 rows selected.
3.months_between函式，兩個日期之間的月份差
sec@ora10g> select sysdate, hiredate, months_between(sysdate,hiredate) months_between, round(months_between(sysdate,hiredate)) round from emp;
SYSDATE HIREDATE MONTHS_BETWEEN ROUND
------------------- ------------------- -------------- ----------
2009-03-06 10:26:45 1980-12-17 00:00:00 338.659201 339
2009-03-06 10:26:45 1981-02-20 00:00:00 336.562427 337
2009-03-06 10:26:45 1981-02-22 00:00:00 336.497911 336
2009-03-06 10:26:45 1981-04-02 00:00:00 335.143072 335
2009-03-06 10:26:45 1981-09-28 00:00:00 329.304363 329
2009-03-06 10:26:45 1981-05-01 00:00:00 334.17533 334
2009-03-06 10:26:45 1981-06-09 00:00:00 332.917266 333
2009-03-06 10:26:45 1987-04-19 00:00:00 262.594685 263
2009-03-06 10:26:45 1981-11-17 00:00:00 327.659201 328
2009-03-06 10:26:45 1981-09-08 00:00:00 329.949524 330
2009-03-06 10:26:45 1987-05-23 00:00:00 261.465653 261
2009-03-06 10:26:45 1981-12-03 00:00:00 327.110814 327
2009-03-06 10:26:45 1981-12-03 00:00:00 327.110814 327
2009-03-06 10:26:45 1982-01-23 00:00:00 325.465653 325
14 rows selected.
4.add_months函式，增加月份
sec@ora10g> select sysdate, add_months(sysdate,9) add_months from dual;
SYSDATE ADD_MONTHS
------------------- -------------------
2009-03-06 10:33:19 2009-12-06 10:33:19
5.next_day函式，計算下一個指定天是什麼
sec@ora10g> select sysdate, next_day(sysdate,'friday') next_day from dual;
SYSDATE NEXT_DAY
------------------- -------------------
2009-03-06 10:32:30 2009-03-13 10:32:30
6.last_day函式，計算月底的日期
sec@ora10g> select sysdate, last_day(sysdate) from dual;
SYSDATE LAST_DAY(SYSDATE)
------------------- -------------------
2009-03-06 10:35:37 2009-03-31 10:35:37
-- The End --
1.sysdate函式及日期顯示格式
1).顯示當前時間
sys@ora10g> select sysdate from dual;
SYSDATE
---------
07-MAR-09
2).檢視當前session中日期的顯示格式
sys@ora10g> select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
3).修改session級別的日期顯示格式
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sys@ora10g> select sysdate from dual;
SYSDATE
-------------------
2009-03-06 10:17:07
2.round和trunc取整函式在日期中的應用
sec@ora10g> select sysdate, hiredate, (sysdate - hiredate) , round(sysdate - hiredate) days from emp;
SYSDATE HIREDATE (SYSDATE-HIREDATE) DAYS
------------------- ------------------- ------------------ ----------
2009-03-06 10:21:29 1980-12-17 00:00:00 10306.4316 10306
2009-03-06 10:21:29 1981-02-20 00:00:00 10241.4316 10241
2009-03-06 10:21:29 1981-02-22 00:00:00 10239.4316 10239
2009-03-06 10:21:29 1981-04-02 00:00:00 10200.4316 10200
2009-03-06 10:21:29 1981-09-28 00:00:00 10021.4316 10021
2009-03-06 10:21:29 1981-05-01 00:00:00 10171.4316 10171
2009-03-06 10:21:29 1981-06-09 00:00:00 10132.4316 10132
2009-03-06 10:21:29 1987-04-19 00:00:00 7992.43159 7992
2009-03-06 10:21:29 1981-11-17 00:00:00 9971.43159 9971
2009-03-06 10:21:29 1981-09-08 00:00:00 10041.4316 10041
2009-03-06 10:21:29 1987-05-23 00:00:00 7958.43159 7958
2009-03-06 10:21:29 1981-12-03 00:00:00 9955.43159 9955
2009-03-06 10:21:29 1981-12-03 00:00:00 9955.43159 9955
2009-03-06 10:21:29 1982-01-23 00:00:00 9904.43159 9904
sec@ora10g> select sysdate, round(sysdate,'mm') round_mm, round(sysdate,'month') round_month from dual;
SYSDATE ROUND_MM ROUND_MONTH
------------------- ------------------- -------------------
2009-03-06 11:21:14 2009-03-01 00:00:00 2009-03-01 00:00:00
sec@ora10g> select sysdate, round(sysdate,'yyyy') round_yyyy, round(sysdate,'year') round_year from dual;
SYSDATE ROUND_YYYY ROUND_YEAR
------------------- ------------------- -------------------
2009-03-06 11:21:21 2009-01-01 00:00:00 2009-01-01 00:00:00
sec@ora10g> select sysdate, trunc(sysdate,'mm') trunc_mm, trunc(sysdate,'month') trunc_month from dual;
SYSDATE TRUNC_MM TRUNC_MONTH
------------------- ------------------- -------------------
2009-03-06 11:21:25 2009-03-01 00:00:00 2009-03-01 00:00:00
sec@ora10g> select sysdate, trunc(sysdate,'yyyy') trunc_yyyy, trunc(sysdate,'year') trunc_year from dual;
SYSDATE TRUNC_YYYY TRUNC_YEAR
------------------- ------------------- -------------------
2009-03-06 11:21:30 2009-01-01 00:00:00 2009-01-01 00:00:00
14 rows selected.
3.months_between函式，兩個日期之間的月份差
sec@ora10g> select sysdate, hiredate, months_between(sysdate,hiredate) months_between, round(months_between(sysdate,hiredate)) round from emp;
SYSDATE HIREDATE MONTHS_BETWEEN ROUND
------------------- ------------------- -------------- ----------
2009-03-06 10:26:45 1980-12-17 00:00:00 338.659201 339
2009-03-06 10:26:45 1981-02-20 00:00:00 336.562427 337
2009-03-06 10:26:45 1981-02-22 00:00:00 336.497911 336
2009-03-06 10:26:45 1981-04-02 00:00:00 335.143072 335
2009-03-06 10:26:45 1981-09-28 00:00:00 329.304363 329
2009-03-06 10:26:45 1981-05-01 00:00:00 334.17533 334
2009-03-06 10:26:45 1981-06-09 00:00:00 332.917266 333
2009-03-06 10:26:45 1987-04-19 00:00:00 262.594685 263
2009-03-06 10:26:45 1981-11-17 00:00:00 327.659201 328
2009-03-06 10:26:45 1981-09-08 00:00:00 329.949524 330
2009-03-06 10:26:45 1987-05-23 00:00:00 261.465653 261
2009-03-06 10:26:45 1981-12-03 00:00:00 327.110814 327
2009-03-06 10:26:45 1981-12-03 00:00:00 327.110814 327
2009-03-06 10:26:45 1982-01-23 00:00:00 325.465653 325
14 rows selected.
4.add_months函式，增加月份
sec@ora10g> select sysdate, add_months(sysdate,9) add_months from dual;
SYSDATE ADD_MONTHS
------------------- -------------------
2009-03-06 10:33:19 2009-12-06 10:33:19
5.next_day函式，計算下一個指定天是什麼
sec@ora10g> select sysdate, next_day(sysdate,'friday') next_day from dual;
SYSDATE NEXT_DAY
------------------- -------------------
2009-03-06 10:32:30 2009-03-13 10:32:30
6.last_day函式，計算月底的日期
sec@ora10g> select sysdate, last_day(sysdate) from dual;
SYSDATE LAST_DAY(SYSDATE)
------------------- -------------------
2009-03-06 10:35:37 2009-03-31 10:35:37
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-563006/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql日期函式總結MySql函式
- mysql日期函式小結及個人實驗MySql函式
- Go基礎-時間和日期函式Go函式
- 【機器學習基礎】常見損失函式總結機器學習函式
- 前端學習之PHP基礎函式總結前端PHP函式
- JS函式驗證總結JS函式
- Kotlin——初級篇（七）：函式基礎總結Kotlin函式
- php 驗證格式的函式總結PHP函式
- ORACLE 實用函式總結Oracle函式
- JS基礎難點總結（函式作用域，變數提升，物件，建構函式，this）全！！！JS函式變數物件
- 函式基礎函式
- MySQL日期和時間函式彙總MySql函式
- 日期函式函式
- 函式基礎和函式引數函式
- 超實用PHP函式總結整理PHP函式
- 正規表示式基礎知識總結
- SQL函式之日期函式SQL函式
- php函式總結PHP函式
- Oracle 函式總結Oracle函式
- 基礎實驗
- 【實驗】【總結】Oracle日期類操作（格式 加減乘 取毫秒）Oracle
- ABAP日期函式函式
- Sybase日期函式函式
- sql 日期函式SQL函式
- JavaScript基礎總結JavaScript
- 安全基礎總結
- VUE基礎總結Vue
- MongoDB基礎總結MongoDB
- 機器學習基礎總結機器學習
- 【HTML——基礎總結】HTML
- 【總結】Javascript基礎JavaScript
- Java基礎總結Java
- jQuery基礎總結jQuery
- Python基礎-函式Python函式
- python函式基礎Python函式
- Go基礎-字串函式Go字串函式
- python基礎函式Python函式
- 前端基礎（三）：函式前端函式