DATETIME FORMATE（时间格式）

Yes

Punctuation and quoted text is reproduced in the result.

Yes

AD indicator with or without periods.

Yes

Meridian indicator with or without periods.

Yes

BC indicator with or without periods.

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.

Yes

Day of week (1-7). This element depends on the NLS territory of the session.

Yes

Name of day.

Yes

Day of month (1-31).

Yes

Day of year (1-366).

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.

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.

Yes

Abbreviated name of day.

Yes

Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).

Yes

Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).

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;

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

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

Yes

Hour of day (1-12).

Yes

Hour of day (0-23).

Week of year (1-52 or 1-53) based on the ISO standard.

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

4-digit year based on the ISO standard.

Yes

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

Yes

Minute (0-59).

Yes

Month (01-12; January = 01).

Yes

Abbreviated name of month.

Yes

Name of month.

Yes

Meridian indicator with or without periods.

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

Yes

Roman numeral month (I-XII; January = I).

Yes

Lets you store 20th century dates in the 21st century using only two digits.

See Also: "The RR Datetime Format Element"

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.

Yes

Second (0-59).

Yes

Seconds past midnight (0-86399).

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.

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).

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'.

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'.

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

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.

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

Yes

Local radix character.

Example: 'HH:MI:SSXFF'.

Yes

Year with comma in this position.

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

Yes

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

Yes

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