ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】【总结】基础日期函数

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

原创 Linux操作系统 作者:secooler 时间:2009-03-06 11:11:35 0 删除 编辑
日期类常用操作函数实验(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 --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-563006/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8039528