CONVERT

The CONVERT function converts values from one type of data to another.

Return Value

The return value depends on the value of the type argument.

Syntax

CONVERT(expression, type [argument...])

Arguments

expression

The expression or variable to be converted.

type

The type of data to which you want to convert expression. The keywords that represent the types are described in Table 7-3, "Keywords for the type Argument of the CONVERT Function".

Table 7-3 Keywords for the type Argument of the CONVERT Function

Keyword	Description
BINARY	Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value. For TEXT data types, the result will be the value itself and will therefore be of variable length. For ID and DECIMAL data types, the result will be 8 bytes long; ID values will be blank filled, when necessary. For BOOLEAN or INTEGER, the default result will be 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly). For all other data types, the result will be 4 bytes long. See "PACKED and BINARY Conversion".
BOOLEAN	Conversion to Oracle OLAP BOOLEAN data type.
BYTE	Converts a single character into an ASCII INTEGER value in the range 0 to 255. Or BYTE converts an INTEGER within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.
DATE	Conversion to Oracle OLAP DATE data type.
DATETIME	Conversion to Oracle OLAP DATETIME data type.
DECIMAL	Conversion to Oracle OLAP DECIMAL data type.
DSINTERVAL	Conversion to Oracle OLAP DML DSINTERVAL data type.
ID	Conversion to Oracle OLAP ID data type.
INFILE	Encloses an ID, TEXT, DATE, or RELATION value within single quotes, so that it can be read with an INFILE statement. This means that expression must have ID, TEXT, DATE, or RELATION value values. In the case of TEXT values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.
INTEGER	Conversion to Oracle OLAP INTEGER data type.
LONGINTEGER	Conversion to Oracle OLAP LONGINTEGER data type.
NTEXT	Conversion to standard Oracle OLAP data types. Corresponds to the NCHAR and NVARCHAR2 SQL data types. An NTEXT character is encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. A conversion from NTEXT to TEXT can result in data loss when the NTEXT value cannot be represented in the database character set.
NUMBER [(p,[s])]	Conversion to Oracle OLAP NUMBER data type.
PACKED	Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest INTEGER. See "PACKED and BINARY Conversion".
ROWID	Converts a text value to a ROWID value.
SHORTDECIMAL	Conversion to Oracle OLAP SHORTDECIMAL data type.
SHORTINTEGER	Conversion to Oracle OLAP SHORTINTEGER data type.
TEXT	Conversion to standard Oracle OLAP data types. Corresponds to CHAR and VARCHAR2 data types in SQL. A TEXT character is encoded in the database character set.
TIMESTAMP	Conversion to Oracle OLAP DML TIMESTAMP data type.
TIMESTAMP_LTZ	Conversion to Oracle OLAP DML TIMESTAMP_LTZ data type.
TIMESTAMP_TZ	Conversion to Oracle OLAP DML TIMESTAMP_TZ data type.
UROWID	Converts a text value to a UROWID value.
YMINTERVAL	Conversion to Oracle OLAP DML YMINTERVAL data type.

argument

When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done as outlined in Table 7-4, "Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE".

Table 7-4 Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE

Keyword for type argument	When Converting From	Syntax for All Arguments
TEXT	Any numeric	TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
NTEXT	Any numeric	NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
ID	Any numeric	ID [decimal-int|DECIMALS]
TEXT, NTEXT, or ID	Any datetime	ID|TEXT|NTEXT ['date_format']
TEXT, NTEXT, or ID	DATE	ID|TEXT|NTEXT ['dateformat']
ID or TEXT for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF	DATE	ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf ']
DATE	TEXT, NTEXT, or ID	DATE [date-order|dwmqy-dimname]
NTEXT	TEXT	NOXLATE
TEXT	NTEXT	NOXLATE
INFILE		INFILE [width-exp|LSIZE [escape-int|0]]
IBINARY with BOOLEAN or INTEGER		BINARY [width-exp]

decimal-int

An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).

comma-bool

A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is YES, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is YES).

paren-bool

A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is YES, parentheses are used; when the value is NO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is NO).

date_format

A text expression that specifies the template to use when converting a datetime expression to text. The valid formats for each date field are the same as the formats that you can specify using the DATE_FORMAT command.

When you do not include the date_format argument, the format of the result is determined by the default date format for the session as described in "Default Datetime Format Template".

dateformat

A text expression that specifies the template to use when converting a DATE-only expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (<)and followed by a right angle bracket (>). You can include additional text before, after, or between the components.

The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.

In the following statement, CONVERT returns today's date as a text value that is formatted by a dateformat argument.

SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

03-31-98

