很实用的时间处理知识点。
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<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
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<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/