ORACLE日期函数文档格式.docx
- 文档编号:20676398
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:16
- 大小:21.79KB
ORACLE日期函数文档格式.docx
《ORACLE日期函数文档格式.docx》由会员分享,可在线阅读,更多相关《ORACLE日期函数文档格式.docx(16页珍藏版)》请在冰豆网上搜索。
PARAMETERVALUE
----------------------------------------------------------------------
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETZHS16GBK
NLS_CALENDARGREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
NLS_SORTBINARY
NLS_TIME_FORMATHH.MI.SSXFFAM
NLS_TIMESTAMP_FORMATDD-MON-RRHH.MI.SSXFFAM
NLS_TIME_TZ_FORMATHH.MI.SSXFFAMTZH:
TZM
NLS_TIMESTAMP_TZ_FORMATDD-MON-RRHH.MI.SSXFFAMTZH:
NLS_DUAL_CURRENCY$
NLS_COMPBINARY
NLS_NCHAR_CHARACTERSETZHS16GBK
NLS_RDBMS_VERSION8.1.7.0.0
ò
ß
V$NLS_PARAMETERS±
select*fromV$NLS_PARAMETERS;
Ó
À
à
Ë
to_date×
·
´
®
Í
×
ª
Î
¦
É
±
Ø
ë
ä
§
Þ
SQL>
selectto_date('
2004-11-1212-07-32'
'
yyyy-mm-ddhh24-mi-ss'
)valuefromdual;
VALUE
-------------------
2004.11.1212:
07:
32
20041015'
2004.10.1500:
00:
00
20041315'
ERRORÎ
1Ð
ORA-01861:
Î
¥
Å
sysdateµ
Ç
°
selectsysdatevaluefromdual;
2003.11.2317:
09:
01
last_day±
Ô
î
ó
selectlast_day(sysdate)valuefromdual;
2003.11.3017:
08:
17
add_months(d,n)È
dº
n¸
ö
selectadd_months(sysdate,2)valuefromdual;
2005.01.2317:
10:
21
next_day(d,day)È
dÖ
Ö
¨
)Ê
selectnext_day(sysdate,1)valuefromdual;
2004.11.2817:
38:
55
³
©
Ý
À
ú
£
¬
¿
ê
õ
Á
ð
¡
oracleÖ
sqlÓ
ï
â
Oracleµ
ø
1¡
Sysdateµ
Selectsysdatefromdual;
SYSDATE
----------
21-6Ô
-05
2¡
Last_day±
Selectlast_day(sysdate)fromdual;
LAST_DAY(S
30-6Ô
3¡
Add_months(d,n)µ
Ù
date_value:
=add_months(date_value,number_of_months)
Selectadd_months(sysdate,2)fromdual;
ADD_MONTHS
21-8Ô
4¡
Months_between(f,s)È
fº
s¼
selectmonths_between(sysdate,to_date('
2005-11-12'
yyyy-mm-dd'
))fromdual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('
YYYY-MM-DD'
))
----------------------------------------------------------
-4.6966741
5¡
NEXT_DAY(d,day_of_week)
"
day_of_week"
ü
d"
¤
÷
SELECTnext_day(to_date('
20050620'
YYYYMMDD'
),1)FROMdual;
NEXT_DAY(T
26-6Ô
6¡
current_date()·
=current_date
columnsessiontimezonefora15
selectsessiontimezone,current_datefromdual;
SESSIONTIMEZONECURRENT_DA
-------------------------
+08:
0013-11Ô
-03
altersessionsettime_zone='
-11:
00'
2/
selectsessiontimezone,current_timestampfromdual;
SESSIONTIMEZONECURRENT_TIMESTAMP
---------------------------------------------------
0012-11Ô
-0304.59.13.668000Ï
-11:
7¡
current_timestamp()Ò
timestampwithtimezoneÊ
selectcurrent_timestampfromdual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
-0510.13.08.220589É
+08:
8¡
dbtimezone()·
selectdbtimezonefromdual;
DBTIME
------
-08:
9¡
extract()Õ
ô
=extract(date_fieldfrom[datetime_value|interval_value])
selectextract(monthfromsysdate)"
ThisMonth"
fromdual;
ThisMonth
6
selectextract(yearfromadd_months(sysdate,36))"
Years"
Years
2008
10¡
localtimestamp()·
selectlocaltimestampfromdual;
LOCALTIMESTAMP
-0510.18.15.855652É
£
Y»
YY»
YYYÄ
Selectto_char(sysdate,¡
YYY¡
)fromdual£
002±
2002Ä
SYEAR»
YEARSYEARÊ
«
SYEAR¡
-1112±
1112Ä
Q¼
3Ô
Q¡
2±
þ
¢
MMÔ
MM¡
12±
12Ô
RMÔ
RM¡
IV±
4Ô
MonthÓ
9¸
Month¡
Mayº
6¸
5Ô
WWµ
WW¡
24±
6Ô
13È
24Ö
W±
W¡
2002Ä
10Ô
1È
1Ö
DDDµ
1Ô
001£
2Ô
032Selectto_char(sysdate,¡
DDD¡
3632002Ä
12Ô
29È
363Ì
DDµ
DD¡
0410Ô
4È
4Ì
DÖ
D¡
52002Ä
14È
DYÖ
DY¡
SUN2002Ä
24È
HH»
HH1212½
HH¡
02Î
2µ
ã
8·
02
HH2424Ð
HH24¡
14Ï
08·
14
MI·
(0¡
59)Selectto_char(sysdate,¡
MI¡
17Ï
4µ
17·
SSÃ
SS¡
2211µ
3·
22Ã
MM¸
(·
MI)¡
MMÊ
ù
selectto_char(add_months(last_day(sysdate),-1),'
yyyy-MM-dd'
)LastDayfrom
dual;
LASTDAY
2005-05-31
selectto_char(add_months(sysdate,-1),'
)PreTodayfromdual;
PRETODAY
2005-05-21
3.É
selectto_char(add_months(last_day(sysdate)+1,-2),'
)firstDayfromdual;
FIRSTDAY
2005-05-01
4.°
selectto_char(sysdate,'
ww'
)fromdualgroupbyto_char(sysdate,'
);
TO
--
25
mm'
06
q'
T
-
2
yyyy'
TO_C
----
2005
8.Ò
å
selectto_char(t.d,'
YY-MM-DD'
)from(
selecttrunc(sysdate,'
MM'
)+rownum-1asd
fromdba_objects
whererownum<
32)t
whereto_char(t.d,'
)=to_char(sysdate,'
)--Õ
andtrim(to_char(t.d,'
Day'
))='
'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
)¸
sysdate-90£
9.oracleÖ
è
altersessionsetnls_date_format='
yyyy-mm-ddhh:
mi:
ss'
;
setserverouton
declare
2DateValuedate;
3begin
4selectsysdateintoDateValuefromdual;
5dbms_output.put_line('
||to_char(DateValue));
6dbms_output.put_line('
1Ì
||to_char(DateValue-1));
7dbms_output.put_line('
1Ð
||to_char(DateValue-1-1/24));
8dbms_output.put_line('
1·
||to_char(DateValue-1-1/24-1/(24*60)));
9dbms_output.put_line('
1Ã
||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
10end;
11/
2003-12-2911:
53:
41
2003-12-2811:
2003-12-2810:
52:
40
PL/SQL¹
OracleÖ
--Ã
Add_Times
--¹
d1Ó
NewTimeÏ
--Ë
NewTimeÖ
--È
2004-12-07
--°
æ
1.0
--×
Kevin
createorreplacefunctionAdd_Times(d1indate,NewTimeindate)returndate
is
hhnumber;
mmnumber;
ssnumber;
hoursnumber;
dResultdate;
begin
--Ï
selectto_number(to_char(NewTime,'
HH24'
))intohhfromdual;
MI'
))intommfromdual;
SS'
))intossfromdual;
--»
hours:
=(hh+(mm/60)+(ss/3600))/24;
--µ
selectd1+hoursintodResultfromdual;
return(dResult);
endAdd_Times;
--²
--selectAdd_Times(sysdate,to_date('
2004-12-0603:
23:
YYYY-MM-DDHH24:
MI:
))fromdual
Oracle9iÖ
OracleDATAÊ
1£
È
round(to_number(end-date-start_date))-Ï
round(to_number(end-date-start_date)*24)-Ï
round(to_number(end-date-start_date)*1440)-Ï
SQ
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 日期 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)