Shulou(Shulou.com)06/01 Report--

It comes from the official documentation of Oracle. For ease of use at work, it is intercepted here for reference:

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.

ADA.D.

Yes

AD indicator with or without periods.

AMA.M.

Yes

Meridian indicator with or without periods.

BCB.C.

Yes

BC indicator with or without periods.

CCSCC

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

HHHH12

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.

IYYIYI

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.

PMP.M.

Yes

Meridian indicator with or without periods.

Q

Quarter of year (1,2,3,4; January-March = 1).

RM

Yes

Roman numeral month (ImurXII; 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.

YEARSYEAR

Year, spelled out; S prefixes BC dates with a minus sign (-).

YYYYSYYYY

Yes

4-digit year; S prefixes BC dates with a minus sign.

YYYYYY

Yes

Last 3,2, or 1 digit (s) of year.

Let's test it one by one:

1. Such as-/. Symbolic expressive characters such as "text" can be reproduced in the result

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

2. Year display, SYEAR/YEAR character shows year, SYYYY/YYYY shows year, YMagi YYY displays year separated by comma, IYYY/IYY/IY/I shows several digits after year, RR/RRRR value shows year, B.C. B.C., A.D. In the SCC/CC century, if the last two values of the year are 0199, the century value = the first two values of the year plus one, otherwise it is the same as the first two values of the year. AM (A.M.) / PM (P.M.) Show morning or afternoon

SQL > select to_char (sysdate,'SYEAR,YEAR') from dual TO_CHAR (SYSDATE,'SYEAR,YEAR')-TWENTY FIFTEEN,TWENTY FIFTEENSQL > select to_char (sysdate,'SYYYY,YYYY,Y,YYY,IYYY,IYY,IY,I:RR,RRRR:B.C. A.D., Journal SCC, CC, AMJournal, 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, and 2. 015,2015,015,15,5:15,2015:A.D.,A.D., 21,21,PM,P.M.

3. Month display, MM value shows month, MON character shows month, MON full character shows month, RM Roman alphabet shows month.

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

4. The display of days, the number of days in D week, the name of DAY days, the number of days in DD month, DDD represents the number of days in the year, and DY is displayed in an abbreviated week.

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

5. The week shows that the week of the IW year (1-53), the week of the WW year is the first week of the year, the week of the W month, and the first week of each month.

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

6. Time format DL is displayed in long-time format, while DS is displayed in short-time format, which depends on the configuration of NLS_TERRITORY and NLS_LANGUAGE parameters. The display format varies in different regions. Ether EE shows the age in acronym and full name respectively.

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

7. The hour HH/HH12 is displayed in 12 hours and the HH24 is displayed in 24 hours.

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

8. Minute MI

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

9. Number of seconds SS displays the number of seconds, FF displays millisecond accuracy [1: 9], FM makes the returned value have no spaces before and after, and FX accurately matches the date format type.

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

10. Other TS short format display time, TZD daytime even, TZH area hours, TZM area minutes, TZR time area information, X decimal point, Q quarter.

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:00SQL > select to_char (systimestamp,'XQ') from dual TO_CHA-.1

11. Calculate the time difference

Year difference

Select floor (to_number (sysdate-to_date ('01-31-1999)) / 365) as "YEARS" FROM DUAL

Monthly difference

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

Day difference (24 hours, 24-60 minutes, and so on)

Select floor (to_number (sysdate-to_date ('01-31-1999) as "YEARS" FROM DUAL

Get the exact time through EXTRACT ()

SQL > SELECT EXTRACT (MINUTEFROMTIMESTAMP'2001-02-162) from dual;EXTRACT (MINUTEFROMTIMESTAMP'2001-02-162))-- 38

Obtain the future time through the NEXT_DAY () function, where 1-7 represents from the day, one, two, three, four, five, and six, respectively.

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

View the environment parameter settings for the time:

SQL > col PARAMETER for a30SQL > col VALUE for a40SQL > select * from nls_session_parameters PARAMETER VALUE-- NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS. NLS_CALENDAR GREGORIANNLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SSNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMPARAMETER VALUE- -NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.