Section B.3. Formatting Dates

Table of contents:

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.

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net