常用日期函数介绍

 

36、add_months

add_months(日期值,增加(减少)值)

增加或减去月份；

SQL>select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHAR(ADD_MONTHS(TO_DATE('19

------------------------------

200002

 

SQL>select hiredate,add_months(hiredate,2) from emp where ename='SMITH';

HIREDATE    ADD_MONTHS(HIREDATE,2)

----------- ----------------------

1980/12/17  1981/2/17

 

请查找最近350个月入职的员工

SQL>select ename,hiredate from emp where add_months(hiredate,350)>=sysdate;

ENAME      HIREDATE

---------- -----------

SCOTT      1987/4/19

ADAMS      1987/5/23

 

37、last_day

返回日期的最后一天；

SQL>select to_char(sysdate,'yyyy-mm-dd'),to_char((sysdate)+1,'yyyy-mm-dd') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DD') TO_CHAR((SYSDATE)+1,'YYYY-MM-D

----------------------------- ------------------------------

2014-04-24                    2014-04-25

 

SQL>select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;

TO_CHAR(LAST_DAY(SYSDATE),'YYY

------------------------------

2014-04-30

 

38、months_between(date2,date1)

给出date2-date1的月份，共有多少个月；

SQL>select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

MON_BETWEEN

-----------

          9

 

SQL>select months_between(to_date('2000-05-20','yyyy-mm-dd'),to_date('2005-05-20','yyyy-mm-dd')) mon_betw from dual;

  MON_BETW

----------

       -60

 

39、new_time(date,'this','that')

给出在this时区=other时区的日期和时间；

SQL>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') bj_date,to_char(new_time

(sysdate,'PDT','GMT'),'yyyy-mm-dd hh24:mi:ss') los_date from dual;

BJ_DATE             LOS_DATE

------------------- -------------------

2014-04-24 15:27:38 2014-04-24 22:27:38

 

40、next_day(date,'day')

给出日期date和星期X之后计算下一个星期的日期；

SQL>select next_day('18-5月-2001','星期五') next_day from dual;

NEXT_DAY

-----------

2001/5/25

 

41、sysdate

用来得到系统的当前日期；

SQL>select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

----------------------

星期四