数据库函数总结（Oracle篇二）

一、日期函数

SYSDATE
当前日期
SELECT SYSDATE FROM DUAL;

ADD_MONTHS
增加或减去月份（正数表示增加，负数表示减去）
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

LAST_DAY
返回日期的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份（需要注意参数的格式）
SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03', 'YYYY-MM-DD'), TO_DATE('2011-01-23', 'YYYY-MM-DD')) FROM DUAL;
SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;

NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,
       TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;
       
 简写                  时区                 
 AST OR ADT          大西洋标准时间         
 HST OR HDT          阿拉斯加—夏威夷时间
 BST OR BDT          英国夏令时
 MST OR MDT          美国山区时间
 CST OR CDT          美国中央时区
 NST                 新大陆标准时间
 EST OR EDT          美国东部时间
 PST OR PDT          太平洋标准时间
 GMT                 格伦威治标准时间
 YST OR YDT          Yukon标准时间
 
 NEXT_DAY 
 返回与制定日期在同一个星期或之后一个星期内的，你所要求的星期天数的确切日期。
从周日开始算起
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;
 
CURRENT_DATE
 当前会话时区中的当前日期 
SELECT CURRENT_DATE FROM DUAL;

CURRENT_TIMESTAMP
以timestamp with time zone数据类型返回当前会话时区中的当前日期
SELECT CURRENT_TIMESTAMP FROM DUAL;

DBTIMEZONE() 
返回时区
SELECT DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE
返回回话时区  其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变
SELECT SESSIONTIMEZONE FROM DUAL;  

ALTER SESSION SET TIME_ZONE = '8:00';
SELECT SESSIONTIMEZONE FROM DUAL;  

EXTRACT 
找出日期或间隔值的字段值 
SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL;
SELECT EXTRACT(DAY  FROM SYSDATE)  AS "DAY" FROM DUAL;
SELECT EXTRACT(YEAR FROM SYSDATE) AS "YEAR" FROM DUAL;

LOCALTIMESTAMP 
返回会话中的日期和时间 
SELECT LOCALTIMESTAMP FROM DUAL;

TRUNC(for dates)
　　TRUNC函数为指定元素而截去的日期值。
　　其具体的语法格式如下：
　　TRUNC（date[,fmt]）
　　其中：
　　date 一个日期值
　　fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
　　下面是该函数的使用情况：
　　TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')
　　='24-Nov-1999 12:00:00 am'
　　TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'
　  SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回当年第一天.
　  SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回当月第一天.
　　SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回当前星期的第一天.
　　SELECT TRUNC(SYSDATE,'DD') FROM DUAL;--返回当前年月日

上月最后一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL;

上月的今天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL;

上月第一天
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL;

按照每周进行统计
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;

按照每月进行统计
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;

按照每季度进行统计
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

按照每年进行统计
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;


要找到某月中所有周五的具体日期 
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')) = '星期五'


日期和字符转换函数用法（to_date,to_char）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//


求某天是星期几      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;      
   星期一      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
   monday      
   设置日期语言      
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';      
   也可以这样      
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')    


两个日期间的天数      
    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;    


时间为null的用法      
   select id, active_date from table1 
   UNION      
   select 1, TO_DATE(null) from dual; 
   
   注意要用TO_DATE(null) 


月份差   
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd') 

二、类型转换函数

TO_CHAR(DATE,'FORMAT')
把对应的数据转换为字符串类型
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(122323.45, '$99999999.99') FROM DUAL;

TO_DATE(STRING,'FORMAT')
将字符串转化为ORACLE中的一个日期
SELECT  TO_DATE('2011-03-24', 'YYYY/MM/DD') FROM DUAL;   

TO_NUMBER
将给出的字符转换为数字
SELECT TO_NUMBER('2008') AS Year FROM DUAL;


RUNC(for number)
按照指定的精度截取一个数
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
　　其具体的语法格式如下
　　TRUNC（number[,decimals]）
　　其中：
　　number 待做截取处理的数值
　　decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分


SELECT TRUNC(314.14159234, -2) AS FirstNumber, TRUNC(314.14159234,2) FROM DUAL;

将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。
SELECT CAST(SYSDATE AS VARCHAR2(100)) FROM dual;


CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SELECT CONVERT('? ê í ó ? A B C D E ', 'US7ASCII', 'WE8ISO8859P1')   FROM DUAL; 

 HEXTORAW
将一个十六进制构成的字符串转换raw
 SELECT HEXTORAW('324') FROM DUAL;


RAWTOHEX
将RAW类数值rawvalue转换为一个相应的十六进制表示的字符串. rawvalue中的每个字节都被转换为一个双字节的字符串. RAWTOHEX和HEXTORAW是两个相反的函数.
SELECT RAWTOHEX('11') FROM DUAL;
 
TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
即将指定字符转换为全角并返回char类型字串 
SELECT TO_MULTI_BYTE('ABC abc　中华') FROM DUAL;


asciistr(str)
将任意字符集的字符串转换为当前数据库实例对应的ascii字符串。
SELECT asciistr('中华民族') FROM dual;
备注：和该函数相似的有ascii,它是取得字符串第一个字符的ascii码，后面的字符不管；chr是将ascii码转换为对应的字符。