ORACLE日期时间函数
OracleÈÕÆÚº?Êý???õ2009-04-03 10:01:16| ?ÖÀà?º oracle º?Êý ÔÄ?Á2067
ÆÀÂÛ0 ×ÖºÅ?º?óÖÐÐ? ??ÔÄ .
OracleÈÕÆÚº?Êý???õ(Ò?)
Ò??? ??ÓÃÈÕÆÚÊý?Ý?ñÊ?
1.Y?òYY?òYYY ÄêµÄ×îºóÒ?Î???Á?Î??òÈýÎ?
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from dual;
TO_CHAR(SYSDATE,'YYY')
----------------------
007
2.Q ???È 1??3ÔÂΪµÚÒ????È??2?íÊ?µÚ?þ???È??
SQL> Select to_char(sysdate,'Q') from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
2
3.MM ÔÂ?ÝÊý
SQL> Select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
05
4.RM ÔÂ?ݵÄÂÞÂí?íÊ? ??VÔÚÂÞÂíÊý×ÖÖÐ?íÊ? 5??
SQL> Select to_char(sysdate,'RM') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------
V
5.Month ÓÃ9?ö×Ö?û???È?íÊ?µÄÔÂ?ÝÃû
SQL> Select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
5ÔÂ
6.WW µ?ÄêµÚ??ÖÜ??2007Äê5ÔÂ29ÈÕΪ2007ÄêµÚ22ÖÜ??
SQL> Select to_char(sysdate,'WW') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------
22
7.W ??ÔµÚ??ÖÜ ??2007Äê5ÔÂ29ÈÕΪ5ÔµÚ5ÖÜ??
SQL> Select to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'W')
--------------------
5
8.DDD µ?ÄêµÚ??Ìì??2007Äê5ÔÂ29ÈÕΪ2007ÄêµÚ149Ìì??
SQL> Select to_char(sysdate,'DDD') from dual;
TO_CHAR(SYSDATE,'DDD')
----------------------
149
9. DD µ?ÔµÚ??Ìì
SQL> Select to_char(sysdate,'DD') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------
29
10.D ÖÜÄÚµÚ??Ìì
SQL> Select to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'D')
--------------------
3
11.DY ÖÐÎĵÄÐÇÆÚ?? ????2007Äê5ÔÂ29ÈÕΪÐÇÆÚ?þ????
SQL> Select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------
ÐÇÆÚ?þ
12.HH?òHH12 12?øÖÆÐ?Ê?Êý??16?º09?ÖΪÓÃ12Ð?Ê?ÖÆ?ÆÊ?Ϊ4µã??
SQL> Select to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'HH')
---------------------
04
13.HH24 24Ð?Ê?ÖÆ
SQL> Select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
-----------------------
16
OracleÈÕÆÚº?Êý???õ(?þ)
?þ????ÓÃÊ??äº?Êý
1.trunc(sysdate,'Q') ?????ȵÚÒ?Ìì
SQL> select trunc(sysdate,'Q') from dual;
TRUNC(SYSDATE,'Q')
------------------
2007-4-1
2.trunc(sysdate,'D') ??ÖܵĵÚÒ?Ìì(ÖÜÈÕ)
SQL> select trunc(sysdate,'D')from dual;
TRUNC(SYSDATE,'D')
------------------
2007-5-27
3.last_day(sysdate) ??ÔÂ×îºóÒ?Ìì
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2007-5-31 15:20:3
4.add_months(sysdate,2) ÈÕÆÚsysdateºóÍÆ2?öÔÂ
SQL> select add_months(sysdate,2) from dual;
ADD_MONTHS(SYSDATE,2)
---------------------
2007-7-29 15:21:14
5.next_day(sysdate,2)
ÈÕÆÚsysdateÖ?ºóµÄµÚÒ?ÖÜÖÐ,µÚ2(Ö???ÐÇÆڵĵÚ??Ìì)ÊÇÊ?Ã?ÈÕÆÚ
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
-------------------
2007-6-4 15:22:10
6.Months_between(f,s) ÈÕÆÚfºÍs?äÏà?îÔÂÊý
SQL> select
months_between(sysdate,to_date('2007-04-12','yyyy-mm-dd'))from
dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
1.56909908900836
7.µÃµ?SYSDATE+5ËùÔÚµÄÔÂ?Ý
SQL> SELECT to_char(SYSDATE+5,'mon','nls_date_language=american')
FROM dual;
TO_CHAR(SYSDATE+5,'MON','NLS_D
------------------------------
jun
8.current_date()?µ?ص?Ç??á??Ê?ÇøÖеĵ?Ç?ÈÕÆÚ??
9.select dbtimezone from dual;
10.extract()ÕÒ?öÈÕÆÚ?ò?ä?ôÖµµÄ×Ö?ÎÖµ
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
5
SQL> select extract(year from sysdate) "This year" from dual;
This year
----------
2007
SQL> select extract(month from add_months(sysdate,2)) " Month" from
dual;
Month
----------
7
==================================================================
OracleÈÕÆÚº?Êý???õ(Èý)
Èý??Ò?Ð?ʵ?ùºóµÄÓÃ???º
1.ÉÏÔÂÄ?Ìì?º
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual??
2.ÉÏÔÂ?ñÌì
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
3.ÉÏÔÂÊ×Ìì
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
4.ÒªÕÒµ?Ä?ÔÂÖÐËùÓÐÖÜÎåµÄ?ßÌåÈÕÆÚ
SELECT to_char(b.a,'YY-MM-DD')
FROM ( SELECT trunc(SYSDATE,'mm')+ROWNUM-1 a
FROM dba_objects where rownum < 32 ) b
WHERE to_char(b.a,'day')='ÐÇÆÚÎå';
Èç?û?Ñwhere to_char(t.d, 'MM') = to_char(sysdate, 'MM')?Ä?Ésysdate-90????Ϊ?éÕÒµ?Ç?ÔÂ?ݵÄÇ?Èý?öÔÂÖÐ
µÄÃ?ÖÜÎåµÄÈÕÆÚ??
5.µÃµ?ϵÍ?µ?Ç?ÔÂ??ÒÔºóµÄÈÕÆÚ
select trunc(sysdate, 'MM')+ROWNUM-1 FROM dba_objects ; -----------------------------------
to_date ×Ö?û??ÀàÐÍתΪ??ÈÕÆÚÀàÐÍ
×Ö?û??ÖеÄÏàÓ?Î?ÖÃÉϵÄ×Ö?û,?ØÐë?ûºÏÊ??ä??Î?µÄÏÞÖÆ
14.MI ?ÖÖÓÊý(0??59)
ÌáÊ?×?Òâ??Òª??MM?ñÊ?ÓÃÓÚ?ÖÖÓ(?ÖÖÓÓ??ÃÊ?ÓÃMI)??MMÊÇÓÃÓÚÔÂ?ݵÄ?ñÊ?????ËüÓÃÓÚ?ÖÖÓÒ?ÄÜ??×???µ??á?ûÊÇ?íÎóµÄ??
15.SS ÃëÊý(0??59)
×Ü?á?ºoracleÊ??ä?Ù×?ÓÃ?Ó?õ???Í?ÉÒÔ???ù??µ?Î?Ϊ1Ìì??20Ð?Ê?ÒÔºóµÄÊ??äÓ??ÃÔõÑù?ÆËã?
20Ð?Ê?ÒªÕÛËãµ??ÖÖÓ,?ÍÊÇ1/1440,1Ð?Ê??ÍÊÇ1/24,20Ð?Ê?Ò??ÉÒÔÓÃ20/24?òÕß1200/1400,120/144À??íÊ???select to_date( '2007-9-20 9:52:15 ', 'yyyy-mm-dd hh24:mi:ss ')+20/24 from dual;Õâ?ö?ÍÊÇ?Ó20Ð?Ê?ÁË