Oracle常用日期函数

一、常用日期函数

1、Sysdate 当前日期和时间
SQL> Select sysdate from dual;

2、Last_day 本月最后一天
SQL> Select last_day(sysdate) from dual;

3、Add_months(d,n) 当前日期d后推n个月 用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)
SQL> Select add_months(sysdate,2) from dual;

4、Months_between(f,s) 日期f和s间相差月数
SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;

5、NEXT_DAY(d, day_of_week)
返回由"day_of_week"命名的，在变量"d"指定的日期之后的第某个工作日的日期。
参数"day_of_week"必须为该星期中的某一天。
SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;

6、current_date()返回当前会话时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;

SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00          13-11月-03
 
SQL> alter session set time_zone='-11:00' 2  /
会话已更改。
 
SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00          12-11月-03 04.59.13.668000 下午 -11:00

7、current_timestamp()以timestamp with time zone数据类型返回当前会话时区中的当前日期
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------
21-6月 -05 10.13.08.220589 上午 +08:00

8、dbtimezone()返回时区
SQL> select dbtimezone from dual;

9、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value |interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;

This Month
----------
        6

SQL> select extract(year from add_months(sysdate,36)) " Years" from dual;

    Years
----------
      2008

10、localtimestamp()返回会话中的日期和时间
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
21-6月 -05 10.18.15.855652 上午

二、常用日期数据格式

1、Y或YY或YYY 年的最后一位、两位或三位，008表示2008年
SQL> select to_char(sysdate,'YYY')from dual;

2、SYEAR或YEAR SYEAR使公元前的年份前加一负号，-1112表示公元前1112年
SQL> select to_char(sysdate,'SYEAR') from dual;

3、Q 季度，1～3月为第一季度
SQL> select to_char(sysdate,'Q') from dual;

4、MM 月份数
SQL> select to_char(sysdate,'MM') from dual;

5、RM 月份的罗马表示，IV表示4月
SQL> select to_char(sysdate,'RM') from dual;

6、Month 用9个字符长度表示的月份名，April后跟4个空格表示4月
SQL> select to_char(sysdate,'Month') from dual;

8、W 本月第几周，4表示2007年4月27日为第4周
SQL> select to_char(sysdate,'W') from dual;

9、DDD 当年第几天, 1月1日为001，2007年4月27日为第117天
SQL> select to_char(sysdate,'DDD') from dual;

10、DD 当月第几天，4月27日为第27天
SQL> select to_char(sysdate,'DD') from dual;

11、D 周内第几天，2007年4月27日为星期五
SQL> select to_char(sysdate,'D') from dual;

12、DY 周内第几天缩写，2007年4月27日为星期五
SQL> select to_char(sysdate,'DY') from dual;

13、HH或HH12 12进制小时数
SQL> select to_char(sysdate,'HH') from dual;

14、HH24 24小时制
SQL> select to_char(sysdate,'HH24') from dual;

15、MI 分钟数(0～59) ，35为下午5点35分
SQL> select to_char(sysdate,'MI') from dual;

16、SS 秒数(0～59)，40为17点35分40秒
SQL>  Select to_char(sysdate,'SS') from dual;

三、一些实践后的用法：

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

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

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

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

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

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

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

8、要找到某月中所有周五的具体日期
SQL> select to_char(t.d,'YY-MM-DD') from (select trunc(sysdate, 'MM')+rownum-1 as d from all_objects where rownum < 32) t
  2  where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
  3  and trim(to_char(t.d, 'Day')) = '星期五' ;

9、oracle中时间运算
内容如下：
1、oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可
4、进行时间进制转换时注意加括号，否则会出问题

SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';

Session altered

SQL>  set serverout on
SQL>  declare DateValue date;
  2  begin
  3  select sysdate into DateValue from dual;
  4  dbms_output.put_line('源时间:' | |to_char(DateValue));
  5  dbms_output.put_line('源时间减1天:' | |to_char(DateValue-1));
  6  dbms_output.put_line('源时间减1天1小时:' | |to_char(DateValue-1-1/24));
  7  dbms_output.put_line('源时间减1天1小时1分:' | |to_char(DateValue-1-1/24-1/(24*60)));
  8  dbms_output.put_line('源时间减1天1小时1分1秒:' | |to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
  9  end;
10  /
源时间:2007-04-27 06:04:45
源时间减1天:2007-04-26 06:04:45
源时间减1天1小时:2007-04-26 05:04:45
源时间减1天1小时1分:2007-04-26 05:03:45
源时间减1天1小时1分1秒:2007-04-26 05:03:44

PL/SQL procedure successfully completed

10、在Oracle中实现时间相加处理
-- 名称：Add_Times
-- 功能：返回d1与NewTime相加以后的结果，实现时间的相加
-- 说明：对于NewTime中的日期不予考虑

create or replace function Add_Times(d1 in date,NewTime in date) return date
is
  hh  number;
  mm  number;
  ss  number;
  hours number;
  dResult  date; 
begin
  -- 下面依次取出时、分、秒
  select to_number(to_char(NewTime,'HH24')) into hh from dual;
  select to_number(to_char(NewTime,'MI')) into mm from dual;
  select to_number(to_char(NewTime,'SS')) into ss from dual;
  -- 换算出NewTime中小时总和，在一天的百分几
  hours := (hh + (mm / 60) + (ss / 3600))/ 24;
  -- 得出时间相加后的结果
  select d1 + hours into dResult from dual;
  return(dResult);
end Add_Times;