Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata