ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle時間處理大全(轉)

oracle時間處理大全(轉)

原创 Linux操作系统 作者:ma_vicky 时间:2011-03-24 11:29:03 0 删除 编辑
很实用的时间处理知识点。
G vz(d2N1|TV ]zd25530136ITPUB个人空间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:45ITPUB个人空间_'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 EightITPUB个人空间_,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:2008ITPUB个人空间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:fridayITPUB个人空间B)G4D.TS"n
   SQL:select to_char(to_date('2008-2-29','yyyy-mm-dd'),'dd') from dual;RESULT:29ITPUB个人空间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_objectsITPUB个人空间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')+1ITPUB个人空间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
   21ITPUB个人空间#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.90322580645161ITPUB个人空间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 7ITPUB个人空间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:366ITPUB个人空间-S%i%Q2E1V`e#t3V1r
12、 yyyy与rrrr的区别ITPUB个人空间8fV$fwu
   yyyy 99 0099
p~eU6PIk25530136   rrrr 99 1999ITPUB个人空间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-29ITPUB个人空间%k8fST-QpZG#tnG
14、add_months()用于从一个日期值增加或减少一些月份ITPUB个人空间 ].@jUu.N.o\
   SQL:select add_months(sysdate,12) from dual;                      RESULT:2009-2-28 11:23:50ITPUB个人空间i'R/F8Jr!\ ~pl
15、extract()找出日期或间隔值的字段值ITPUB个人空间V+z+bK[*p"iw3F~
   SQL:select extract(month from sysdate) from dual; RESULT:2ITPUB个人空间'`0{-ZAe;o?'Eo r
   SQL:select extract(year from add_months(sysdate,36)) from dual; RESULT:2011ITPUB个人空间hH;l(h5G{U)P
16、last_day()返回包含了日期参数的月份的最后一天的日期
-C^ }8GHR'\$Cf25530136   SQL:select last_day(sysdate) from dual;                                         RESULT:2008-2-29ITPUB个人空间 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  twelfthITPUB个人空间6DjG4}MZI d
  Month:  ITPUB个人空间Z*I0h+~ j*un%[
  mm    number     03ITPUB个人空间Fo[5v1]OP
  mon   abbreviated  marITPUB个人空间3~ sf3C+Db7?$b({ o~
  month  spelled out  march ITPUB个人空间d t9K4J,K3HZu\
  Year:             ITPUB个人空间7a VO?;q:RK4i
  yy    two digits  98ITPUB个人空间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-TwoITPUB个人空间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   UNIONITPUB个人空间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 = AmericanITPUB个人空间D'@|-I%Ha
     alter session set NLS_DATE_LANGUAGE = AmericanITPUB个人空间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_parametersITPUB个人空间.Gq+J Z.P[~3e'^,P
  select * from V$NLS_PARAMETERSITPUB个人空间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 130ITPUB个人空间 pWPC UY~P
   sql> select * from nls_session_parameters;ITPUB个人空间w+Rc`Mz@7]'X
   NLS_LANGUAGE          AMERICANITPUB个人空间(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          GREGORIANITPUB个人空间2b2C*~;PU j`9L,Y
   NLS_DATE_FORMAT        DD-MON-RR
V)PD s%p Z_25530136   NLS_DATE_LANGUAGE       AMERICANITPUB个人空间9\ A ||uf v3_%d{{
   NLS_SORT            BINARY
,y%}Xq![25530136   NLS_TIME_FORMAT        HH.MI.SSXFF AMITPUB个人空间-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:TZMITPUB个人空间x!Cq]+G/Q6Y"U(t
   NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZH:TZMITPUB个人空间3RSLL6K'e#c\
   NLS_DUAL_CURRENCY       $ITPUB个人空间I8M!z0a)a9WC(m%Q GK
   NLS_COMP            BINARY
{i?'`6@ v y1y25530136ITPUB个人空间(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_objectsITPUB个人空间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 offerITPUB个人空间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 19ITPUB个人空间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       ) daysITPUB个人空间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_dateITPUB个人空间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_CITPUB个人空间$FV J i5M"@$y$[-_
  ------- ----
.L!{&f(PN^(g*C6l25530136  yyyy 99 0099
Hv hI;H25530136  rrrr 99 1999ITPUB个人空间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') ,sysdateITPUB个人空间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 dualITPUB个人空间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
  selectITPUB个人空间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  fromITPUB个人空间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             TIME2ITPUB个人空间`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 numberITPUB个人空间5mW&E/L[N
asITPUB个人空间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_cntITPUB个人空间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;OITPUB个人空间N_wB a&ku
ITPUB个人空间o"g'n:mBC

"Da#?.b1e;u8l+E25530136
+m3_5yK;L7|Z.f25530136
,[$e,R2G1RMo25530136
9t!s#H{:Jg$^{25530136ITPUB个人空间%^7];~qLoq$c6A5ID
ORACLE日期时间函数大全
7\1nBlgR25530136ITPUB个人空间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 两位年                显示值:07ITPUB个人空间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         当月第几天        显示值:02ITPUB个人空间}:N-@3CFlci
        ddd    number         当年第几天        显示值:02ITPUB个人空间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小时进制            显示值:01ITPUB个人空间X9\6YN"km^ \^
              hh24 two digits 24小时进制            显示值:13ITPUB个人空间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进制                显示值:25ITPUB个人空间\1Th;nQ"e
            ITPUB个人空间J1Iu&H e+l
              其它ITPUB个人空间:[i^@.L%A
              Q     digit         季度                  显示值:4
;z-J q6g u:R@ J25530136              WW    digit         当年第几周            显示值:44ITPUB个人空间,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,D25530136ITPUB个人空间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:}25530136ITPUB个人空间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       ) daysITPUB个人空间!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 empITPUB个人空间,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_25530136ITPUB个人空间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 dualITPUB个人空间*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,TtGSGg1Ml25530136ITPUB个人空间(?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\25530136ITPUB个人空间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[e25530136ITPUB个人空间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 dualITPUB个人空间 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 dualITPUB个人空间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 c25530136ITPUB个人空间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@O25530136ITPUB个人空间.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,@25530136ITPUB个人空间V&_v L^j4]R
6,返回ascii值对应的字母ITPUB个人空间}f2k A]4}.K
   select chr(97) from dualITPUB个人空间s^3i1qQ}b3J |M;n

N+q&`0K O8L$sJ vc255301367,计算字符串长度
i'[bH eD}/@ PC^Ps25530136   select length('abcdef') from dual
GIw;lha^y+s6JL25530136ITPUB个人空间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') s3ITPUB个人空间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;其它取0ITPUB个人空间 `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
        ENDITPUB个人空间1RV e5qn)SCK
   FROM DUALITPUB个人空间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') s6ITPUB个人空间/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{v9l25530136ITPUB个人空间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_deptITPUB个人空间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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 常用sql(轉)
请登录后发表评论 登录
全部评论

注册时间:2011-03-14

  • 博文量
    7
  • 访问量
    5274