Data Transformation Functions

TO_CHAR

Syntax

TO_CHAR(d[, format])

Description

The TO_CHAR function transforms a date or a "d" number into a string of symbols. If the "format" parameter is specified it is used to control the result structure. Format string consists of format elements. Each element returns some part of a value, for example, it can be the month. Basic format elements used in the TO_CHAR function are described in the table:

Element of the date format	Description
"text"	A text enclosed in double quotes is reproduced unchanged.
D	Day of the week (1 - 7).
DAY	Name of the day padded with spaces to the length of nine symbols.
DD	Day of the month (1 - 31).
DDD	Day of the year (1 - 366).
DY	Abbreviated weekday name.
HH12	Hour (1 - 12).
HH24	Hour (1 - 24).
IW	Week of the year (1 - 52, 1 - 53) based on the ISO standard.
IYY, IY, I	Last three, two or one digit of the year.
YYYY	Four-digit indication of the year.
Ml	Minutes (0 - 59).
MM	Month (1 - 12).
MON	Abbreviated name of the month.
MONTH	Name of the month padded with spaces to the length of nine symbols.
Q	Quarter (1 - 4).
RM	Month in Roman numerals (I - XII).
SS	Seconds (0 - 59).
SSSSS	Seconds starting from midnight (0 - 86399).
Element of numeric format
9	A digit is displayed. The leading zero is replaced with a space.
0	A digit is displayed. The leading zero is displayed.
EEEE	The result is displayed in the exponential notation.
G	A group separation symbol is displayed in the specified position.

Example

TO_CHAR(SYSDATE,'IYYY') = VAL

Value of the VAL field (Date type) should match the current year.

TO_LONGCHAR

Syntax

TO_LONGCHAR(d)

Description

The TO_LONGCHAR functions transforms the input value "d" into a long string (LONGCHAR).

Example

TO_LONGCHAR(VAL) IS NOT NULL

The VAL field should not contain the NULL value after it has been transformed into a long string.

TO_DATE

Syntax

TO_DATE(string[, format])

Description

The TO_DATE functions transforms a string into the Date type. The "format" argument is a string of the date format. Format elements are the same as the date format elements for the TO_CHAR function.

Example

SYSDATE > TO_DATE(VAL,'DD.MM.YYYY')

The VAL field of the string type is to contain a value corresponding to the date, but less than the current date.

TO_NUMBER

Syntax

TO_NUMBER(string[, format])

Description

The TO_NUMBER function transforms the "string" string into a numerical value. The "format" argument is a string of the number format. Format elements are the same as the number format elements for the TO_CHAR function.

Example

100.00 > TO_NUMBER(VAL,'999.99')

The VAL field of the string type is to contain a value corresponding to the number, but less than 100.00.