oracle函数笔记(主要是日期函数)

黎2094

1，求一段时间内共跨越了多少天(例如求平均日访问量时要用到)
select to_date(to_char(max(t.logdate), 'yyyy-MM-dd'), 'yyyy-MM-dd') -
                              to_date(to_char(min(t.logdate), 'yyyy-MM-dd'),'yyyy-MM-dd') + 1
                         from tb_log t
2，Add_months(d,n) 当前日期d后推n个月(n可以为负数)
   当找不到某月的匹配日时取该月最后一天。例如3-31后推1个月为4-30
3，last_day(d)  某日期d所在月份的最后一天
4，转换某日期为'yyyy-m-d'格式
    select to_char(sysdate, 'yyyy') || '-' || to_number(to_char(sysdate, 'mm')) || '-' ||
       to_number(to_char(sysdate, 'dd')) from dual
5,查询系统时间戳的毫秒值
SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 +
       TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS MILLIONS
  FROM DUAL
见http://yangtingkun.itpub.net/post/468/244564

6,oracle函数处理字符串时，下标是从1开始的。

7,
INSTR方法的格式为
INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)  (注:起始位置是从1开始，不能从0开始)
例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置。
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是
Instring
——————
14

注:INSTR('test','a')得到的是0,而不是-1
参考:
http://df41.spaces.live.com/blog/cns!D064C4537B2605A6!317.entry
http://hi.baidu.com/fgfd0/blog/item/7a48d5f9155a0059252df2dc.html

8，to_date时报"格式代码出现两次"
   原因是"You are using MM twice"

9,substr( string, start_position, [length])  下标从1开始
substr('This is a test', 6, 2)   would return 'is'
substr('This is a test', 6)      would return 'is a test'
substr('TechOnTheNet', 1, 4)     would return 'Tech'
substr('TechOnTheNet', -3, 3)    would return 'Net'
substr('TechOnTheNet', -6, 3)    would return 'The'
substr('TechOnTheNet', -8, 2)    would return 'On'

10,select to_date(2008-03-14 14:49:37,'yyyy-MM-dd hh24:mi')  from dual
报错：ora-01830错误：日期格式图片在转换整个输入字符串之前结束
原因是格式化的字符窜长度大于格式化标准（yyyy-MM-dd hh24:mi）的长度
注意:月份的mm大小写不一样,hh24和hh不一样,分钟用mi而不是mm

11，lengthb查看字节数

12，case的用法
select case when t.classtype = 1 then
          '名称一'
         when t.classtype = 2 then
          '名城二'
       end name from tb_test t