SQL内置函数的使用方法及结果(7)

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>selectmonths_between(to_date("2000.05.20","yyyy.mm.dd"),to_date("2005.05.20","yyyy.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_time,to_char(new_time

2 (sysdate,"PDT","GMT"),"yyyy.mm.dd hh24:mi:ss") los_angles from dual;

BJ_TIME LOS_ANGLES

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

2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,"day")

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

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

NEXT_DAY

----------

25-5月 -01

41.SYSDATE 用来得到系统的当前日期

SQL> select to_char(sysdate,"dd-mm-yyyy day") from dual;

TO_CHAR(SYSDATE,"

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

09-05-2004 星期日

trunc(date,fmt)按照给出的要求将日期截断,如果fmt="mi"表示保留分,截断秒

SQL> select to_char(trunc(sysdate,"hh"),"yyyy.mm.dd hh24:mi:ss") hh,

2 to_char(trunc(sysdate,"mi"),"yyyy.mm.dd hh24:mi:ss") hhmm from dual;

HH HHMM

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

2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID 将字符数据类型转换为ROWID类型

SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID ROWIDTOCHAR(ROWID) ENAME

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

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES