溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

DATETIME FORMATE（時(shí)間格式）

發(fā)布時(shí)間：2020-08-01 14:33:10 來(lái)源：網(wǎng)絡(luò) 閱讀：496 作者：onlinekof2001 欄目：關(guān)系型數(shù)據(jù)庫(kù)

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

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

Table 3-15 Datetime Format Elements

ElementTO_* 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.

 我們來(lái)逐一測(cè)試下:

1、諸如- / , . ; : "text"  這類(lèi)符號(hào)表達(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 以逗號(hào)分隔的年顯示方式，IYYY/IYY/IY/I顯示年后幾位數(shù)值，RR/RRRR數(shù)值顯示年， B.C.公元前, A.D.公元 SCC/CC 世紀(jì)，如果年的后兩個(gè)數(shù)值是01~99，則世紀(jì)值=年的前兩個(gè)值加一，否則與年前兩個(gè)值相同。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 天的名稱(chēng)，DD 月中的天數(shù)，DDD表示年中的天數(shù)，DY以縮寫(xiě)的星期顯示。

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月中的周，以第每個(gè)月頭一天算第一周。

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

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

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

7、小時(shí) HH/HH12以12小時(shí)顯示，HH24以24小時(shí)顯示。

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讓返回的值沒(méi)有前后空格，F(xiàn)X精確匹配日期格式類(lèi)型。

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

10、其他 TS短格式顯示時(shí)間，TZD白天即使，TZH區(qū)域小時(shí)，TZM區(qū)域分鐘，TZR時(shí)間區(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ì)算時(shí)間差

年份差

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ù)差(時(shí)*24，分24*60，以此類(lèi)推)

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

 

通過(guò)EXTRACT()獲取精準(zhǔn)的時(shí)間

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

通過(guò)NEXT_DAY()函數(shù)獲取未來(lái)時(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)時(shí)間的環(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

 

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手