When you do not include the dateformat argument, the format of the result is determined by the current setting of the DATEFORMAT option.

dwmqy-dimension

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE-only value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.

In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.

show convert(today text year)

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

YR98

vnf

A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.

The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.

Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:

• Ordinal styles for the day of the month (DT and DTL)

• First-letter style for the month (MT)

• Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)

Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.

You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.

In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.

SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')

In this example, the first MONTH value in status is DEC97, and the SHOW statement presents it in the following format.

December, 1997

When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.

date-order

A text expression that specifies how to interpret the specified text value as a DATE-only value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', or 'DYM'. Each letter represents a component of the date: M stands for month, D stands for day, and Y stands for year.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

dwmqy-dimname

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE-only value when the order of the text value's components is ambiguous.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

width-exp

An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.

escape-int

Indicates whether escape sequences are to be used in the output. For this argument you can specify one of the values listed in Table 7-5, "Values for escape-int Argument of the CONVERT".

Table 7-5 Values for escape-int Argument of the CONVERT

Value	Description
-1	Do not use escapes. Precede -1 with a comma (,-1) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.
0	(Default) Use escapes for unprintable characters.
1	Use escapes for all characters.

For more information on escape sequences in the OLAP DML, see "Escape Sequences".

width-exp

An INTEGER expression that controls the width of the converted result. It can evaluate to 1, 2, or 4 bytes. The default width is 2 for BOOLEAN, or 4 for INTEGER. When an INTEGER value is too large to fit in the specified width, the result is NA. When the width is invalid or specified for some other data type, an error occurs.

NOXLATE

A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.

Notes

INFILE Conversion

The maximum number of characters in a line is 4000. An error occurs when you try an INFILE conversion that produces a line with more than 4000 characters. This can occur when the source line exceeds 99 characters and enough of them need escape sequences.

Converting DATE-only Values to Numeric Values

The result of converting a value that has the DATE-only data type to a value with any numeric data type is the sequence number that represents the date (the sequence number 1 represents January 1, 1900).

Oracle OLAP first converts the DATE-only value to an INTEGER value that is the sequence number that represents the DATE-only value. When the target data type is a numeric data type other than INTEGER, Oracle OLAP then converts that INTEGER value to the specified numeric data type.

The value 32,767 is the largest possible value for a SHORTINTEGER, and (as an INTEGER value) represents the date September 17, 1992. Therefore, CONVERT returns NA when you attempt to convert any DATE-only later than September 17, 1992 to a SHORTINTEGER value.

Converting Numeric Values to DATE-only Values

The result is the DATE-only whose sequence number matches the specified number (January 1, 1900 is represented by the sequence number 1); or NA, when the result is outside the range of valid dates. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).

When the numeric data type is an INTEGER data type, Oracle OLAP converts the INTEGER value directly to the DATE-only value whose sequence number matches the specified number. When the numeric data type is not INTEGER, Oracle OLAP first converts the numeric value to an INTEGER value and then converts that INTEGER value to a DATE-only value.

Converting DATE-only Dimension Values to ID Values

When the result is more than eight characters long, the result is truncated.

Converting Relation Values to INTEGER Values

The result is an INTEGER value that represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.

Converting Values From One Numeric Data Type to Another

The result is the value in the specified data type; or NA when the value is outside the range of valid values for the target data type.

Thus, when you try to convert a an INTEGER value that is larger than 32,767 or smaller than -32,767 to a SHORTINTEGER value, CONVERT returns NA.

String-to-Datetime Conversion Rules

The following formatting rules apply when converting string values to datetime values:

• You can omit punctuation included in the format string from the datetime string if all the digits of the numerical format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.

• You can omit time fields found at the end of a format string from the datetime string.

• When a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 7-6, "Oracle Format Matching".

'MM'

Converting Null and Blank Text Values to BYTE Values

CONVERT returns the same value for a null string ('') as it does for a blank string (' '). In both cases, you get a result of 32.

PACKED and BINARY Conversion

The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT statement to help format the file.

Examples

LIMIT month TO 'Jul96'
LIMIT product to 'Canoes'
SHOW JOINCHARS('Price of Canoes = $' CONVERT(price TEXT 0))
Price of Canoes = $200

DEFINE textvar VARIABLE TEXT
textvar = 'testvalue'
SHOW CONVERT(textvar INFILE 9 1)
'\d116\d101\d115\d116\d118\d097\d108\d117\d101'
ESCAPEBASE = 'x'
SHOW CONVERT(textvar INFILE 9 1)
'\x74\x65\x73\x74\x76\x61\x6C\x75\x65'