【实验】【总结】基础日期函数

日期类常用操作函数实验(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 --