【转】Oracle日期计算

来源：互联网发布：鸟倦飞而知还的意思编辑：程序博客网时间：2024/05/16 11:02

首先介绍oracle中几个关于日期时间的函数

ADD_MONTHS

SQL> select sysdate,add_months(sysdate,1) from dual;

SYSDATE ADD_MONTHS
---------- ----------
2006-10-23 2006-11-23

1 row selected.

SQL> select sysdate,add_months(sysdate,-1) from dual;

SYSDATE ADD_MONTHS
---------- ----------
2006-10-23 2006-09-23

1 row selected.

LAST_DAY

SQL> select sysdate,last_day(sysdate) from dual;

SYSDATE LAST_DAY(S
---------- ----------
2006-10-23 2006-10-31

1 row selected.

NEXT_DAY

SQL> select sysdate,next_day(sysdate,'TUESDAY') from dual;

SYSDATE NEXT_DAY(S
---------- ----------
2006-10-23 2006-10-24

1 row selected.

TRUNC(date)

SQL> select sysdate,trunc(sysdate,'YEAR') from dual;

SYSDATE TRUNC(SYSD
---------- ----------
2006-10-23 2006-01-01

1 row selected.

ROUND(date)

SQL> select sysdate,round(sysdate,'YEAR') from dual;

SYSDATE ROUND(SYSD
---------- ----------
2006-10-23 2007-01-01

1 row selected.

以上函数，更详细的用法和参数请参考oracle的sql reference文档

通过上面的函数以及组合，可以得到我们想要的其他日期时间。下面给出几个示例：

1>本月的最后一天
SQL> select sysdate,last_day(sysdate) from dual;

SYSDATE LAST_DAY(
--------- ---------
23-OCT-06 31-OCT-06

1 row selected.

2>本季度最后一天
SQL> select sysdate,last_day(add_months(trunc(sysdate,'q'),2)) from dual;

SYSDATE LAST_DAY(
--------- ---------
23-OCT-06 31-DEC-06

1 row selected.

3>本年最后一天
SQL> select sysdate,last_day(add_months(trunc(sysdate,'year'),11)) from dual;

SYSDATE LAST_DAY(
--------- ---------
23-OCT-06 31-DEC-06

1 row selected.

4>去年的最后一天
SQL> select sysdate,last_day(add_months(trunc(sysdate,'year'),-1)) from dual;

SYSDATE LAST_DAY(
--------- ---------
23-OCT-06 31-DEC-05

5>下一个星期三的日期
SQL> select sysdate,next_day(sysdate,'WEDNESDAY') from dual;

SYSDATE NEXT_DAY(
--------- ---------
23-OCT-06 25-OCT-06

1 row selected.

6>下周星期三的日期
SQL> select sysdate,trunc(sysdate,'ww')+10 from dual;

SYSDATE TRUNC(SYS
--------- ---------
23-OCT-06 01-NOV-06

1 row selected.

7>本月的第一个星期一
SQL> select sysdate,next_day(trunc(sysdate,'month'),'MONDAY') from dual;

SYSDATE NEXT_DAY(
--------- ---------
23-OCT-06 02-OCT-06

1 row selected.