溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
來源于Oracle官方文檔，為了工作中方便使用，這里截取下來供參考：
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.
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 Restriction: You can specify this format only with the |
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 Restriction: You can specify this format only with the |
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 Examples:
|
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 Restriction: You can specify this format only with the |
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 Example: |
TZH | Yes | Time zone hour. (See Example: |
TZM | Yes | Time zone minute. (See Example: |
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 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: |
Y,YYY | Yes | Year with comma in this position. |
YEAR SYEAR | Year, spelled out; | |
YYYY SYYYY | Yes | 4-digit year; |
YYY YY Y | Yes | Last 3, 2, or 1 digit(s) of year. |
我們來逐一測試下:
1、諸如- / , . ; : "text" 這類符號表達字符可以在結果中重現
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顯示公元數值年，Y,YYY 以逗號分隔的年顯示方式，IYYY/IYY/IY/I顯示年后幾位數值，RR/RRRR數值顯示年， B.C.公元前, A.D.公元 SCC/CC 世紀，如果年的后兩個數值是01~99，則世紀值=年的前兩個值加一，否則與年前兩個值相同。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數值顯示月份，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 一周中的天數，DAY 天的名稱，DD 月中的天數，DDD表示年中的天數，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年中的周以當年的第一天算第一周，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兩個參數配置，不同地區顯示的格式不一樣。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、秒數 SS顯示秒數，FF顯示毫秒精確度[1~9]，FM讓返回的值沒有前后空格，FX精確匹配日期格式類型。
SQL> select to_char(SYSTIMESTAMP,'SS.FF3,SSSSS') from dual; TO_CHAR(SYSTIMESTAMP,'SS.FF3,SSSSS') ------------------------------------------------------ 58.180,49438
10、其他 TS短格式顯示時間，TZD白天即使，TZH區域小時，TZM區域分鐘，TZR時間區域信息，X小數點，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、計算時間差
年份差
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;天數差(時*24，分24*60，以此類推)
select floor(to_number(sysdate-to_date('01-31-1999','MM-DD-YYYY'))) as "YEARS" FROM DUAL;
通過EXTRACT()獲取精準的時間
SQL> SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 2:38:40') from dual; EXTRACT(MINUTEFROMTIMESTAMP'2001-02-162:38:40') ----------------------------------------------- 38
通過NEXT_DAY()函數獲取未來時間，這里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
查看有關時間的環境參數設置：
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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
