DATETIMEFORMATE（時間格式）

來源于Oracle官方文檔，為了工作中方便使用，這里截取下來供參考：

創(chuàng)新互聯(lián)擁有一支富有激情的企業(yè)網(wǎng)站制作團(tuán)隊(duì)，在互聯(lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)深耕十載，專業(yè)且經(jīng)驗(yàn)豐富。十載網(wǎng)站優(yōu)化營銷經(jīng)驗(yàn)，我們已為上1000家中小企業(yè)提供了網(wǎng)站制作、網(wǎng)站建設(shè)解決方案，定制制作，設(shè)計(jì)滿意，售后服務(wù)無憂。所有客戶皆提供一年免費(fèi)網(wǎng)站維護(hù)!

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#i34924

Table 3-15 Datetime Format Elements

Element	TO_* datetime functions?	Description
- / , . ; : "text"	Yes	Punctuation and quoted text is reproduced in the result.
AD A.D.	Yes	AD indicator with or without periods.
AM A.M.	Yes	Meridian indicator with or without periods.
BC B.C.	Yes	BC indicator with or without periods.
CC SCC		Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. For example, 2002 returns 21; 2000 returns 20.
D	Yes	Day of week (1-7). This element depends on the NLS territory of the session.
DAY	Yes	Name of day.
DD	Yes	Day of month (1-31).
DDD	Yes	Day of year (1-366).
DL	Yes	Returns a value in the long date format, which is an extension of the Oracle Database `DATE` format, determined by the current value of the `NLS_DATE_FORMAT` parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the `NLS_TERRITORY` and `NLS_LANGUAGE` parameters. For example, in the `AMERICAN_AMERICA` locale, this is equivalent to specifying the format `'fmDay,` `Month` `dd,` `yyyy'`. In the `GERMAN_GERMANY` locale, it is equivalent to specifying the format '`fmDay, dd.` `Month yyyy`'. Restriction: You can specify this format only with the `TS` element, separated by white space.
DS	Yes	Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the `NLS_TERRITORY` and `NLS_LANGUAGE` parameters. For example, in the `AMERICAN_AMERICA` locale, this is equivalent to specifying the format '`MM/DD/RRRR`'. In the `ENGLISH_UNITED_KINGDOM` locale, it is equivalent to specifying the format '`DD/MM/RRRR`'. Restriction: You can specify this format only with the `TS` element, separated by white space.
DY	Yes	Abbreviated name of day.
E	Yes	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE	Yes	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]	Yes	Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in `DATE` formats. Examples: `'HH:MI:SS.FF'` `SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;`
FM	Yes	Returns a value with no leading or trailing blanks. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX	Yes	Requires exact matching between the character data and the format model. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH HH12	Yes	Hour of day (1-12).
HH24	Yes	Hour of day (0-23).
IW		Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I		Last 3, 2, or 1 digit(s) of ISO year.
IYYY		4-digit year based on the ISO standard.
J	Yes	Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI	Yes	Minute (0-59).
MM	Yes	Month (01-12; January = 01).
MON	Yes	Abbreviated name of month.
MONTH	Yes	Name of month.
PM P.M.	Yes	Meridian indicator with or without periods.
Q		Quarter of year (1, 2, 3, 4; January - March = 1).
RM	Yes	Roman numeral month (I-XII; January = I).
RR	Yes	Lets you store 20th century dates in the 21st century using only two digits. See Also: "The RR Datetime Format Element"
RRRR	Yes	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS	Yes	Second (0-59).
SSSSS	Yes	Seconds past midnight (0-86399).
TS	Yes	Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the `NLS_TERRITORY` and `NLS_LANGUAGE` initialization parameters. Restriction: You can specify this format only with the `DL` or `DS` element, separated by white space.
TZD	Yes	Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in `DATE` formats. Example: `PST` (for US/Pacific standard time); `PDT` (for US/Pacific daylight time).
TZH	Yes	Time zone hour. (See `TZM` format element.) Valid in timestamp and interval formats, but not in `DATE` formats. Example: `'HH:MI:SS.FFTZH:TZM'`.
TZM	Yes	Time zone minute. (See `TZH` format element.) Valid in timestamp and interval formats, but not in `DATE` formats. Example: `'HH:MI:SS.FFTZH:TZM'`.
TZR	Yes	Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in `DATE` formats. Example: US/Pacific
WW		Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W		Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X	Yes	Local radix character. Example: `'HH:MI:SSXFF'`.
Y,YYY	Yes	Year with comma in this position.
YEAR SYEAR		Year, spelled out; `S` prefixes BC dates with a minus sign (-).
YYYY SYYYY	Yes	4-digit year; `S` prefixes BC dates with a minus sign.
YYY YY Y	Yes	Last 3, 2, or 1 digit(s) of year.

我們來逐一測試下:

1、諸如- / , . ; : "text" 這類符號表達(dá)字符可以在結(jié)果中重現(xiàn)

SQL> select to_char(sysdate,'yyyy"year"mm-dd;') from dual;
TO_CHAR(SYSDATE,'YYYY"YEAR"MM-DD;')
------------------------------------------
2015year02-24;

2、年的顯示，SYEAR/YEAR字符顯示年，SYYYY/YYYY顯示公元數(shù)值年，Y,YYY 以逗號分隔的年顯示方式，IYYY/IYY/IY/I顯示年后幾位數(shù)值，RR/RRRR數(shù)值顯示年， B.C.公元前, A.D.公元 SCC/CC 世紀(jì)，如果年的后兩個數(shù)值是01~99，則世紀(jì)值=年的前兩個值加一，否則與年前兩個值相同。AM(A.M.)/PM(P.M.)顯示上午還是下午

