Table B-3. Date format elements
Format element |
Function |
|---|
-/,.;: |
Punctuation may be included anywhere in the date format string, and
will be included in the output. |
'text' |
Quoted text may also be included in the date format string, and will
be reproduced in the output. |
AD or A.D.BC or B.C. |
Includes an AD or BC indicator with the date. |
AM or A.M.PM or P.M. |
Prints AM or PM, whichever applies, given the time in question. |
CC |
Century number. This is 20 for years 1900 through 1999. |
D |
Number of the day of the week. This is 1 through 7. |
DAY |
Name of the day. This is Saturday, Sunday, Monday, etc. |
DD |
Day of the month. |
DDD |
Day of the year. |
DL |
Returns a date in a long-date format, which
depends on NLS_TERRITORY and NLS_LANGUAGE. May be combined only with
TS. |
DS |
Returns a date in a short-date format, which
depends on NLS_TERRITORY and NLS_LANGUAGE. May be combined only with
TS. |
DY |
Abbreviation for name of the day. This is Sat, Sun, Mon, and so forth. |
E |
Abbreviation for era name. Valid only for the following calendars:
Japanese Imperial, ROC Official, and Thai Buddha. Input only. |
EE |
Full era name. |
FF FF1..FF9 |
Fractional seconds. Valid only when used with TIMESTAMP values. Prior
to Oracle Database 10 g , use FF (two Fs)
regardless of the number of decimal digits you wish to see or use.
Any other number of Fs is invalid. Beginning with Oracle Database
10 g , you may use FF1..FF9 to specify the number
of fractional digits you desire. |
FM |
Element that toggles suppression of blanks in output from conversion.
(FM stands for Fill Mode.) |
FX |
Element that requires exact pattern matching between data and format
model. (FX stands for Format eXact.) |
HH |
Hour of the day. This is 1-12. |
HH12 |
Hour of the day. This is 1-12, the same as HH. |
HH24 |
Hour of the day on a 24-hour clock. This is 0-23. |
I |
Last digit of the ISO standard year. Output only. |
IW |
Week in the year, from 1-52 or 1-53, based on the ISO standard.
Output-only. |
IY |
Last two digits of the ISO standard year. Output only. |
IYY |
Last three digits of the ISO standard year. Output only. |
IYYY |
Four-digit ISO standard year. Output only. |
J |
Julian day. Day 1 is equivalent to Jan 1, 4712 BC. |
MI |
Minute. |
MM |
Month number. |
MON |
Three-letter month abbreviation. |
MONTH |
Month name, fully spelled out. |
Q |
Quarter of the year. Quarter 1 is Jan-Mar, quarter 2 is Apr-Jun, and
so forth. |
RM |
Month number in Roman numerals. |
RR |
When used with TO_CHAR, returns the last two digits of the year. |
RRRR |
When used with TO_CHAR, returns the four-digit year. |
SCC |
Same as CC, but BC dates will be negative. |
SP |
Suffix that converts a number to its spelled format. This element can
appear at the end of any element that results in a number. For
example, a mask such as
"DDth-Mon-Yyyysp" results in output
such as "15th-Nov-One Thousand Nine Hundred
Sixty-One." The return value is in English,
regardless of the date language. (Yyyy resulted in mixed-case words). |
SPTH |
Suffix that converts a number to its spelled and ordinal format; for
example, 4 becomes FOURTH and 1 becomes FIRST. This element can
appear at the end of any element that results in a number. For
example, a mask such as "Ddth Mon,
Yyyysp" results in output such as "
Fifteenth Nov, One Thousand Nine Hundred Sixty-One."
The return value is in English, regardless of the date language. |
SS |
Second. |
SSSSS |
Number of seconds since midnight. |
SYEAR |
Year spelled out in words, with a leading negative sign when the year
is BC. |
SYYYY |
Four-digit year, with a leading negative sign when the year is BC. |
TH |
Suffix that converts a number to its ordinal format; for example, 4
becomes 4th and 1 becomes 1st. This element can appear at the end of
any element that results in a number. For example,
"DDth-Mon-YYYY' results in output
such as "15th-Nov-1961." The return
value is in English, regardless of the date language. |
TS |
Returns a time in a short-time format, which
depends on NLS_TERRITORY and NLS_LANGUAGE. May be combined with only
DL or DS. |
TZD |
Abbreviated time zone name; for example: EST, PST, etc. This is an
input-only format, which may seem odd at first. |
TZH |
Time zone hour displacement. For example, -5 indicates a time zone
five hours earlier than UTC. |
TZM |
Time zone minute displacement. For example -5:30 indicates a time
zone that is five hours, thirty-minutes earlier than UTC. A few such
time zones do exist. |
TZR |
Time zone region. For example,
"US/Eastern" is the region in which
EST (Eastern Standard Time) and EDT (Eastern Daylight Time) are
valid. |
W |
Week of the month. Week one starts on the first of the month. Week
two starts on the 8th of the month, and so forth. |
WW |
Week of the year. |
X |
Local radix character. In American-English, this is a period (.).
This element can be placed in front of FF, so that fractional seconds
are properly interpreted and represented. |
Y |
Last digit of the year number. |
Y,YYY |
Four-digit year with a comma after the first digit. |
YEAR |
Year spelled out in words. |
YY |
Last two digits of the year number. |
YYY |
Last three digits of the year number. |
YYYY |
Four-digit year. |
• Table of Contents • Index • Reviews • Reader Reviews • Errata • Academic Oracle SQL*Plus: The Definitive Guide, 2nd Edition By
Jonathan Gennick Publisher : O''Reilly Pub Date : November 2004 ISBN : 0-596-00746-9 Pages : 582
Updated for Oracle 10g, this bestselling book is the only
in-depth guide to SQL*Plus. It clearly describes how to
perform, step-by-step, all of the tasks that Oracle
developers and DBAs want to perform with SQL*Plus--and maybe
some you didn''t realize you could perform. If you want to
leverage of the full power and flexibility of this popular
Oracle tool, this book is an indispensable resource.
لطفا منتظر باشید ...
