oracle 常用函数

1。上月末天：
SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from
dual;

LASTDAY
----------
2005-05-31

2。上月今天
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;


PRETODAY
----------
2005-05-21

3.上月首天
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;

FIRSTDAY
----------
2005-05-01

4.按照每周进行统计
SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');

TO
--
25

5。按照每月进行统计
SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');

TO
--
06

6。按照每季度进行统计
SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');

T
-
2

7。按照每年进行统计
SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');

TO_C
----
2005

8.要找到某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期

and trim(to_char(t.d, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30 

如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。
SQL> set serverout on
SQL> declare
  2 DateValue date;
  3 begin
  4 select sysdate into DateValue from dual;
  5 dbms_output.put_line('源时间:'||to_char(DateValue));
  6 dbms_output.put_line('源时间减1天:'||to_char(DateValue-1));
  7 dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24));
  8 dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60)));
  9 dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
10 end;
11 /
源时间:2003-12-29 11:53:41
源时间减1天:2003-12-28 11:53:41
源时间减1天1小时:2003-12-28 10:53:41
源时间减1天1小时1分:2003-12-28 10:52:41
源时间减1天1小时1分1秒:2003-12-28 10:52:40

PL/SQL 过程已成功完成。