SQL> select to_char(sysdate,'SYEAR,YEAR') from dual;
TO_CHAR(SYSDATE,'SYEAR,YEAR')
--------------------------------------------------------------------------------
 TWENTY FIFTEEN,TWENTY FIFTEEN
SQL> select to_char(sysdate,'SYYYY,YYYY,Y,YYY,IYYY,IYY,IY,I:RR,RRRR:B.C.,A.D.,SCC,CC,AM,P.M.') from dual;
TO_CHAR(SYSDATE,'SYYYY,YYYY,Y,YYY,IYYY,IYY,IY,I:RR,RRRR:B.C.,A.D.,SCC,CC,AM,P.M.
--------------------------------------------------------------------------------
 2015,2015,2,015,2015,015,15,5:15,2015:A.D.,A.D., 21,21,PM,P.M.

3、月的顯示, MM數(shù)值顯示月份，MON字符顯示月份，MON完整字符顯示月份，RM羅馬字母顯示月份。

SQL> select to_char(sysdate,'MM,MON,MONTH,RM') from dual;
TO_CHAR(SYSDATE,'MM,MON,MONTH,RM')
--------------------------------------------------------------------------------
02,FEB,FEBRUARY ,II

4、天的顯示，D 一周中的天數(shù)，DAY 天的名稱，DD 月中的天數(shù)，DDD表示年中的天數(shù)，DY以縮寫的星期顯示。

SQL> select to_char(sysdate,'D,DAY,DD,DDD,DY') from dual;
TO_CHAR(SYSDATE,'D,DAY,DD,DDD,DY')
--------------------------------------------------------------------------------
3,TUESDAY  ,24,055,TUE

5、周顯示，IW年中的周(1-53),WW年中的周以當(dāng)年的第一天算第一周，W月中的周，以第每個月頭一天算第一周。

SQL> select to_char(sysdate,'IW,WW,W') from dual;
TO_CHAR(SYSDATE,'IW,W
---------------------
09,08,4

6、時間格式 DL以長時間格式顯示，DS以短時間格式顯示，這兩者取決于NLS_TERRITORY和NLS_LANGUAGE兩個參數(shù)配置，不同地區(qū)顯示的格式不一樣。E,EE分別以縮寫和全稱的方式顯示時代。

SQL> select to_char(sysdate,'DL:DS') from dual;
TO_CHAR(SYSDATE,'DL:DS')
--------------------------------------------------------------------------------
Tuesday, February 24, 2015:2/24/2015

7、小時 HH/HH12以12小時顯示，HH24以24小時顯示。

SQL> select to_char(SYSTIMESTAMP,'HH,HH12,HH24') from dual;
TO_CHAR(SYSTIMESTAMP,'HH
------------------------
01,01,13

8、分鐘MI

SQL> select to_char(systimestamp,'MI') from dual;
TO_CHA
------
13

9、秒數(shù) SS顯示秒數(shù)，F(xiàn)F顯示毫秒精確度[1~9]，F(xiàn)M讓返回的值沒有前后空格，F(xiàn)X精確匹配日期格式類型。

SQL> select to_char(SYSTIMESTAMP,'SS.FF3,SSSSS') from dual;
TO_CHAR(SYSTIMESTAMP,'SS.FF3,SSSSS')
------------------------------------------------------
58.180,49438

10、其他 TS短格式顯示時間，TZD白天即使，TZH區(qū)域小時，TZM區(qū)域分鐘，TZR時間區(qū)域信息，X小數(shù)點(diǎn)，Q季度。

SQL> select to_char(systimestamp,'TS,TZD,TZH,TZM,TZR') from dual;
TO_CHAR(SYSTIMESTAMP,'TS,TZD,TZH,TZM,TZR')
--------------------------------------------------------------------------------
2:09:27 PM,,+08,00,+08:00
SQL> select to_char(systimestamp,'XQ') from dual;
TO_CHA
------
.1

11、計(jì)算時間差

年份差

select floor(to_number(sysdate-to_date('01-31-1999','MM-DD-YYYY'))/365) as "YEARS" FROM DUAL;

月份差

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 
select ceil(months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')))"MONTHS" FROM DUAL;

天數(shù)差(時*24，分24*60，以此類推)

select floor(to_number(sysdate-to_date('01-31-1999','MM-DD-YYYY'))) as "YEARS" FROM DUAL;

通過EXTRACT()獲取精準(zhǔn)的時間

SQL> SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 2:38:40') from dual;
EXTRACT(MINUTEFROMTIMESTAMP'2001-02-162:38:40')
-----------------------------------------------
                                             38

通過NEXT_DAY()函數(shù)獲取未來時間，這里1-7分別表示從日、一、二、三、四、五、六

SQL> select sysdate,next_day(sysdate,6) from dual;
SYSDATE             NEXT_DAY(SYSDATE,6)
------------------- -------------------
2015-03-09 12:44:02 2015-03-13 12:44:02

查看有關(guān)時間的環(huán)境參數(shù)設(shè)置：

SQL> col PARAMETER for a30
SQL> col VALUE for a40
SQL> select * from nls_session_parameters;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

網(wǎng)頁名稱：DATETIMEFORMATE（時間格式）
網(wǎng)址分享：http://muchs.cn/article42/ijccec.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、標(biāo)簽優(yōu)化、網(wǎng)站制作、App設(shè)計(jì)、品牌網(wǎng)站建設(shè)、營銷型網(wǎng)站建設(shè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容