Previous | Table of Contents | Next

Page 69

Format Meaning
Dy Same as DY, but only the initial letter is capitalized (Fri)
dy Same as DY, but all the letters are lower case. (fri)
J Julian day of the year (1997004)
WW Number of the week of the year
W Number of the week of the month
IW Number of the week of the ISO standard
MM Number of the month (12)
MON Three-letter abbreviationof month (APR)
Mon Same as MON, but only the initial letter is capitalized (Apr)
mon Same as MON, but all letters are lower case (apr)
MONTH Month fully spelled out (APRIL)
Month Same as MONTH, but only the initial letter is capitalized (April)
month Same as MONTH, but all letters are lowercase (april)
RM Roman numeral month (XII)
I Last digit of the ISO year
IY Last two digits of the ISO year
IYY Last three digits of the ISO year
IYYY ISO four-digit standard year
RR Last two digits of the year.
SYYYY Signed year (1500 bc = -1500)
Y Last digit of the year (7)
YY Last two digits of the year (97)
YYY Last three digits of the year (997)
YYYY Four digit year (1997)
YEAR The year spelled out (NINETEEN-NINETY-SEVEN)
Year The year spelled out with initial capitals
year The year spelled in all lower case letters
Q Number of the quarter
CC or SCC Century
                                                                            continues

Page 70

Table 3.6Continued

Format Meaning
BC or AD Displays BC or AD
B.C. or A.D. Displays B.C. or A.D.
bc or ad Displays bc or ad
b.c. or a.d. Displays b.c. or a.d.
AM or PM Meridian indicator
A.M. or P.M. Meridian indicator with periods
am or pm Meridian indicator with all lowercase letters
HH Hour of the day
HH12 Same as HH
HH24 Hour of the day in military format
MI Minute of the hour.
SS Second of the minute
SSSS Seconds since midnight, the number is always between 0 - 86399

Date pictures must be used with the to_char function. This function is a date function. It can be used to change any numeric data into a string. It is used most often with date pictures. The picture is placed in the format section of the function (see Listing 3.40).

Listing 3.40L_03_40.TXT—Example of Various Date Pictures

SQL> column date1 format a40
SQL> column date2 format a33
SQL> select last_name, birth_date,
  2    to_char(birth_date, `DAY MONTH YEAR') DATE1,
  3    to_char(birth_date, `dd-mm-yyyy hh mi ss') DATE2
4  from employee;
LAST_NAME       BIRTH_DAT DATE1                                    DATE2
--------------- --------- ---------------------------------------- --------------------------------
COOLIDGE        01-JUL-72 SATURDAY  JULY      Eighteen SEVENTY-TWO 01-07-1972 12 Â00 00
JOHNSON         27-AUG-08 THURSDAY  AUGUST    ONE THOUSAND NINE HU 27-08-1908 12 Â00 00
.

19 records selected.

Page 71

NOTE
In the preceding example, date 2 does not display the full year for the second record. The reason is because the column was not formatted to the proper size. This can be done by changing the column format settings. This command will be discussed in greater detail in the next chapter.

You can also add suffixes to values in the picture to enhance readability. These suffixes are sp, th, and spth. sp causes SQL*PLUS to spell the value (ten), th places `th' after the value (10th), and spth spells the value out and places a `th' at the end. In addition to these suffixes, you can also include text in the picture by enclosing it with double quotes. Listing 3.41 shows an example of special formatting:

Listing 3.41L_03_41.TXT—Example of Using Literals in the Date Picture

SQL> set linesize 130
SQL> column date1 format a90
SQL> select last_name, to_char(birth_date,
2    `"THE" ddth "of" MONTH "IN THE YEAR OF OUR LORD" YEAR') date1
3  from employee;
LAST_NAME       DATE1
--------------- -----------------------------------------------------------------------------------------
COOLIDGE        THE 01 of JULY      IN THE YEAR OF OUR LORD EIGHTEEN SEVENTY-TWO
JOHNSON         THE 27 of AUGUST    IN THE YEAR OF OUR LORD ONE THOUSAND NINE
     ÂHUNDRED
REAGAN          THE 01 of OCTOBER   IN THE YEAR OF OUR LORD NINETEEN TWENTY-FOUR
BUSH            THE 06 of FEBRUARY  IN THE YEAR OF OUR LORD NINETEEN ELEVEN

.

19 records selected.

Notice that the date picture must be enclosed by single quotes. Within the single quotes, the special date characters may be placed along with any literal text. The literal text such as "THE YEAR OF OUR LORD" must be enclosed by double quotes.

Understanding Group Functions

One last set of functions that you can use in the select clause is the group functions. These functions are performed on sets of data that range from one record to the entire table. They evaluate the records in the set and return a value. Table 3.7 contains a listing of group functions.

Page 72

Table 3.7Group Functions

Function Name Syntax Description
avg avg(value) Computes the average of the set
count count(value) Counts the number of rows in the set
max max(value) Determines the greatest value of the set
min min(value) Determines the smallest value of the set
stddev stddev(value) Computes the standard deviation of the set
sum sum(value) Computes the sum or total of the set
variance variance(value) Computes the variance of the total

The Average Function

The average function is used to compute the average or mean of a group of data. Listing 3.42 shows an example that computes the average wage per employee in the table.

Listing 3.42L_03_42.TXT—Example of the AVG Function

SQL> select avg(wages) from employee;
AVG(WAGES)
10905.882

Null valued records are not included in the calculation. When you think some records have nulls, be certain to use the nvl function (see Listing 3.43).

Listing 3.43L_03_43.TXT—Example of the AVG Function Using a Nested NVL Function

SQL> select avg(nvl(wages, 0)) from employee;
AVG(NVL(WAGES,0))

9757.8947

The difference in the values returned for the two statements can be attributed to the fact that the avg function, and in fact all group functions, do not consider null valued records a part of the set of records for the calculation. This means the denominator used in Listing 3.42 is greater that the denominator in Listing 3.42. In both queries, the numerator is the same. This is why the result in Listing 3.42 is larger than Listing 3.43.

The COUNT Function

The count function calculates the number of values in a set or group. When a column name is placed within the function, only those records that do not have a null value in the column are

Previous | Table of Contents | Next