Section B.3. Formatting Dates

B 3 Formatting Dates

SQL*Plus doesn't format dates at all. If you are selecting a date column from the database, you must use Oracle's built-in TO_CHAR function to convert the date to a character string, formatting it the way you want it. As far as SQL*Plus is concerned , that makes it another character column. Table B-3 shows the date format elements that can be used with the TO_CHAR function.

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.

 

The one SQL*Plus command that does recognize these date format elements is the ACCEPT command. When you ask the user to enter a date, you can provide a date format specification. SQL*Plus rejects any date the user enters that doesn't match that format.

When displaying a date, you can use the TO_CHAR function to specify the format. The following example displays the current value of SYSDATE, including the time:

SQL>

SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy hh:mi:ss PM')

2

FROM dual;

TO_CHAR(SYSDATE,'DD-MON

-----------------------

13-Dec-1998 09:13:59 PM

 

When you use a date format element that displays a text value, such as the name of a month, you need to pay attention to the case. The case of the element displayed follows the case used when you specified the element. Suppose you want to display the three-letter abbreviation for a month. You could place "Mon," "mon," or "MON" in your format string, and you would get back "Dec," "dec," or "DEC," respectively. You will see examples of this in Table B-4, which shows the results of several sample date format specifications.

Table B-4. Date format examples

Value	Format	Result
13-Dec-1998 09:13:59 PM	dd-mon-yyyy	13-dec-1998
13-Dec-1998 09:13:59 PM	dd-Mon-yyyy	13-Dec-1998
13-Dec-1998 09:13:59 PM	DD-MON-YYYY	13-DEC-1998
13-Dec-1998 09:13:59 PM	Month dd, yyyy	December 13, 1998
13-Dec-1998 09:13:59 PM	Month dd, yyyy "at" hh:mi am	December 13, 1998 at 09:13 pm
13-Dec-1998 09:13:59 PM	mm/dd/yy	12/13/98
13-Dec-1998 09:13:59 PM	mm/dd/rr	12/13/98
13-Dec-1998 09:13:59 PM	mm/dd/yyyy	12/13/1998
13-Dec-1998 09:13:59 PM	Day	Sunday
13-Dec-1998 09:13:59 PM	ddd	347
13-Dec-1998 09:13:59 PM	ww	50
13-Dec-1998 09:13:59 PM	q	4
13-Dec-1998 09:13:59 PM	year	nineteen ninety-eight
13-Dec-1998 09:13:59 PM	Year	Nineteen Ninety-Eight
13-Dec-1998 09:13:59 PM	YEAR	NINETEEN NINETY-EIGHT

To find out how to use a date format with the ACCEPT command, consult Chapter 11. ACCEPT uses the date format to validate what the user enters, and there are some limits on how closely the user is forced to follow that format.