selectto_date('2004-05-0713:23:44','yyyy-mm-ddhh24:mi:ss')fromdual//
2.selectto_char(to_date(222,'J'),'Jsp')fromdual显示TwoHundredTwenty-Two
3.求某天是星期几selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day')fromdual;星期一selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual;monday设置日期语言ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN';也可以这样TO_DATE('2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE=American')
4.两个日期间的天数selectfloor(sysdate-to_date('20020405','yyyymmdd'))fromdual;
8.selectcount(*)from(selectrownum-1rnumfromall_objectswhererownum<=to_date('2002-02-28','yyyy-mm-dd')-to_date('2002-02-01','yyyy-mm-dd')+1)whereto_char(to_date('2002-02-01','yyyy-mm-dd')+rnum-1,'D')notin('1','7')查找2002-02-28至2002-02-01间除星期一和七的天数在前后分别调用DBMS_UTILITY.GET_TIME,让后将结果相减(得到的是1/100秒,而不是毫秒).
9.查找月份selectmonths_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY'))"MONTHS"FROMDUAL;1selectmonths_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY'))"MONTHS"FROMDUAL;1.0322580645161310.Next_day的用法Next_day(date,day)Monday-Sunday,forformatcodeDAYMon-Sun,forformatcodeDY1-7,forformatcodeD
11selectto_char(sysdate,'hh:mi:ss')TIMEfromall_objects注意：第一条记录的TIME与最后一行是一样的可以建立一个函数来处理这个问题createorreplacefunctionsys_datereturndateisbeginreturnsysdate;end;selectto_char(sys_date,'hh:mi:ss')fromall_objects;12.获得小时数extract()找出日期或间隔值的字段值SELECTEXTRACT(HOURFROMTIMESTAMP'2001-02-162:38:40')fromofferSQL>selectsysdate,to_char(sysdate,'hh')fromdual;SYSDATETO_CHAR(SYSDATE,'HH')-----------------------------------------2003-10-1319:35:2107SQL>selectsysdate,to_char(sysdate,'hh24')fromdual;SYSDATETO_CHAR(SYSDATE,'HH24')-------------------------------------------2003-10-1319:35:2119
13.年月日的处理selectolder_date,newer_date,years,months,abs(trunc(newer_date-add_months(older_date,years*12+months)))daysfrom(selecttrunc(months_between(newer_date,older_date)/12)YEARS,mod(trunc(months_between(newer_date,older_date)),12)MONTHS,newer_date,older_datefrom(selecthiredateolder_date,add_months(hiredate,rownum)+rownumnewer_datefromemp))
14.处理月份天数不定的办法selectto_char(add_months(last_day(sysdate)+1,-2),'yyyymmdd'),last_day(sysdate)fromdual
16.找出今年的天数selectadd_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year')fromdual
闰年的处理方法to_char(last_day(to_date('02'||:year,'mmyyyy')),'dd')如果是28就不是闰年
17.yyyy与rrrr的区别'YYYY99TO_C-----------yyyy990099rrrr991999yyyy010001rrrr012001
18.不同时区的处理selectto_char(NEW_TIME(sysdate,'GMT','EST'),'dd/mm/yyyyhh:mi:ss'),sysdatefromdual;
19.5秒钟一个间隔SelectTO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300)*300,'SSSSS'),TO_CHAR(sysdate,'SSSSS')fromdual
2002-11-19:55:0035786SSSSS表示5位秒数
20.一年的第几天selectTO_CHAR(SYSDATE,'DDD'),sysdatefromdual3102002-11-610:03:51
21.计算小时,分,秒,毫秒selectDays,A,TRUNC(A*24)Hours,TRUNC(A*24*60-60*TRUNC(A*24))Minutes,TRUNC(A*24*60*60-60*TRUNC(A*24*60))Seconds,TRUNC(A*24*60*60*100-100*TRUNC(A*24*60*60))mSecondsfrom(selecttrunc(sysdate)Days,sysdate-trunc(sysdate)Afromdual)
select*fromtabnameorderbydecode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');//floor((date2-date1)/365)作为年floor((date2-date1,365)/30)作为月d(mod(date2-date1,365),30)作为日.
23.next_day函数返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。1234567日一二三四五六---------------------------------------------------------------select(sysdate-to_date('2003-12-0312:55:45','yyyy-mm-ddhh24:mi:ss'))*24*60*60fromddual日期返回的是天然后转换为ss24,round[舍入到最接近的日期](day:舍入到最接近的星期日)selectsysdateS1,round(sysdate)S2,round(sysdate,'year')YEAR,round(sysdate,'month')MONTH,round(sysdate,'day')DAYfromdual
25,trunc[截断到最接近的日期,单位为天],返回的是日期类型selectsysdateS1,trunc(sysdate)S2,//返回当前日期,无时分秒trunc(sysdate,'year')YEAR,//返回当前年的1月1日,无时分秒trunc(sysdate,'month')MONTH,//返回当前月的1日,无时分秒trunc(sysdate,'day')DAY//返回当前星期的星期天,无时分秒fromdual
26,返回日期列表中最晚日期selectgreatest('01-1月-04','04-1月-04','10-2月-04')fromdual
29.查找月的第一天,最后一天SELECTTrunc(Trunc(SYSDATE,'MONTH')-1,'MONTH')First_Day_Last_Month,Trunc(SYSDATE,'MONTH')-1/86400Last_Day_Last_Month,Trunc(SYSDATE,'MONTH')First_Day_Cur_Month,LAST_DAY(Trunc(SYSDATE,'MONTH'))+1-1/86400Last_Day_Cur_MonthFROMdual;
三.字符函数(可用于字面字符或数据库列)
1，字符串截取selectsubstr('abcdef',1,3)fromdual
2,查找子串位置selectinstr('abcfdgfdhd','fd')fromdual
3,字符串连接select'HELLO'||'helloworld'fromdual;
4,1)去掉字符串中的空格selectltrim('abc')s1,rtrim('zhang')s2,trim('zhang')s3fromdual2)去掉前导和后缀selecttrim(leading9from9998767999)s1,trim(trailing9from9998767999)s2,trim(9from9998767999)s3fromdual;5,返回字符串首字母的Ascii值selectascii('a')fromdual
6,返回ascii值对应的字母selectchr(97)fromdual
7,计算字符串长度selectlength('abcdef')fromdual
8,initcap（首字母变大写）,lower(变小写),upper(变大写)selectlower('ABC')s1,upper('def')s2,initcap('efg')s3fromdual;
9,Replaceselectreplace('abc','b','xy')fromdual;
10,translateselecttranslate('abc','b','xx')fromdual;--x是1位
11,lpad[左添充]rpad[右填充](用于控制输出格式)selectlpad('func',15,'=')s1,rpad('func',15,'-')s2fromdual;selectlpad(dname,14,'=')fromdept;
12,decode[实现if..then逻辑]注:第一个是表达式,最后一个是不满足任何一个条件的值selectdeptno,decode(deptno,10,'1',20,'2',30,'3','其他')fromdept;例:selectseed,account_name,decode(seed,111,1000,200,2000,0)fromt_userInfo//如果seed为111,则取1000;为200,取2000;其它取0selectseed,account_name,decode(sign(seed-111),1,'bigseed',-1,'littleseed','equalseed')fromt_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显
示相等
13case[实现switch..case逻辑]SELECTCASEX-FIELDWHENX-FIELD<40THEN'X-FIELD小于40'WHENX-FIELD<50THEN'X-FIELD小于50'WHENX-FIELD<60THEN'X-FIELD小于60'ELSE'UNBEKNOWN'ENDFROMDUAL注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时，用Decode更为简洁。
四.数字函数1，取整函数（ceil向上取整,floor向下取整）selectceil(66.6)N1,floor(66.6)N2fromdual;
2,取幂(power)和求平方根(sqrt)selectpower(3,2)N1,sqrt(9)N2fromdual;
3,求余selectmod(9,5)fromdual;
4,返回固定小数位数(round:四舍五入，trunc:直接截断)selectround(66.667,2)N1,trunc(66.667,2)N2fromdual;
5,返回值的符号(正数返回为1,负数为-1)selectsign(-32),sign(293)fromdual;
五.转换函数1，to_char()[将日期和数字类型转换成字符类型]1)selectto_char(sysdate)s1,to_char(sysdate,'yyyy-mm-dd')s2,to_char(sysdate,'yyyy')s3,to_char(sysdate,'yyyy-mm-ddhh12:mi:ss')s4,to_char(sysdate,'hh24:mi:ss')s5,to_char(sysdate,'DAY')s6fromdual;2)selectsal,to_char(sal,'＄99999')n1,to_char(sal,'＄99,999')n2fromemp
4,带有rollup和cube操作符的GroupByrollup按分组的第一个列进行统计和最后的小计cube按分组的所有列的进行统计和最后的小计selectdeptno,job,sum(sal)fromempgroupbydeptno,job;selectdeptno,job,sum(sal)fromempgroupbyrollup(deptno,job);cube产生组内所有列的统计和最后的小计selectdeptno,job,sum(sal)fromempgroupbycube(deptno,job);
八、临时表只在会话期间或在事务处理期间存在的表.临时表在插入数据时，动态分配空间createglobaltemporarytabletemp_dept(dnonumber,dnamevarchar2(10))oncommitdeleterows;insertintotemp_deptvalues(10,'ABC');commit;select*fromtemp_dept;--无数据显示,数据自动清除oncommitpreserverows:在会话期间表一直可以存在（保留数据）oncommitdeleterows:事务结束清除数据（在事务结束时自动删除表的数据）