Oracle SQL*Plus: The Definitive Guide

Formatting Dates

SQL*Plus doesn’t really 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 just another character column. Table 2.3 shows the date format elements that can be used with the TO_CHAR function.

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 also provide a date format specification. SQL*Plus will reject any date the user enters that does not match that format.

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	The century number. This will be 20 for years 1900 through 1999.
SCC	Same as CC, but BC dates will be negative.
D	The number of the day of the week. This will be 1 through 7.
DAY	The name of the day. This will be Saturday, Sunday, Monday, and so forth.
DD	The day of the month.
DDD	The day of the year.
DY	The abbreviated name of the day. This will be Sat, Sun, Mon, and so forth.
HH