oracle時間處理大全(轉)

很实用的时间处理知识点。
G vz(d2N1|TV ]zd25530136 ITPUB个人空间6T[4O7R@+R+pD2{
举例说明：
;J5J3X(]x6C255301361、日期字符转换函数to_date(),to_char() ITPUB个人空间'_ ? J8l2HG
   SQL：select to_date('20080229132545','yyyy-mm-dd hh24:mi:ss') from dual ； RESULT：2008-2-29 13:25:45 ITPUB个人空间_'B;]NcA M v
   SQL：select to_char(sysdate,'hh:mi:ss') TIME from dual; RESULT:10:51:43
na PdKx9v3r(o4X*jW255301362、
)n[%E2le25530136   SQL：select to_char( to_date( 2008,'J'),'Jsp') from dual； RESULT：Two Thousand Eight ITPUB个人空间_,t:U;lJ0P(_/] h[/y
3、查看哪天的年份
"Y(t^EN)y%h2wT25530136   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'year') from dual;     RESULT：two thousand eight
(oK+?Tq`3C+v25530136   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'yyyy') from dual;     RESULT：2008 ITPUB个人空间4CN$Z:w)n0_ i
4、查看哪天的月份 ITPUB个人空间 M"t ?8t-KE's
   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'month') from dual；RESULT：2月
@#s!L"w)g25530136   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'mm') from dual；    RESULT：02
}-dY~'CyD%OH255301365、查看哪天是星期几
5f;H@ kt o*Y1c)k25530136   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'day') from dual;      RESULT：星期五
J;`%O*plb7Q25530136   SQL：select to_char(to_date('2008-02-29','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; ITPUB个人空间Ss;pS0tt yW5c
   RESULT：friday ITPUB个人空间B)G4D.TS"n
   SQL：select to_char(to_date('2008-2-29','yyyy-mm-dd'),'dd') from dual；RESULT：29 ITPUB个人空间vF4P)~v:m,`I
6、查看两个日期之间的天数
v2{z_Lg25530136   SQL：select floor(sysdate - to_date('20080201','yyyymmdd')) from dual; RESULT：28
_N.gz5~25530136   注：sysdate是2008-02-29
HR1g ? {O[p_d255301367、取两个日期之间除了周六周日的天数 ITPUB个人空间(qOU#Tpt;{:D:o?
   SQL： ITPUB个人空间4J e;AP0h!l
   select count(*) ITPUB个人空间w e l[o]
   from ( select rownum-1 rnum
m.J~Uyj25530136   from all_objects ITPUB个人空间Q@+I&EINsk
   where rownum <= to_date('2008-02-29','yyyy-mm-dd') - to_date('2008- ITPUB个人空间8F @ T5^6v7M]c
   02-01','yyyy-mm-dd')+1 ITPUB个人空间0X@0Ak NR| gE|
   ) ITPUB个人空间 }Mcr,eN5LH7R,NT
   where to_char( to_date('2008-02-01','yyyy-mm-dd')+rnum-1, 'D' ) ITPUB个人空间1];] I QL
   not
L(|0[O(t`GBJ25530136   in ( '6', '7' ) ITPUB个人空间4w'E Lb5b7wj6S
   RESULT： ITPUB个人空间`KH)ZM7i:z7J'MCp
   21 ITPUB个人空间#Zx6ufK
8、查看两个日期之间的月数 ITPUB个人空间%jHU9p5n
   SQL：select months_between(to_date('2-29-2008', 'MM-DD-YYYY'),to_date('10-1-2007', 'MM-DD-YYYY')) "MONTHS" FROM DUAL; ITPUB个人空间8J5QYF,s+LJ
   RESULT：4.90322580645161 ITPUB个人空间f~'Y4F%km3\ ?
9、next_day函数 ITPUB个人空间3U+@L,vs%@Q'T
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
E(f:sy2w25530136   1 2 3 4 5 6 7 ITPUB个人空间KYH3ki ~
   日 一 二 三 四 五 六
U jF3{&`\4K7l C2553013610、查看小时分秒 ITPUB个人空间?&N {9X?4Iz*~"m
   select sysdate ,to_char(sysdate,'hh') from dual;
NF2Rzq*U5I,}25530136   select sysdate ,to_char(sysdate,'mi') from dual; ITPUB个人空间ojr)Y5PYU
   select sysdate ,to_char(sysdate,'ss') from dual; ITPUB个人空间.p:P'@Q6ucU
11、取一年的天数（例如今年2008）
$B E g V*h kc25530136   SQL：select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual ；RESULT：366 ITPUB个人空间-S%i%Q2E1V`e#t3V1r
12、 yyyy与rrrr的区别 ITPUB个人空间8fV$fwu
   yyyy 99 0099
p~eU6PIk25530136   rrrr 99 1999 ITPUB个人空间o ^7T {$ro0`;{*p"E
   yyyy 01 0001
:[_U"L9o^#V(d_N#S ?({25530136   rrrr 01 2001
:H'uT8c]2553013613、一年的第几天 ITPUB个人空间Sp:e6^;J/Z
   SQL：select TO_CHAR(SYSDATE,'DDD'),sysdate from dual ；RESULT：060
3izs&TQ)@fq25530136   注：sysdate是2008-02-29 ITPUB个人空间%k8fST-QpZG#tnG
14、add_months()用于从一个日期值增加或减少一些月份 ITPUB个人空间 ].@jUu.N.o\
   SQL：select add_months(sysdate,12) from dual;                      RESULT：2009-2-28 11:23:50 ITPUB个人空间i'R/F8Jr!\ ~pl
15、extract()找出日期或间隔值的字段值 ITPUB个人空间V+z+bK[*p"iw3F~
   SQL：select extract(month from sysdate) from dual; RESULT：2 ITPUB个人空间'`0{-ZAe;o?'Eo r
   SQL：select extract(year from add_months(sysdate,36)) from dual; RESULT：2011 ITPUB个人空间hH;l(h5G{U)P
16、last_day()返回包含了日期参数的月份的最后一天的日期
-C^ }8GHR'\$Cf25530136   SQL：select last_day(sysdate) from dual;                                         RESULT：2008-2-29 ITPUB个人空间 K{j+o.p~,d
ITPUB个人空间 }BOHu1K;J\(o
  ITPUB个人空间$Nl:q5fM-[W#L&@,w

#O} E3XVg KL7C}25530136===================================================================================================================
1]w6hu$j25530136
h%lt,jD&n0O*ih8]25530136TO_DATE格式 ITPUB个人空间T)UR r]2o_Zl}
　 Day:　　　
#U0v)S6c/y25530136　 dd　　　 number　　　　 12
(j@#b#d MJ7O,@25530136　 dy　　　 abbreviated　　fri　　　　　
s%mzrx/AQ1m25530136　 day　　　spelled out　　friday　　　　　　　　　　　　 ITPUB个人空间,j5tY dO5LG
　 ddspth　 spelled out,　 ordinal　 twelfth ITPUB个人空间6DjG4}MZI d
　 Month:　　 ITPUB个人空间Z*I0h+~ j*un%[
　 mm　　　 number　　　　 03 ITPUB个人空间Fo[5v1]OP
　 mon　　　abbreviated　　mar ITPUB个人空间3~ sf3C+Db7?$b({ o~
　 month　　spelled out　　march　 ITPUB个人空间d t9K4J,K3HZu\
　 Year:　　　　　　　　　　　　　 ITPUB个人空间7a VO?;q:RK4i
　 yy　　　 two　digits　　98 ITPUB个人空间w-\6OY9h5alv
　 yyyy　　 four　digits　 1998　　
:~%l U6H1Sx25530136　　
K5{T~ n7E#pv25530136　 24小时格式下时间范围为： 0:00:00 - 23:59:59....
%_3S~1MP6G25530136　 12小时格式下时间范围为： 1:00:00 - 12:59:59 ....　　 ITPUB个人空间1q,lK2x`Ys7HDGY
1. ITPUB个人空间*B}ARd(xM5fWR
　 日期和字符转换函数用法（to_date,to_char） 　　　 ITPUB个人空间fx-w9dl3K \|
　 ITPUB个人空间NK8@f1g8ZP
2.
,s;Fm yr"j N25530136　 select to_char( to_date(222,'J'),'Jsp')　from dual
ex@RmR25530136　　
*HAL F9L\25530136　 显示Two Hundred Twenty-Two ITPUB个人空间Z(ANmrY'{
　
p7TB-fC#i ?0S o255301363.求某天是星期几 ITPUB个人空间:X_-D/u){'c(kdi
　 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;　
;WK` u4[K }6T{25530136　 星期一
"OVT+{v*h7t25530136　 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;　
3{,F'fn t25530136　 monday　　 ITPUB个人空间:L2q7eF%ZX ~
　 设置日期语言
V V5SWi+L{25530136　 ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
3wdl*^8W2W]25530136　 也可以这样
:j!|4Q~D}!M25530136　 TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') ITPUB个人空间UW/myN.n ?
　
C*B}*A!i255301364.两个日期间的天数 ITPUB个人空间-e EZqj&Jr(f
　 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; ITPUB个人空间,mcS w/y$ez
　
x8fB nd-Y'y)HE255301365. 　　 时间为null的用法 ITPUB个人空间X!`'O3r1_;_`
　 select id, active_date from table1
b0`^&r"wM@!n2Z2M25530136　　 UNION ITPUB个人空间O ^2?'q,~(O
　 select 1, TO_DATE(null) from dual;
%tSV z:W1W9_25530136　　 ITPUB个人空间/?} Yb+Ph5s#su#no
　 注意要用TO_DATE(null) ITPUB个人空间 EO,ok4GCo
　
4Z.zc7w*F|U255301366.　
l+k)r'Nv P25530136　 a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd') ITPUB个人空间#o h3v.X0J d
　 那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
%pKP1w:bkg4b,W25530136　 所以，当时间需要精确的时候，觉得to_char还是必要的 ITPUB个人空间&e&HW [y p q1v;|
7.　　　日期格式冲突问题 ITPUB个人空间} ^[~4Ek
　　　　 输入的格式要看你 安装的 ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01' ITPUB个人空间-U9j-yX,X'Wo{
　 alter system set NLS_DATE_LANGUAGE = American ITPUB个人空间D'@|-I%Ha
　　　　 alter session set NLS_DATE_LANGUAGE = American ITPUB个人空间dtM D;AM/b
　 或者在to_date中写 ITPUB个人空间[)h2Y1uh?5m
　 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;　 ITPUB个人空间w ]"sJQ(C"hF
　 注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， ITPUB个人空间.m$}/RSk
　 可查看 ITPUB个人空间@|*]qG
　 select * from nls_session_parameters ITPUB个人空间.Gq+J Z.P[~3e'^,P
　 select * from V$NLS_PARAMETERS ITPUB个人空间8}a1T _yX/Xn
ITPUB个人空间 l2rw.W#Y!YX
　日期和字符转换函数用法（to_date,to_char）
&^$XS8\*Is25530136　　　　 We overwrite NLS_DATE_FORMAT into different formats for the session.　
(C_ Kl5de N25530136　　　　 sql> alter session set nls_date_format = 'DD-MON-RR'; Session altered.
%s#~+[1\7l*h N\25530136　　　　 sql> set pagesize 0
i Rj"J:[!R ^/|8T'O!?3u25530136　　 sql> set linesize 130 ITPUB个人空间 pWPC UY~P
　　 sql> select * from nls_session_parameters; ITPUB个人空间w+Rc`Mz@7]'X
　　 NLS_LANGUAGE　　　　　　　　　 AMERICAN ITPUB个人空间(j3zM dFA0U
　　 NLS_TERRITORY　　　　　　　　　AMERICA
Pi+d u yg,@&A!@&LY25530136　　 NLS_CURRENCY　　　　　　　　　 $ ITPUB个人空间8g2p'IG a Z
　　 NLS_ISO_CURRENCY　　　　　　　 AMERICA
&y(a$x+`&\H25530136　　 NLS_NUMERIC_CHARACTERS　　　　 ., ITPUB个人空间Mp;O4@0J0m.e^
　　 NLS_CALENDAR　　　　　　　　　 GREGORIAN ITPUB个人空间2b2C*~;PU j`9L,Y
　　 NLS_DATE_FORMAT　　　　　　　　DD-MON-RR
V)PD s%p Z_25530136　　 NLS_DATE_LANGUAGE　　　　　　　AMERICAN ITPUB个人空间9\ A ||uf v3_%d{{
　　 NLS_SORT　　　　　　　　　　　 BINARY
,y%}Xq![25530136　　 NLS_TIME_FORMAT　　　　　　　　HH.MI.SSXFF AM ITPUB个人空间-b~)TSz^7E3I!p
　　 NLS_TIMESTAMP_FORMAT　　　　　 DD-MON-RR HH.MI.SSXFF AM
7|;`{7Fw8W X f25530136　　 NLS_TIME_TZ_FORMAT　　　　　　 HH.MI.SSXFF AM TZH:TZM ITPUB个人空间x!Cq]+G/Q6Y"U(t
　　 NLS_TIMESTAMP_TZ_FORMAT　　　　DD-MON-RR HH.MI.SSXFF AM TZH:TZM ITPUB个人空间3RSLL6K'e#c\
　　 NLS_DUAL_CURRENCY　　　　　　　$ ITPUB个人空间I8M!z0a)a9WC(m%Q GK
　　 NLS_COMP　　　　　　　　　　　 BINARY
{i?'`6@ v y1y25530136 ITPUB个人空间(O+mhzp w0vVT[i!F
　　 15 rows selected. ITPUB个人空间!Nd T'|M:R{%d
　　　　　specify it in sql statement:
R:K.S0J+ul25530136　　　　　
|j!TKQ5w}t25530136　　　　sql> select to_date('03-SEP-1999','DD-MON-YYYY') from dual; ITPUB个人空间`,JL uO
　　　　 03-SEP-99
Ts/On3z1M#z25530136　　　　sql> alter session set nls_date_format = 'MM-DD-YYYY';
jG)z7E M25530136　　　　　　 Session altered. ITPUB个人空间P{-_K h
　　　　　　
k'l ug(m-yMT HSD25530136　　　　sql> select to_date('03-SEP-99','DD-MON-YY') from dual; ITPUB个人空间^,LT9@7ME6m3C ~V
　　　　　　 TO_DATE('0
Je-N._ VJ25530136　　　　　　 ----------
4U^6tu Y,_Kp q25530136　　　　　　 09-03-1999　 ITPUB个人空间 { n0E-ut
　　　　sql> alter session set nls_date_format = 'RRRR-MM-DD'; ITPUB个人空间J3?E3nNe|)[ B{#c
　　　　　　 Session altered.　
4~h9LAZ"Ky$u:CTa25530136　　　　　　
xA!~Wg)@&r#~25530136　　　　sql> select to_date('03-SEP-1999','DD-MON-YYYY') from dual;
D;Tp(Ce2I;i25530136　　　　　　 TO_DATE('0
`(^a!\G y25530136　　　　　　 ----------
7l)F+x&a:q25530136　　　　　　 1999-09-03　
y8_:hS4@\'nT25530136　　　　　　 ITPUB个人空间qB Z!JV
　　When we use TO_CHAR function, we get expected results of format from　current sql statement:　
!r3b/\ jFS25530136　　　　sql> alter session set nls_date_format = 'MM-DD-YY'; ITPUB个人空间p7Zz P e A9Z&k
　　　　　　 Session altered.　 ITPUB个人空间 n yM9mS6^h)wc
　　　　sql> select to_char(sysdate,'dd-mm-yyyy') from dual; ITPUB个人空间wYX3h1} uf\
　　　　　　 TO_CHAR(SYSDATE,'DD-MM-YYYY')　 ITPUB个人空间J)f5s9eH2A
　　　　　　 07-09-1999　
;q4s;x qrX"Jo$E9G)x25530136　　　　sql> alter session set nls_date_format = 'RR-MON-DD'; ITPUB个人空间9foe(L,LnZ\ V
　　　　　　 Session altered.　 ITPUB个人空间 HD/BX a
　　　　sql> select to_char(sysdate,'dd-mon-yy') from dual;
X2]+ygbi$f25530136　　　　　　 TO_CHAR(SYSDATE,'DD-MON-YY')　 ITPUB个人空间`|qm]r
　　　　　　 07-sep-99　 ITPUB个人空间yAv0h'w
　　　　sql> select to_char(sysdate,'dd-Mon-yy') from dual;
2U!qO#]&{Vd6N'D,u _25530136　　　　　　 TO_CHAR(SYSDATE,'DD-MON-YY')　 ITPUB个人空间Sb`2j6A:f7r9r/k t;wL
　　　　　　 07-Sep-99
G#UX B {2SN t255301368. ITPUB个人空间/t CDf*]5b:M_
　 select count(*)　
:_O2WfgI,I25530136　 from ( select rownum-1 rnum　 ITPUB个人空间6K"V/oUUe
　　　　　from all_objects　
E mf+?Om25530136　　　　　where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
)y{}:v'tmrc&s25530136　　　　　 02-01','yyyy-mm-dd')+1　 ITPUB个人空间*{4wS8jbW
　　　　 )　 ITPUB个人空间5l`hq^u
　 where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) ITPUB个人空间P)\%M&f y}&uE
　 not　
'Q|G0b ?+m zm;n25530136　 in ( '1', '7' )
JOS%k#|$jK j7T25530136　　 ITPUB个人空间5da c ]R D&v8Cf8^
　 查找2002-02-28至2002-02-01间除星期一和七的天数
ZZ2@:fG-{25530136　 在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).　　 ITPUB个人空间}5Mm']-z*s'a+t
　
m5z"V+|.b oB255301369.
wP5x7CE25530136　 select months_between(to_date('01-31-1999','MM-DD-YYYY'),　
5y U Nx\1NB E"C25530136　　 to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;　
[ng#Kiv25530136　 1
~ \ATfR O25530136　　 ITPUB个人空间L3wa*lc} e
　 select months_between(to_date('02-01-1999','MM-DD-YYYY'),　　
W h,kZ8mA'd25530136　　 to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;　
a+B,D WuPv%i?!x25530136　　 ITPUB个人空间gMw5s@2V^8Wn
　 1.03225806451613
+Z:W)PQ Q2553013610.　　 Next_day的用法
~0]N:w4D25530136　 Next_day(date, day) ITPUB个人空间*@(Yx7O#j#fgE
　　 ITPUB个人空间d3d8S3q R
　 Monday-Sunday, for format code DAY　　　
*Q+L9bl&t!W*J25530136　 Mon-Sun, for format code DY　　　　　 ITPUB个人空间 AU\[D$tv r
　 1-7, for format code D　 ITPUB个人空间8F$_})R{ w]g1w Q#F"A
　
}vM._-jn6c^Q)_F7i2553013611　　　 ITPUB个人空间+jf&l$dHK0["i
　 select to_char(sysdate,'hh:mi:ss') TIME from all_objects ITPUB个人空间o ChJ(Pz
　 注意：第一条记录的TIME 与最后一行是一样的 ITPUB个人空间 ACt)LW![(} _F4S%d
　 可以建立一个函数来处理这个问题
@3M:j$i5nsU{L,b;n25530136　 create or replace function sys_date return date is　
?T [5u a q*KT25530136　 begin　
3f&X'u5X/\25530136　 return sysdate;　 ITPUB个人空间)J7r].F{
　 end;　
?7|_-e/_%@K;^25530136　　
@q\t+]$Vv)q25530136　 select to_char(sys_date,'hh:mi:ss') from all_objects;　 ITPUB个人空间c6o1UV ?i*Cw
12. ITPUB个人空间%d$L!O+M9X+c"Gr4U!K0D
　　　　 获得小时数 ITPUB个人空间;y6`9HCQ3[p
　　　　　
:E4@k-C0iW3{t sJM25530136　　　　 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer ITPUB个人空间0euTm^ Fe#^
　　　　 sql>　 select sysdate ,to_char(sysdate,'hh')　from dual;
0Gtz_ xR![\6A,h25530136　　　　　 ITPUB个人空间c[A#BV
　　　　 SYSDATE　　　　　　　TO_CHAR(SYSDATE,'HH') ITPUB个人空间'A q C\6Ee%NuN
　　　　 -------------------- ---------------------
W)p@@2|j25530136　　　　 2003-10-13 19:35:21　07
w+`1t_.q,Q\g25530136　　　　　 ITPUB个人空间 G)[ cG6rcw3C1S~
　　　　 sql>　 select sysdate ,to_char(sysdate,'hh24')　from dual; ITPUB个人空间'y j w-S/y
　　　　　 ITPUB个人空间6]pc1n!w9i l.C F:W
　　　　 SYSDATE　　　　　　　TO_CHAR(SYSDATE,'HH24')
[Mk6[D"oG5d,gIV25530136　　　　 -------------------- -----------------------
1K6ol4A9zT:Q25530136　　　　 2003-10-13 19:35:21　19 ITPUB个人空间tG{~5S3XB/{'_ M
　　　　　
K cL7[(uju25530136　　　　 获取年月日与此类似 ITPUB个人空间msx4`Y'G0_P
13. ITPUB个人空间v jUv|
　 年月日的处理 ITPUB个人空间'^z^mp
　 select older_date, ITPUB个人空间;f}O$F$j+Y#n
　　　　　　newer_date, ITPUB个人空间4^q }N})C0F
　　　　　　years, ITPUB个人空间5?8fc6U~+~*B rM
　　　　　　months,
%^,Z6F*L ?,~-D25530136　　　　　　abs( ITPUB个人空间nc]pA?0H
　　　　　　　trunc(
0~ vlJ;D itv9P25530136　　　　　　　　 newer_date-
"`@/Ey*S25530136　　　　　　　　　　 add_months( older_date,years*12+months ) ITPUB个人空间]MZN/\4Hw1TN
　　　　　　　　　 )
UE C|![-\IQ25530136　　　　　　　) days ITPUB个人空间vZq q]$Nrw ?{ f1g
　　　from ( select　
/dB3E PR-o n25530136　　　　 trunc(months_between( newer_date, older_date )/12) YEARS, ITPUB个人空间pax {\%Wp
　　　 mod(trunc(months_between( newer_date, older_date )),　
Vl1iA5d[ck"wu25530136　　　　　　 12 )　MONTHS, ITPUB个人空间0|1DQ;Q jE
　　　　　　　　　　newer_date,
`lL's)l25530136　　　　　　　　　　 older_date
r@a#[8D@)q$ia25530136　　　　　　　 from ( select hiredate older_date, ITPUB个人空间 s(S%s|5zZ#jz.?
　　　　　　　　　 add_months(hiredate,rownum)+rownum newer_date ITPUB个人空间xEpW_'B:c&} x"W
　　　　　　　　　　　　from emp ) ITPUB个人空间,D9{E*Qo5IA7O(JP
　　　　　 ) ITPUB个人空间Z u w;y'~
　 ITPUB个人空间zA_H:c&o%U!bo6y
14.
&kU p9t;G8r.[25530136　 处理月份天数不定的办法
M!~0c1^-qG [P.h'Y25530136　 select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
z\)n4R ZW7zH25530136　 ITPUB个人空间 j:p;ff6j!dG
16. ITPUB个人空间h8A0l8d0c`[
　 找出今年的天数
@\*^ Q J.K25530136　 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
U6G-W&LT;k#[;dUA8O/`+KU25530136　　 ITPUB个人空间QqhX6BtzQ'E P:I+r
　 闰年的处理方法 ITPUB个人空间zC'l*~&t9?F\o
　 to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' ) ITPUB个人空间s3Fnye
　 如果是28就不是闰年
^ O @BBrD*q25530136　 ITPUB个人空间e,v;E F^Q.M)A Y,ICv
17.
-DbU ]_~i25530136　 yyyy与rrrr的区别 ITPUB个人空间L'N#l W[
　 'YYYY99 TO_C ITPUB个人空间$FV J i5M"@$y$[-_
　 ------- ----
.L!{&f(PN^(g*C6l25530136　 yyyy 99 0099
Hv hI;H25530136　 rrrr 99 1999 ITPUB个人空间3E J A"h0pY
　 yyyy 01 0001
2r5Q:`VJbV25530136　 rrrr 01 2001
t)I9hy{25530136　 ITPUB个人空间OBVOJ%Kh*c"[\(Qr
18.不同时区的处理
"Y?E-N&]25530136　 select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate ITPUB个人空间u nl u6u E.Q4|JL
　 from dual;　 ITPUB个人空间g~$O Za
　
]CT5k%uo;zC2553013619.
\/HWb$M"SsU25530136　 5秒钟一个间隔 ITPUB个人空间S%tj`$f8mS9u
　 Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
2D chWjf8g[25530136　　from dual ITPUB个人空间2X9TW$T0h
　　 ITPUB个人空间OaG7cX e'x5P,aH?
　　2002-11-1 9:55:00　35786
1g1Oc#I IZw ? |25530136　　SSSSS表示5位秒数 ITPUB个人空间d4g$_&K j8[
　
/l{T^A'k;o2553013620. ITPUB个人空间 X"|f"U u7FQb
　 一年的第几天 ITPUB个人空间q;AeB!n$aw
　 select TO_CHAR(SYSDATE,'DDD'),sysdate from dual　
4V;ND0f"E2X NGe25530136　 310　2002-11-6 10:03:51
*n})k N7P)Ox^7W25530136　
U%Ea1bt mQI:H:p'F3q2553013621.计算小时,分,秒,毫秒 ITPUB个人空间OT(n4nh-J.h
　 select ITPUB个人空间b@,K9q^.Ns _%\
　　　　　 Days,
-Z4NP}`FA25530136　　　　　 A,
9R\'M7Z(h c25530136　　　　　 TRUNC(A*24)　　　　　　　　　　　　　　　　　　 Hours,
&O[2u&A;R8B` ]25530136　　　　　 TRUNC(A*24*60 - 60*TRUNC(A*24))　　　　　　　　 Minutes, ITPUB个人空间\ MM0s1l
　　　　　 TRUNC(A*24*60*60 - 60*TRUNC(A*24*60))　　　　　 Seconds, ITPUB个人空间L.M-R.WdT F(qVr|
　　　　　 TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60))　 mSeconds
4JPbxb5j25530136　 from ITPUB个人空间o c_ e"m7F*PY5W(O
　 (
/r/sGa]j3F~(^4_25530136　 select
o]4@*B.ZSTeE ^ x25530136　　　　　 trunc(sysdate)　　　　　　 Days,
}t~h/C k&H25530136　　　　　 sysdate - trunc(sysdate)　　　A
E[ FFjO25530136　 from dual
wZ#iU4hn25530136　 )
({Vt$a/zA25530136　 ITPUB个人空间MG.e }o.axe+d
　　
E1]]\9M8U255301369i以上版本
$n5I`Wt!w25530136　　-----------------------------
tf#N9hXq D*{A25530136　　sql>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1, ITPUB个人空间Z:L;dU&z&G p&k
　　to_char(current_timestamp) time2 from dual;
k1l JBMnz25530136　　 ITPUB个人空间@PG%{3n.bFd
　　TIME1　　　　　　　　　　　　 TIME2 ITPUB个人空间`L6Pi Q6Sov
　　----------------------------- ----------------------------------------------------------------
'q;OhV%w:l&L25530136　　 2003-10-24 10:48:45.656000　　24-OCT-03 10.48.45.656000 AM +08:00
1w-MJy@ `U0B25530136　　可以看到，毫秒在to_char中对应的是FF。 ITPUB个人空间4cc7e a,s
　　 ITPUB个人空间1p*Lcqk3sa9bC
　　sql> select to_timestamp('2003-10-24 10:48:45.656000','yyyy-mm-dd hh24:mi:ssxff') from dual; ITPUB个人空间M5` sS;T9rYn
　　
Gp.@|:Q!ft\+\5mH25530136　　 TO_TIMESTAMP('2003-10-2410:48:
1G5j;Lm1o e;s9y$G+^25530136　　 -------------------------------------------------
4r(c7jjg25530136　　24-10月-03 10.48.45.656000000 上午 ITPUB个人空间6`W*l@L1v2jf}"ZH|:}+Y
　 ITPUB个人空间.?-oJ e unw-w
22.
y9tOF"}7n*C@ |25530136floor((date2-date1) /365) 作为年
;T0uwB d @Xj25530136floor((date2-date1, 365) /30) 作为月 ITPUB个人空间5r9e D,PI*_G2v
mod(mod(date2-date1, 365), 30)作为日.
&TrX"hA1s2553013623.next_day函数 ITPUB个人空间&f#`&pr#Y h(bC
next_day(sysdate,6) 是从当前开始下一个星期五。后面的数字是从星期日开始算起。　 ITPUB个人空间u1c&Xh6g&O(r@xg
1　2　3　4　5　6　7　 ITPUB个人空间u4m;u/c n C7i H%|8Se4k
日 一 二 三 四 五 六　
-f |-_8S/}/OX25530136　
W9x*j6zP(rz2553013624.取出一个时间段中星期日星期六的天数 ITPUB个人空间(HB!v&{.h6Y+v
function weekends( p_date1 in date, p_date2 in date ) ITPUB个人空间+iJv4p!h!~0c CL
return number ITPUB个人空间5mW&E/L[N
as ITPUB个人空间H PtZv @6_2}?
　　 l_date1 date default least(p_date1,p_date2); ITPUB个人空间;x x6B^ {
　　 l_date2 date default greatest(p_date1,p_date2); ITPUB个人空间)^0y|_,^
　　 l_days　number default trunc(l_date2-l_date1)+1; ITPUB个人空间SZM4rhO0? DM2g
　　 l_cnt　 number;
P}J)Y ]~5D J25530136begin
I M+AQ8Mj(y3L25530136　　 select count(*) into l_cnt ITPUB个人空间7LhCua%J!D
　　　 from (select rownum r　 ITPUB个人空间$}Ws2x:Q5a
　　　　　　　 from all_objects where rownum <= l_days)
?J6f Si#B25530136　　　where to_char(l_date1+r-1,'dy') in ( 'sat','sun' );
*G)kg J8h2CF25530136　 ITPUB个人空间 uA@9b DR6J1b.F{
　　 return l_cnt; ITPUB个人空间1a(D5bf0M4iG8{u}
end;O ITPUB个人空间N_wB a&ku
ITPUB个人空间o"g'n:mBC

"Da#?.b1e;u8l+E25530136
+m3_5yK;L7|Z.f25530136
,[$e,R2G1RMo25530136
9t!s#H{:Jg$^{25530136 ITPUB个人空间%^7];~qLoq$c6A5ID
ORACLE日期时间函数大全
7\1nBlgR25530136 ITPUB个人空间vv/jZ5o2z K;w
   TO_DATE格式(以时间:2007-11-02   13:45:25为例)
@0~+[&d WW]{25530136 
/G pw7D N|25530136        Year:     ITPUB个人空间 h7E'Q _0B
        yy two digits 两位年                显示值:07 ITPUB个人空间NgQa G
        yyy three digits 三位年                显示值:007
K/F$Ce_G]25530136        yyyy four digits 四位年                显示值:2007
\7`s,}}25530136           ITPUB个人空间z.Lg9J:G S3b
        Month:    
hpZ5HR ?.pF25530136        mm    number     两位月              显示值:11
i#@^$W/h ~,|.HJ BD25530136        mon    abbreviated 字符集表示          显示值:11月,若是英文版,显示nov    ITPUB个人空间/k0`E\l*B[pQ
        month spelled out 字符集表示          显示值:11月,若是英文版,显示november
4_w r4G*y;Qv"te25530136        
#M%X B1i(h25530136        Day:     ITPUB个人空间.lXa H+sNW-Hc6W
        dd    number         当月第几天        显示值:02 ITPUB个人空间}:N-@3CFlci
        ddd    number         当年第几天        显示值:02 ITPUB个人空间P(y_"c?V,F
        dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri
*fS,Ob*Ul4nG&z25530136        day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday      
M&H#@l,aez+u3M*U25530136        ddspth spelled out, ordinal twelfth
]3h'@^3_C[D25530136            ITPUB个人空间({9~^,Y5wm,Mk
              Hour: ITPUB个人空间P;p;P:w!A0X!} Vf
              hh    two digits 12小时进制            显示值:01 ITPUB个人空间X9\6YN"km^ \^
              hh24 two digits 24小时进制            显示值:13 ITPUB个人空间a AP)~ Y
            
%sK6[#J$D25530136              Minute: ITPUB个人空间)M_m BH y2x(k~
              mi    two digits 60进制                显示值:45
%b ^"w}9`/Hjm.o25530136            
'w/k:x8e B;A25530136              Second: ITPUB个人空间I2RZp/d7HK
              ss    two digits 60进制                显示值:25 ITPUB个人空间\1Th;nQ"e
             ITPUB个人空间J1Iu&H e+l
              其它 ITPUB个人空间:[i^@.L%A
              Q     digit         季度                  显示值:4
;z-J q6g u:R@ J25530136              WW    digit         当年第几周            显示值:44 ITPUB个人空间,c:?nxo!U0H V?!U dM
              W    digit          当月第几周            显示值:1
'K(aO3O3f7[^ ^9Y25530136            
~&Qq w zs25530136        24小时格式下时间范围为： 0:00:00 - 23:59:59....     ITPUB个人空间 L)ZeB*wSL
        12小时格式下时间范围为： 1:00:00 - 12:59:59 .... ITPUB个人空间6y5gy6^ l z-Vu [G
          
#fZEK-j'~a!]255301361. 日期和字符转换函数用法（to_date,to_char） ITPUB个人空间BH:D"L6j
       
eAOI0e'Z.e!BA25530136select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串 
+W w*^ ^&n'B+DNe25530136select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年  ITPUB个人空间3| Ht6lS,^]
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月  ITPUB个人空间7l8f.m#C5?&d4|,JN
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日 
(U,M;V?s25530136select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时 
G%g {\ k"C(X'y`i&H25530136select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分  ITPUB个人空间8f,@RU\a
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒 ITPUB个人空间6i*K@{jP-M.j

e%q\yg~25530136  
dZ4T!^#g25530136select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//
%s r;Gl/ZM f%L25530136
hu x%R+o%u`Z255301362.    
tGi8r_}v#i JO)\25530136    select to_char( to_date(222,'J'),'Jsp') from dual     ITPUB个人空间3FM,kYg"m4f&z j
  
C4y Wk:k)]25530136    显示Two Hundred Twenty-Two   
"H)QK'f~%F3HD,D25530136 ITPUB个人空间3|}|#k)j6Z s\d
3.求某天是星期几    
R'o4E1E qQ)Z5Nm25530136   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;    
k0K5R Y.n%x3G S25530136   星期一    
^*jD;eb+BoW25530136   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;     ITPUB个人空间*Cm*S}.u3Gf8w7l
   monday    
D8i*V![+u25530136   设置日期语言     ITPUB个人空间+XI(lU/x
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';    
OH5Dd)u:f25530136   也可以这样     ITPUB个人空间%d$}Pm%g a'fR2gq
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')   
c`] J;\*DA8f25530136
(H9bX!v3I:A j@255301364. 两个日期间的天数    
@HD;}!Xl AQ25530136    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;   
$s;F(TRM K25530136
b,jLv;M@255301365. 时间为null的用法    
B+[q,\6?25530136   select id, active_date from table1     ITPUB个人空间5x#n@&oy.w9|*s
   UNION    
c[8zNfa E/o1v*X25530136   select 1, TO_DATE(null) from dual;     ITPUB个人空间G3ku3aj7^
  ITPUB个人空间A2eAKMK
   注意要用TO_DATE(null)   
6x+Dv,}5cmX$O pR&N5_25530136
:];~R } e7D],_)GP5m255301366.月份差  ITPUB个人空间$a-On:N)@1Tw)I
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')     ITPUB个人空间)R9s^!E1^
   那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。     ITPUB个人空间bp BT%FE^2~@`:X
   所以，当时间需要精确的时候，觉得to_char还是必要的 ITPUB个人空间v|;R"V te$p].q9br
     ITPUB个人空间i5X~S4G?t!R
7. 日期格式冲突问题    
l nR\,uS V25530136    输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'    
5tI,W%{q%Sqb25530136    alter system set NLS_DATE_LANGUAGE = American    
!I%Vu2w4E5bk3`25530136    alter session set NLS_DATE_LANGUAGE = American    
O#Uw(Vzh25530136    或者在to_date中写     ITPUB个人空间.PbZ7tT4|"Rg'G
    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    
.iCGv N-sn25530136    注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，     ITPUB个人空间G(met$e0\c wg
    可查看    
.B-[bf\,Kw^t e25530136    select * from nls_session_parameters     ITPUB个人空间_;sC MJ$c(H
    select * from V$NLS_PARAMETERS    ITPUB个人空间?C2n.j5g i[7n

_/`jfcYta255301368.    
+W2p%z\\ sK"D25530136   select count(*)    
+ZbT b!L E,`25530136   from ( select rownum-1 rnum     ITPUB个人空间N3Gp!RGKo-@
       from all_objects    
,r~B7u/}8vny%I"Uf25530136       where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-     ITPUB个人空间ocD+s9ZZ$hQ8}]+\/rA
       02-01','yyyy-mm-dd')+1    
[@"\kl25530136      )    
W iPx7I$_+u25530136   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )     ITPUB个人空间[Ct'J2}-[C
        not in ( '1', '7' )     ITPUB个人空间o;I!Nw&n)a`F fb
 
mjJ"n6e(uE)j25530136   查找2002-02-28至2002-02-01间除星期一和七的天数     ITPUB个人空间V(v1r6P O
   在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).   
;Z9g] m&aQ:}25530136 ITPUB个人空间K7U)l8Kig'a6lL Y
9. 查找月份    ITPUB个人空间'iG%u1pX Cz
    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;     ITPUB个人空间f6Al*^/F j$@
    1     ITPUB个人空间2q]`+A L.ZE\8`U
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;     ITPUB个人空间H7Q9y Km#`O
    1.03225806451613
F+ef U6g[L25530136      ITPUB个人空间)O1[-{:qs
10. Next_day的用法     ITPUB个人空间&] e5?7Yd4k'?2TcC:p
    Next_day(date, day)    
+qGbG1qEy*Qd25530136   ITPUB个人空间*z*EvQ?(`QK
    Monday-Sunday, for format code DAY     ITPUB个人空间})U7y8{a)c5r4u
    Mon-Sun, for format code DY     ITPUB个人空间.oK A"IOj T
    1-7, for format code D    ITPUB个人空间;|z5^)Jz rw/XD1V
ITPUB个人空间 S3i2}Z{
11    
f!I"O+G Q7n:|nK25530136   select to_char(sysdate,'hh:mi:ss') TIME from all_objects     ITPUB个人空间:Ne2D9^;aq6KGx@.k
   注意：第一条记录的TIME 与最后一行是一样的     ITPUB个人空间,P+I0E+ymjT1Z f9g
   可以建立一个函数来处理这个问题     ITPUB个人空间;Z6{ `"?$Z5n6[
   create or replace function sys_date return date is    
y%U+{rR,QBh"?Px25530136   begin     ITPUB个人空间J3l)X0K^G
   return sysdate;    
+h4`d+Pq*Q3Eb25530136   end;    
'R0`6U-H8B`,u7v&[25530136 
P6lw-f;L&Iv"X25530136   select to_char(sys_date,'hh:mi:ss') from all_objects; 
4Ao8n s"},B(X"v25530136    ITPUB个人空间4B|$fA9IL Z-e
12.获得小时数     ITPUB个人空间*ZePsfdgo[
     extract()找出日期或间隔值的字段值
vM"Op y i25530136    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer     ITPUB个人空间+R-V7y z"~g2B
    SQL> select sysdate ,to_char(sysdate,'hh') from dual;     ITPUB个人空间(M_WZ4P#L H*\
  
6g$m8?&soxo25530136    SYSDATE TO_CHAR(SYSDATE,'HH')    
hB;V/W eY5l25530136    -------------------- ---------------------    
q |X_\v/K25530136    2003-10-13 19:35:21 07     ITPUB个人空间]}!j:i I(Q#}w
   ITPUB个人空间p/{vH\C
    SQL> select sysdate ,to_char(sysdate,'hh24') from dual;     ITPUB个人空间9KWZ7~D"A
  
7u|k.T a:{$Q25530136    SYSDATE TO_CHAR(SYSDATE,'HH24')     ITPUB个人空间8cT}i$Q$?WB0c
    -------------------- -----------------------    
?ty3yL25530136    2003-10-13 19:35:21 19   
D2}+eRc3D4_y4x25530136
b` UWG6t9ws25530136      ITPUB个人空间L VsH s_
13.年月日的处理     ITPUB个人空间 f:n*c:g;K{D
   select older_date,     ITPUB个人空间dCNz:aG%L.Wo
       newer_date,     ITPUB个人空间R|v{/r+m
       years,    
rU(L3SH25530136       months,     ITPUB个人空间S V8SXp9Mk
       abs(     ITPUB个人空间,I-WS~'I6X
        trunc(     ITPUB个人空间7d4Oj.SSB*JKj
         newer_date-    
3ugj!}[25530136         add_months( older_date,years*12+months )     ITPUB个人空间4nt)|^3m whr Vn
        )    
qtcnL?25530136       ) days ITPUB个人空间!H$U2D U&fL
     
*M:v6q/CKm25530136   from ( select     ITPUB个人空间,Ng$c-c0P Fo$Z5mR(o
        trunc(months_between( newer_date, older_date )/12) YEARS,    
7~9G4G5v c9Zo25530136        mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,     ITPUB个人空间kL,F"e)i6x
        newer_date,     ITPUB个人空间e as3f;l
        older_date    
h7hL#w-`4d5Ag*|25530136        from ( ITPUB个人空间4Vb0aKY#H
              select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date     ITPUB个人空间s5Gl$w&W2Hl^c
              from emp ITPUB个人空间,EM#} bA'P(w \
             )     ITPUB个人空间:qz"["P/[:YQ
      )    ITPUB个人空间'l9yfX*YK w
ITPUB个人空间X'd#A8})|
14.处理月份天数不定的办法     ITPUB个人空间7Oe4Lq?(k
   select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual    ITPUB个人空间wO8F6v'] XU

p,~'P'tnZ2553013616.找出今年的天数    
"@%\wE%pv0Z6f25530136   select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual    ITPUB个人空间!Y6}9R']?%hy

x4k3Gb$IN'Y N25530136   闰年的处理方法     ITPUB个人空间!J/Dz O7MQ Yng)P?8D
   to_char( last_day( to_date('02'    | | :year,'mmyyyy') ), 'dd' )     ITPUB个人空间4h{PcL%p r#gb b
   如果是28就不是闰年   
Gz(Y\S0_25530136 ITPUB个人空间3`K.Y0S2KKt4Q
17.yyyy与rrrr的区别     ITPUB个人空间 v;Ag4wqYx
   'YYYY99 TO_C    
&akQ-jZ2a9v#b25530136   ------- ----    
c7{N;|F }AB.`0j25530136   yyyy 99 0099     ITPUB个人空间 SVyF;k7\n
   rrrr 99 1999     ITPUB个人空间+VV`fSF
   yyyy 01 0001     ITPUB个人空间|"vMj YN X
   rrrr 01 2001    ITPUB个人空间Eo g,sNR[p

!EvK/u5P2553013618.不同时区的处理     ITPUB个人空间e+U8?WMv wd
   select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate     ITPUB个人空间A)\l2g^;C5w+j
   from dual;    ITPUB个人空间a1O;zGZ^&U

'dz$}+V?P!n2553013619.5秒钟一个间隔    
BZy~8[-CM{25530136   Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')    
?5W:n^%A/Jj25530136   from dual    ITPUB个人空间B DI8[ e
ITPUB个人空间@7uAYbB-XX
   2002-11-1 9:55:00 35786     ITPUB个人空间6A#J-~'|[
   SSSSS表示5位秒数    ITPUB个人空间8TImM.d2YJ4H
ITPUB个人空间(Yd%eP du
20.一年的第几天     ITPUB个人空间.aal@J uY4X
   select TO_CHAR(SYSDATE,'DDD'),sysdate from dual ITPUB个人空间*yL{Em^Ui
       ITPUB个人空间+s?4D%re uFji
   310 2002-11-6 10:03:51    ITPUB个人空间[ I"A&kUaz v8d

8u(gy,^7T]f2553013621.计算小时,分,秒,毫秒    
b sR6HE:K t25530136    select    
!d8_"l4p'OY"B)`25530136     Days,    
Q:Sz+](m25530136     A,     ITPUB个人空间TP^D H8]{
     TRUNC(A*24) Hours,    
n M%n3h6LM-yx.U mmV25530136     TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,     ITPUB个人空间/b bi%| d
     TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,    
xJ!@'w3O4]$i25530136     TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds     ITPUB个人空间xw ]#D$c
    from    
p9zn)H4ZX$M[?"d25530136    (    
b}J:Y!f:D25530136     select     ITPUB个人空间p;J;m|m\9_BL
     trunc(sysdate) Days,    
@'T%Eh [+y)|w25530136     sysdate - trunc(sysdate) A    
J,GRb y2B25530136     from dual    
n s+L1{ k!A C1{ [25530136   )    ITPUB个人空间'e w(Q(xA Rt

@8}l,TtGSGg1Ml25530136 ITPUB个人空间(?R})I;wF aG
   select * from tabname    
@R3zDkH1`25530136   order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');    
s+s4|zw-h#\$Y1Z25530136  ITPUB个人空间Q~~"@,`+t}? L4e
   //     ITPUB个人空间#r+GJG(E1[z"T
   floor((date2-date1) /365) 作为年    
w7IM6I tZk25530136   floor((date2-date1, 365) /30) 作为月     ITPUB个人空间x2CnGPhZ4x-_
   d(mod(date2-date1, 365), 30)作为日.
g*|3tm\25530136 ITPUB个人空间vi P~(Ai d^^
23.next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日 ITPUB个人空间%h7maJD.a5{?T
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。    
Ajg\k'y25530136   1 2 3 4 5 6 7    
Jp bx!c5@:r+L3A r25530136   日 一 二 三 四 五 六  
6ka8G3_^tS25530136 
7l k5a;qh&q L25530136   ---------------------------------------------------------------
:v S.}d:^;^PQ0};aV*z25530136 
[$IO#jH!Cv,ZN25530136   select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
@d?ABboKb'Kb |25530136   日期 返回的是天 然后 转换为ss
,y|E9qQ }l4H25530136    ITPUB个人空间5Z*}0G1HzJn:zhm$C
24,round[舍入到最接近的日期](day:舍入到最接近的星期日) ITPUB个人空间A U+|adi"i\
   select sysdate S1, ITPUB个人空间/z ?}/Tcs-m:\
   round(sysdate) S2 , ITPUB个人空间1y;Nm fqv
   round(sysdate,'year') YEAR, ITPUB个人空间 B^ v0g#l CWv
   round(sysdate,'month') MONTH , ITPUB个人空间0Zd_6}b
   round(sysdate,'day') DAY from dual
7h {od?-x2i[9w3[e25530136 ITPUB个人空间TKnuC
25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型 ITPUB个人空间 K/R.fB,sj7Yk
   select sysdate S1,                   
*y5C+u)i!e_)Rq[25530136     trunc(sysdate) S2,                 //返回当前日期,无时分秒
1^9J\9[^)E0lAK25530136     trunc(sysdate,'year') YEAR,        //返回当前年的1月1日,无时分秒 ITPUB个人空间6qW"QO ]
     trunc(sysdate,'month') MONTH ,     //返回当前月的1日,无时分秒
+D l7dQ9u1rnL!^25530136     trunc(sysdate,'day') DAY           //返回当前星期的星期天,无时分秒 ITPUB个人空间~G/Jlt,G Rnv
   from dual ITPUB个人空间 srX!r}tiQ'O%T_
ITPUB个人空间9r7W1Q(u R7pu/i
26,返回日期列表中最晚日期 ITPUB个人空间^EP(z*y(w
   select greatest('01-1月-04','04-1月-04','10-2月-04') from dual ITPUB个人空间2t-q'nA{:X
ITPUB个人空间IN6L:oh D
27.计算时间差
8b6A$Yfn'l!|\ ~25530136     注:oracle时间差是以天数为单位,所以换算成年月,日
4bb|c2i.X Rg25530136    ITPUB个人空间0D|LD{Y[
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年
&N-d9Mr)O`25530136      select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月 ITPUB个人空间3r!D?;tqt1cZ
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天
R0K8yy l`I}25530136      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时 ITPUB个人空间(|?sqzU1r
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分
k%[0y)eK aO25530136      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒 ITPUB个人空间#`#gO%e9LJ

H"eIHIk2553013628.更新时间 ITPUB个人空间/DW$VVtk|;|
     注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
+ZPE:V6vp tK25530136     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual        //改变时间-年 ITPUB个人空间RX6kY F5oG
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual                                 //改变时间-月 ITPUB个人空间Sk3t0X%af v{CJ
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual            //改变时间-日
9B5R I#f1Y*v2h*y25530136     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual         //改变时间-时 ITPUB个人空间0IdL.^UX?
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual      //改变时间-分
(DR4lI4dGTq25530136     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual   //改变时间-秒
K k!Ho+v&`-hg25530136
r-t1_q H pN2553013629.查找月的第一天,最后一天 ITPUB个人空间%PXH F)Tr9w3fm,E
     SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month, ITPUB个人空间XwP)S)NR
       Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
bz?%y1TDA25530136       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
-? GOW-x(jW7kg25530136       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
My8t#?t*z25530136   FROM dual;
aa&Z+{w8j c25530136 ITPUB个人空间2_~m&B2d%bo

i.c!Imx4@25530136三. 字符函数(可用于字面字符或 数据库列) ITPUB个人空间)dD$h!r n,},{J
ITPUB个人空间PfKP3D(lD
1，字符串截取
EZy9W9p*U;A(`T9y25530136   select substr('abcdef',1,3) from dual
.r(u@.^f6@O25530136 ITPUB个人空间.Ub&L~;Uu{+?^
2,查找子串位置 ITPUB个人空间*}ZA:W.Juw!Pv;vB
   select instr('abcfdgfdhd','fd') from dual
1F b MQL#U25530136
1m Jo8W$c3I255301363,字符串连接
,E6\*g8uk}25530136   select 'HELLO'||'hello world' from dual;
.^!ZA{` ls$_(N25530136
wA%y a8~4z8B @1j255301364, 1)去掉字符串中的空格 ITPUB个人空间$H&e2xS M;w:c:{9U
    select ltrim(' abc') s1,
\i&u5\;t8k;w25530136    rtrim('zhang ') s2,
BKq3u&uyw8C*s2j3c25530136    trim(' zhang ') s3 from dual
$}CP%Ph(Zz1Lt25530136   2)去掉前导和后缀
^` gA3vj.A5k25530136    select trim(leading 9 from 9998767999) s1,
u8Q+q8_ L0wp25530136    trim(trailing 9 from 9998767999) s2,
)piiFL*[*{j25530136    trim(9 from 9998767999) s3 from dual; ITPUB个人空间 ?N/h3WW;iQSK
  ITPUB个人空间!f4}@ z ~K
5, 返回字符串首字母的Ascii值
XR[ Q^8u"W }25530136   select ascii('a') from dual
3p'r9pY;nDU,@25530136 ITPUB个人空间V&_v L^j4]R
6,返回ascii值对应的字母 ITPUB个人空间}f2k A]4}.K
   select chr(97) from dual ITPUB个人空间s^3i1qQ}b3J |M;n

N+q&`0K O8L$sJ vc255301367,计算字符串长度
i'[bH eD}/@ PC^Ps25530136   select length('abcdef') from dual
GIw;lha^y+s6JL25530136 ITPUB个人空间ij!enC6f~!\
8,initcap（首字母变大写） ,lower(变小写),upper(变大写) ITPUB个人空间p0W/a-eD
   select lower('ABC') s1,
:XE#q1Gi+r'Dh9g i25530136       upper('def') s2, ITPUB个人空间Rjv'R,Bv&@-bb
       initcap('efg') s3 ITPUB个人空间k%T,[.R@r
   from dual; ITPUB个人空间-h2ZF?:~#h+y|

m{7k#Y'_7B(kZ255301369,Replace
b Vj-?Q ]s&yv25530136   select replace('abc','b','xy') from dual;
` qEw r+y ~}25530136
3j4{#|#r:}!XnX2553013610,translate
+Wk7j:e:E4q25530136   select translate('abc','b','xx') from dual; -- x是1位 ITPUB个人空间D ub1t'YL?G{*iB

U3oX$} y4R2553013611,lpad [左添充] rpad [右填充](用于控制输出格式)
m \&A)T8a&Q$A8P25530136   select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
-b|1z#c?7} D{:s25530136   select lpad(dname,14,'=') from dept;
:M7N+d#~9ChP25530136
;p0? n,R!uEMS6Y1O2553013612, decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值 ITPUB个人空间't0t+G"~Yk'g V4V
   select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
uq SxAj25530136   例:
L \I1\D rb25530136   select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0 ITPUB个人空间 `v;Q H,D(JD6l&q
   select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显 ITPUB个人空间U-y`O,h'VX

+C\kOZ!Au25530136示相等
H$H|D2y$v9Y[ _7H25530136
Xd2["U6\Hu(xof2553013613 case[实现switch ..case 逻辑] ITPUB个人空间:ZcN _K ],Sip(T
    SELECT CASE X-FIELD
*OZeZbp5@[;F25530136         WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
+T/K}$k;D;l Pra1[25530136         WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50' ITPUB个人空间8E&_uj+f"~L8H7C
         WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60' ITPUB个人空间:z$Zk1dky;^U
         ELSE 'UNBEKNOWN' ITPUB个人空间l2[ z?.Q
        END ITPUB个人空间1RV e5qn)SCK
   FROM DUAL ITPUB个人空间sY7G^^
 
8ua}6r"lm25530136   注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时，用Decode更为简洁。 ITPUB个人空间8j V0W4gIqj,YX6R
ITPUB个人空间![ X1p ~dH l
四.数字函数 ITPUB个人空间 AB`8}k$d f|xwK8s
1，取整函数（ceil 向上取整,floor 向下取整） ITPUB个人空间a0qWM@ @
   select ceil(66.6) N1,floor(66.6) N2 from dual; ITPUB个人空间8PY;h`(|a
ITPUB个人空间1_4r_2tqW1z\J,p
2, 取幂(power) 和 求平方根(sqrt) ITPUB个人空间-l"Gn0_6SFC
   select power(3,2) N1,sqrt(9) N2 from dual; ITPUB个人空间,TQS3tmlZ]
ITPUB个人空间8`.uIB'm]v
3,求余
%q%_#s@*v;}Tu25530136   select mod(9,5) from dual; ITPUB个人空间'H,HY%~O:]cE
ITPUB个人空间@"{7AP_.H
4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
6E-s0CL:q o25530136   select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
u p Bevwg9yD25530136
S8~'nV2z#h|j255301365,返回值的符号(正数返回为1,负数为-1)
A#|E_d d25530136   select sign(-32),sign(293) from dual;
tg&H?P0E)fN D ~z25530136
:XH/tD$sw"i@25530136五.转换函数
Kv1P c(h,g8iz255301361，to_char()[将日期和数字类型转换成字符类型]
bCT:YnVW25530136   1) select to_char(sysdate) s1,
3W8? P0^3a"@I%bV25530136        to_char(sysdate,'yyyy-mm-dd') s2, ITPUB个人空间Sw;l1R;B)La kR
        to_char(sysdate,'yyyy') s3, ITPUB个人空间|Y+qt0`~VV\ }b6s*C
        to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
KL&r[lK#D25530136        to_char(sysdate, 'hh24:mi:ss') s5, ITPUB个人空间LPS ]_`9q
        to_char(sysdate,'DAY') s6 ITPUB个人空间/HKx"x2lS-d/KsY
    from dual; ITPUB个人空间7~-|(I&S.X)XSk q-r
   2) select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp
`BEmW7? L n@25530136
:^6K1[p;^0\-Ha^x"c255301362, to_date()[将字符类型转换为日期类型] ITPUB个人空间b|rF@t7VClD
    insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
%p)fF4_pju4k `25530136  ITPUB个人空间%uPh+V)e6H@
3, to_number() 转换为数字类型
^!Ig\G25530136    select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
G1\4b {\25530136  ITPUB个人空间r;a_'r&tN
六.其他函数 ITPUB个人空间3n;pT'E3W'WY wY
   1.user: ITPUB个人空间8fZ:y D,jRs
    返回登录的用户名称 ITPUB个人空间i:S}dDbcR
    select user from dual;
(@8Q} c,I'X|.c#IZ25530136  
:fon%h _{7`w F25530136   2.vsize:
,f.M j [,O25530136    返回表达式所需的字节数 ITPUB个人空间9|b6n;bC,Q?;?,c E
    select vsize('HELLO') from dual;
[J$| O\'}~B2_y25530136  ITPUB个人空间jbck$I(l
   3.nvl(ex1,ex2): 　
+^8kq5~*xcW!Y0d25530136    ex1值为空则返回ex2,否则返回该值本身ex1（常用）
-Z&c#pW*ka.GT;? B3k"Ac25530136    例：如果雇员没有佣金，将显示0，否则显示佣金
;CNpTQ7c25530136    select comm,nvl(comm,0) from emp;
\^U6Ym25530136 
3gtA_hOt25530136   4.nullif(ex1,ex2):
7s~)x2t `25530136    值相等返空，否则返回第一个值 ITPUB个人空间1W P0xXjH Mb/{ D;E
    例：如果工资和佣金相等，则显示空，否则显示工资
c+g&]JC xO q V25530136    select nullif(sal,comm),sal,comm from emp;
;~R4P(\sJB6@25530136 
dr"m N/m0q*e25530136   5.coalesce: 　 ITPUB个人空间#}M Y0})yk/l
    返回列表中第一个非空表达式
k6u6`8ITi~A25530136    select comm,sal,coalesce(comm,sal,sal*10) from emp; ITPUB个人空间g{z B xG0Mplc
 
M ^5Y@v O%|25530136   6.nvl2(ex1,ex2,ex3) : ITPUB个人空间P ]D#a^&O-[ wv W9w
    如果ex1不为空，显示ex2,否则显示ex3
1U4d sQ9D| u"T,\vq25530136    如：查看有佣金的雇员姓名以及他们的佣金
`jd(C^"t!Pz25530136    　　select nvl2(comm,ename,') as HaveCommName,comm from emp;
(]?z t:z25530136 
%v\q#Ma1v.|25530136  ITPUB个人空间VA1D\L6Z$A.\pe
七.分组函数 ITPUB个人空间 Y(T Dq[_|
max min avg count sum
${Fqa"F l/e4O-Kdw6X*Q{255301361，整个结果集是一个组
5P2~Ii9{ s%x25530136   1) 求部门30 的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和 ITPUB个人空间Q9}]:Tew-D-B?
     select max(ename),max(sal), ITPUB个人空间0c-Klif\:v!j v
     min(ename),min(sal),
s7q!zDIa p25530136     avg(sal), ITPUB个人空间uZu2yQ M/L@GeqQ
     count(*) ,count(job),count(distinct(job)) ,
-j)r@r"_V-C3}Y*ax25530136     sum(sal) from emp where deptno=30; ITPUB个人空间Rs*dgE0D
2, 带group by 和 having 的分组 ITPUB个人空间 cIqot
   1)按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和 ITPUB个人空间S/Op#z `e^`$J
    select deptno, max(ename),max(sal), ITPUB个人空间8[M y"E}
    min(ename),min(sal), ITPUB个人空间H5Oj0^k*A/s
    avg(sal),
~8e Y!Q1U4sa#T25530136    count(*) ,count(job),count(distinct(job)) , ITPUB个人空间\T0C Y U
    sum(sal) from emp group by deptno;
1A(r#C5S9~e25530136 
5ju PM$Z|%v;h/MN25530136   2)部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
j { ae{8s!]CA{l7QQB25530136    select deptno, max(ename),max(sal), ITPUB个人空间[,\-`*Rrr.Mu
    min(ename),min(sal),
6a lR ^@25530136    avg(sal),
2K9I]t(p1XV*Z JR25530136    count(*) ,count(job),count(distinct(job)) ,
p[ F6_E@s'e25530136    sum(sal) from emp group by deptno having deptno=30;
0ta5V6Kd X2Y1RF25530136  ITPUB个人空间 P Xd@A?k
3, stddev 返回一组值的标准偏差
4m$X xd'q0K0Tv"wrb25530136    select deptno,stddev(sal) from emp group by deptno; ITPUB个人空间+\!Rl p]1EM(C W
    variance 返回一组值的方差差 ITPUB个人空间+c){_}l q"\&h
    select deptno,variance(sal) from emp group by deptno;
p%J4fn{v9l25530136 ITPUB个人空间I f|j T
4, 带有rollup和cube操作符的Group By
'`{2V D YFWI25530136    rollup 按分组的第一个列进行统计和最后的小计
sl3`9W} D?25530136    cube 按分组的所有列的进行统计和最后的小计
.zX7k(p_I K8U25530136    select deptno,job ,sum(sal) from emp group by deptno,job;
&E6B%VOJ[25530136    select deptno,job ,sum(sal) from emp group by rollup(deptno,job);
D&b{7[ pv!?2WX ^25530136    cube 产生组内所有列的统计和最后的小计
7Q7d3cTv9O{25530136    select deptno,job ,sum(sal) from emp group by cube(deptno,job);
BU-P%I,K@s25530136
s1V g)nV#Z25530136八、临时表 ITPUB个人空间J/gL I Q*jQw
   只在会话期间或在事务处理期间存在的表.
1y$d,dE[sh25530136   临时表在插入数据时，动态分配空间 ITPUB个人空间3nC S7QQFc
   create global temporary table temp_dept ITPUB个人空间m1Fy^ x$J"r-S&XT nN
   (dno number,
@'gE-`h4Y25530136   dname varchar2(10))
J^Qk La5x?7zRY25530136   on commit delete rows; ITPUB个人空间Ou;Yg4W
   insert into temp_dept values(10,'ABC'); ITPUB个人空间"q#V.y"wD?g
   commit;
y r|a a g R'W8I25530136   select * from temp_dept; --无数据显示,数据自动清除 ITPUB个人空间ZcyvHtUf;J w
   on commit preserve rows:在会话期间表一直可以存在（保留数据）
A2w\?W)@A9R25530136   on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据） ITPUB个人空间y7U g[-r-b`h

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25530136/viewspace-690345/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25530136/viewspace-690345/