Table 2-15 Datetime Format Elements Element Specify in TO_* datetime functions?a Meaning
- / , . ; : "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 No One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'.
D Yes Day of week (1-7).
DAY Yes Name of day, padded with blanks to length of 9 characters.
DD Yes Day of month (1-31).
DDD Yes Day of year (1-366).
DY Yes Abbreviated name of day.
E No Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE No 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 uses the precision specified for the datetime datatype or the datatype's default precision. Examples: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
HH Yes Hour of day (1-12).
HH12 No Hour of day (1-12).
HH24 Yes Hour of day (0-23).
IW No Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I No Last 3, 2, or 1 digit(s) of ISO year.
IYYY No 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; JAN = 01).
MON Yes Abbreviated name of month.
MONTH Yes Name of month, padded with blanks to length of 9 characters.
PM P.M. No Meridian indicator with or without periods.
Q No Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
RM Yes Roman numeral month (I-XII; JAN = I).
RR Yes Given a year with 2 digits:If the year is <50 and the last 2 digits of the current year are >=50, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year. If the year is >=50 and the last 2 digits of the current year are <50, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year. See Also: Table 2-16
RRRR Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year.
SS Yes Second (0-59).
SSSSS Yes Seconds past midnight (0-86399).
TZD Yes Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH Yes Time zone hour. (See TZM format element.) Example: 'HH:MI:SS.FFTZH:TZM'.
TZM Yes Time zone minute. (See TZH format element.) Example: 'HH:MI:SS.FFTZH:TZM'.
TZR Yes Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific
WW No 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 No 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 No Year, spelled out; "S" prefixes BC dates with "-".
YYYY SYYYY Yes 4-digit year; "S" prefixes BC dates with "-".
Element
Specify in TO_* datetime functions?a
Meaning
-
/
,
.
;
:
"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 No
One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'.
D Yes
Day of week (1-7).
DAY Yes
Name of day, padded with blanks to length of 9 characters.
DD Yes
Day of month (1-31).
DDD Yes
Day of year (1-366).
DY Yes
Abbreviated name of day.
E No
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE No
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 uses the precision specified for the datetime datatype or the datatype's default precision. Examples: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
HH Yes
Hour of day (1-12).
HH12 No
Hour of day (1-12).
HH24 Yes
Hour of day (0-23).
IW No
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I No
Last 3, 2, or 1 digit(s) of ISO year.
IYYY No
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; JAN = 01).
MON Yes
Abbreviated name of month.
MONTH Yes
Name of month, padded with blanks to length of 9 characters.
PM
P.M. No
Meridian indicator with or without periods.
Q No
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
RM Yes
Roman numeral month (I-XII; JAN = I).
RR Yes
Given a year with 2 digits:If the year is <50 and the last 2 digits of the current year are >=50, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
If the year is >=50 and the last 2 digits of the current year are <50, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year. See Also: Table 2-16
RRRR Yes
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year.
SS Yes
Second (0-59).
SSSSS Yes
Seconds past midnight (0-86399).
TZD Yes
Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH Yes
Time zone hour. (See TZM format element.) Example: 'HH:MI:SS.FFTZH:TZM'.
TZM Yes
Time zone minute. (See TZH format element.) Example: 'HH:MI:SS.FFTZH:TZM'.
TZR Yes
Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific
WW No
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 No
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 No
Year, spelled out; "S" prefixes BC dates with "-".
YYYY
SYYYY Yes
4-digit year; "S" prefixes BC dates with "-".
YYY
YY
Y Yes
Last 3, 2, or 1 digit(s) of year.
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements4a.htm#48515
bzszp,你说的话等于没说，水太多了。
xmwgp,举个简单例子吧，怎么定义？