Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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