Additional functions

CONVERSION FUNCTIONS

The primary purpose of conversion functions is to convert one data type to another data type. This is termed explicit conversion and allows you as the programmer exact control over how Oracle treats data. Table 10.5 lists some conversion functions, and their use and definition.

Table 10.5

Function	Use/Definition
CAST	Convert an Oracle data type to another Oracle data type.
TO_CHAR (and TO_NCHAR)	Converts a character, numeric, or datetime value to a character string.
TO_DATE	Converts a character string or number to a datetime value.

TO_CHAR and TO_DATE Functions

The TO_CHAR and TO_DATE functions are used to format output and to convert data from one data type to another. The general form of these functions is:
TO_CHAR(ValueToConvert, {'FormatString','NLSparameter'})
TO_DATE(CharacterString, {'FormatString','NLSparameter'})
The TO_CHAR function may be used to convert NCHAR, NVARCHAR2, CLOB, and NCLOB data types to a VARCHAR2 character string. TO_CHAR can also convert the NUMBER, BINARY_FLOAT, and BINARY_DOUBLE data types to a VARCHAR2 character string. The optional FormatString is used to specify how numbers are to be formatted when they are converted to a string. The NLSparameter is an optional value used to specify the national language to use if one other than the current default is required.

SQL Example 10.19 illustrates formatting a NUMBER value by displaying the ChargeAmount column value of the treatment table formatted as a currency value. While the format specified is $99,999.99, the actual location of the currency symbol is dictated by the size of the ChargeAmount column value.

/* SQL Example 10.19 */

SELECT TO_CHAR(ChargeAmount, '$99,999.99') "Charge"

FROM Treatment

WHERE PatientID = '100002';

Charge

-----------

$35.00

$30.00
Table 10.6 specifies some of the more common formatting characters that can be used to specify a format string value when converting a NUMBER value to VARCHAR2 with the TO_CHAR function.

Table 10.6

Format	Example	Use/Description
, (comma)	9,999	Specifies to position a comma within a numeric value that is a formatted string.
. (period)	999.99	Specifies to locate a decimal point within a formatted string.
$ (dollar sign)	$999.99	Includes a dollar sign as a leading character.
0 (zero)	0999 9990	Includes leading zeros. Includes trailing zeros.
9 (nine)	9999	Specifies the maximum number of numeric positions to allot for numbers converted to characters. A minus sign is displayed if the number is negative. Leading zeros are blank unless the value zero is a significant digit.
U (letter U)	U9999	Specifies to include the Euro dual currency symbol. (current value of the NLS_DUAL_CURRENCY parameter).

One of the primary uses of the TO_CHAR function is to convert date and time values to character strings. If the FormatString is omitted when converting DATE data type values, the date conversion is to the default date format—generally DD-MON-YY.

The TO_DATE function is the mirror-image of TO_CHAR and converts a date value to a character string. Both of these functions can be used to format output by using a wide range of formatting options.

The default date format for use with TO_CHAR and TO_DATE can be set by assigning a value to the NLS_DATE_FORMAT (national language support date format) parameter. The ALTER SESSION command shown in SQL Example 10.20 sets the format from the default of DD-MON-YY to DD-MON-YYYY to display a full, four-digit year.

/* SQL Example 10.20 */

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

SELECT SYSDATE

FROM Dual;

SYSDATE

-----------

30-SEP-2007
Table 10.7 specifies some of the more common date formats that can be used with the TO_CHAR and TO_DATE functions. A complete listing can be obtained from Oracle's Web site.

Table 10.7

Format	Use/Description
D	Day of week
DD	Days in month
DDD	Days in year
DY	Three-letter day abbreviation
DAY	Day spelled out—padded with blank characters to 9 characters in length
HH, HH12, and HH24	Hour of day; Hour of day (hours 1–12); Hour of day (hours 1–24)
MI	Minute (0–59)
MM	Month – numbered 01 to 12
MON	Month spelled out in abbreviated 3-character format
MONTH	Month spelled out—padded with blank characters to 9 characters in length
SS	Second (0–59)
Y, YY, YYY, and YYYY	Year in 1-, 2-, 3-, or 4-year formats

