【实验】【总结】基础日期函数
日期类常用操作函数实验(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    &nb