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