O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required