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

2020-02-09 15:34:21

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