The SELECT statement in SQL Example 10.21 demonstrates formatting output for the patient table's BirthDate column.

/* SQL Example 10.21 */

SELECT BirthDate "Birth Date",

TO_CHAR(BirthDate, 'MONTH DD, YYYY') "Spelled Out"

FROM Patient;

Birth Dat Spelled Out

--------- ------------------

02-JAN-96 JANUARY 02, 1996

02-FEB-67 FEBRUARY 02, 1967

14-FEB-79 FEBRUARY 14, 1979

More rows will be displayed ...
This query in SQL Example 10.22 displays the time information stored to the BirthDate column, if that is of interest to one of the Company managers.
/* SQL Example 10.22 */

SELECT TO_CHAR(BirthDate, 'HH:MI:SS') "B-Time"

FROM Patient;
B-Time

--------

12:00:00

12:00:00

12:00:00

More rows will be displayed ...
Each row in the table has the same time information stored to the BirthDate column because time information was not specified when the sample Madison Hospital database was created. The default time used when dates are entered is 12:00:00 (midnight).

The TO_DATE function is often used with the INSERT command to convert a literal string into an Oracle date format for purposes of data storage. The example in SQL Example 10.23 inserts a new row into the dependent table. Here the date format specifies the month and day as two digits each followed by a four-digit year value.

/* SQL Example 10.23 */

INSERT INTO dependent VALUES ('67555', 'Lorena', 'F',

TO_DATE('10-02-1991', 'MM-DD-YYYY'), 'DAUGHTER');
TO_DATE is also used in WHERE clauses as is done in SQL Example 10.24 that displays dependents born after January 1, 1990.
/* SQL Example 10.24 */

SELECT Name, BirthDate

FROM dependent

WHERE BirthDate > TO_DATE('01-JAN-1990');

NAME BIRTHDATE

---------------------------------------- ---------

Jo Ellen 05-APR-96

Andrew 25-OCT-98

Rita 11-MAY-94
The TO_DATE function used above will fail if the character string used to specify the date in the TO_DATE function does not match the default Oracle format of DD-MON-YY. In this situation Oracle will return the ORA-1843: not a valid month error message as shown in SQL Example 10.25.
/* SQL Example 10.25 */

SELECT Name, BirthDate

FROM dependent

WHERE BirthDate > TO_DATE('01-01-1990');

ERROR at line 3:

ORA-01843: not a valid month

CAST Function

The CAST function can convert an Oracle data type to another Oracle data type. It is also used to convert collection-typed values as may be the case when the object of the CAST function is a column of values from a subquery. The general formats of the CAST function are given here with the first format used to convert among Oracle data types and the second format used to convert values returned by a subquery. We do not cover the use of MULTISET casting in this textbook:
CAST(Expression, AS DataType)

CAST(MULTISET (Subquery) AS Datatype)

SQL Example 10.25 illustrates use of the CAST function in two different SELECT statements. The first SELECT statement demonstrates casting a character string representing a date as a TIMESTAMP data type for the local time zone. The expression provides gives string that includes both a date and time to be converted. The second SELECT statement illustrates casting the ChargeAmount column of the treatment table. Note that the function is converting the data from one data type to another. The output is not necessarily the best for displaying a result table.
/* SQL Example 10.25 */

SELECT CAST('30-SEP-07 10:58:15' AS TIMESTAMP WITH LOCAL TIME ZONE) "Date-Time"

FROM Dual;
Date-Time

----------------------------

30-SEP-07 10.58.15.000000 AM
SELECT CAST(ChargeAmount AS CHAR(10)) "Charge Amt"

FROM Treatment

WHERE PatientID = '100002';
Charge Amt

----------

35

30