Conversion Functions
Conversion functions convert the expression from one data type into another type. Type conversions can also be specified using the
CAST Expressions.
ANSIDATE
ANSIDATE(expr)
Operand type: CHAR, VARCHAR, ANSIDATE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BYTE, VARBYTE
Result type: ANSIDATE
Converts the expression to internal ANSIDATE representation.
BOOLEAN
BOOLEAN(expr)
Operand type: CHAR, VARCHAR
Result type: BOOLEAN
Converts the string 'FALSE' and 'TRUE', without regard to case and where applicable, trailing or internal whitespace, to the corresponding BOOLEAN values.
Operand type: INTEGER1, SMALLINT, INTEGER, BIGINT
Result type: BOOLEAN
Converts the value 0 to FALSE and the value 1 to TRUE.
BYTE or BINARY
BYTE(expr [, len])
or
BINARY(expr [, len])
Operand type: Any
Result type: BYTE
Converts the expression to BYTE binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using null bytes.
Note: Leftmost refers to the leftmost byte in memory. This can return a different result for the same variable value depending on whether the machine is big or little endian.
Note: The binary data depends on the data type selected during the coercion of the literal in expr. This may not be the data type the user expects. For example, a very large integer literal that is too large to be stored as an INTEGER8 will be coerced into a FLOAT.
CHAR
CHAR(expr [, len])
Operand type: Any
Result type: CHAR
Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.
DECIMAL or NUMERIC
DECIMAL(expr [,precision[,scale]])
or
NUMERIC(expr [,precision[,scale]])
Operand type: Any except date and time types
Result type: DECIMAL
Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows:
Operand Default
Data Type Precision
tinyint 5
smallint 5
integer 11
bigint 19
float 15
float4 15
decimal 15
money 15
Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.
Note: Using DECIMAL on a VARCHAR column requires that the precision and scale be provided.
SELECT DECIMAL('12345678.999',14,2); returns 12345678.99
DOW
DOW(expr)
Operand type: Any absolute date
Result type: CHAR
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
FLOAT4
FLOAT4(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL, INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: FLOAT4
Converts the specified expression to FLOAT4. Numeric overflow can occur if the argument is too large for the result type (possible from string or decimal).
The range of values for float4 is processor dependent.
FLOAT8
FLOAT8(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: FLOAT
Converts the specified expression to FLOAT. Numeric overflow can occur if the argument is too large for a float (possible from string or decimal).
The range of values for float is determined by IEEE_754.
HEX
HEX(expr)
Operand type: Any
Result type: VARCHAR
Returns the hexadecimal representation of the internal Vector form of the argument expression. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each byte of the argument requires two bytes.
HEX('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC).
HEX(INT4(125)) returns '0000007D', the hexadecimal equivalent of the 4 byte binary integer 125.
INT1 or TINYINT
INT1(expr)
or
TINYINT(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: TINYINT
Converts the expression to TINYINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT2 or SMALLINT
INT2(expr)
or
SMALLINT(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: SMALLINT
Converts the expression to SMALLINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT4 or INT or INTEGER
INT4(expr)
or
IINT(expr)
or
INTEGER(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: INTEGER
Converts the expression to INTEGER. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT8 or BIGINT
INT8(expr)
or
BIGINT(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: BIGINT
Converts the expression to BIGINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INTERVAL_DTOS
INTERVAL_DTOS(expr)
Operand type: CHAR, VARCHAR, INTERVAL DAY TO SECOND
Result type: INTERVAL DAY TO SECOND
Converts the expression to internal INTERVAL DAY TO SECOND representation.
INTERVAL_YTOM
INTERVAL_YTOM(expr)
Operand type: CHAR, VARCHAR, INTERVAL YEAR TO MONTH, BYTE, VARBYTE
Result type: INTERVAL YEAR TO MONTH
Converts the expression to internal INTERVAL YEAR TO MONTH representation.
MONEY
MONEY(expr)
Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BYTE, VARBYTE
Result type: MONEY
Converts the expression to internal MONEY representation. Rounds floating point and decimal values, if necessary.
NCHAR
NCHAR(expr [, len])
Operand type: Any
Result type: NCHAR
Converts argument to NCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.
NVARCHAR
NVARCHAR(expr [, len])
Operand type: Any
Result type: NVARCHAR
Converts argument to NVARCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.
TIME or TIME_WO_TZ
TIME(expr)
or
TIME_WO_TZ(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITHOUT TIME ZONE
Converts the expression to internal TIME WITHOUT TIME ZONE representation.
TIME_LOCAL
TIME_LOCAL(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITH LOCAL TIME ZONE
Converts the expression to internal TIME WITH LOCAL TIME ZONE representation.
TIME_WITH_TZ
TIME_WITH_TZ(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITH TIME ZONE
Converts the expression to internal TIME WITH TIME ZONE representation.
TIMESTAMP or TIMESTAMP_WO_TZ
TIMESTAMP(expr)
or
TIMESTAMP_WO_TZ(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITHOUT TIME ZONE
Converts the expression to internal TIMESTAMP WITHOUT TIME ZONE representation.
TIMESTAMP_LOCAL
TIMESTAMP_LOCAL(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITH LOCAL TIME ZONE
Converts the expression to internal TIMESTAMP WITH LOCAL TIME ZONE representation.
TIMESTAMP_WITH_TZ
TIMESTAMP_WITH_TZ(expr)
Operand type: CHAR, VARCHAR, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITH TIME ZONE
Converts the expression to internal TIMESTAMP WITH TIME ZONE representation.
TO_CHAR
TO_CHAR (datetime [,format])
Operand type: ANSIDATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE, BYTE, VARBYTE
Result type: VARCHAR
Converts a datetime or interval value to a value of VARCHAR data type in the specified date format. If you omit format, then date is converted to a VARCHAR value as follows:
• ANSIDATE values are converted to values in the default date format.
• TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
• TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
The format can be any of the following:
For TO_CHAR, the case and punctuation of the format are important. Formats whose results are alphabetic follow the case of the format. For example, 'DY' will yield 'MON' but 'Dy' will yield 'Mon'.
SELECT TO_CHAR('2013-07-30 23:42:00.290533-07:00','YYYY-MONTH-DAY HH24:MI:SSXFF6 tzh:tzm');
returns 2013-JULY-TUESDAY 23:42:00.290533 -07:00
SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD');
returns 2013-JUL-30
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
returns 2013-07-30 23:36:19
TO_DATE
TO_DATE(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: ANSIDATE
Converts char to a value of ANSIDATE data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default date format. Valid values for format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added.
The following examples converts a character string into a date:
SELECT TO_DATE('2012-Dec-17', 'YYYY-MON-DD'); returns 2012-12-17.
SELECT TO_DATE('20121217', 'YYYYMMDD'); returns 2012-12-17.
SELECT TO_DATE('2012-Dec', 'YYYY-MON'); returns 2012-12-01.
SELECT TO_DATE('2012', 'YYYY'); returns 2012-01-01.
TO_TIME
TO_TIME_(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIME
Converts char to a value of TIME data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a date:
SELECT TO_TIME('10:01:59','HH24:MM:SS');
returns 10:01:59
TO_TIMESTAMP
TO_TIMESTAMP(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIMESTAMP
Converts char to a value of TIMESTAMP data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a date:
SELECT TO_TIMESTAMP('2012-Dec-17', 'YYYY-MON-DD');
returns 2012-12-17 00:00:00.000000
SELECT TO_TIMESTAMP('20121217121314', 'YYYYMMDDHH24MISS');
returns 2012-12-17 12:13:14.000000
SELECT TO_TIMESTAMP ('2012-Dec', 'YYYY-MON');
returns 2012-12-01 00:00:00.000000
SELECT TO_TIMESTAMP ('2012', 'YYYY');
returns 2012-01-01 00:00:00.000000
SELECT TO_TIMESTAMP('2013-10-11 20:01:59.123456','YYYY-MM-DD HH24:MI:SSXFF6');
returns 2013-10-11 20:01:59.123456
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIMESTAMP WITH TIME ZONE
Converts char to a value of TIMESTAMP data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a date:
SELECT TO_TIMESTAMP_TZ('2012-Dec-17', 'YYYY-MON-DD TZH:MZM');
returns 2012-12-17 00:00:00.000000-08:00
SELECT TO_TIMESTAMP_TZ('2013-10-11 20:01:59.123456-04:00','YYYY-MM-DD HH24:MI:SSXFF6 tzh:tzm');
returns 2013-10-11 20:01:59.123456-04:00
UNHEX
UNHEX(expr)
Operand type: CHAR, VARCHAR
Result type: VARCHAR
Returns the opposite of the hex function. For example, unhex('61626320') returns 'abc ' and unhex('01204161') returns '\001Aa'.
Exceptions can occur when a “c” data type suppresses the display of certain stored characters, or when the output data type differs from the input type.
Note: Normally one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.
VARBYTE or VARBINARY
VARBYTE(expr [, len])
or
VARBINARY(expr [, len])
Operand type: Any
Result type: BYTE VARYING
Converts the expression to BYTE VARYING binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
VARCHAR
VARCHAR(expr [, len])
Operand type: Any
Result type: VARCHAR
Converts argument to VARCHAR string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
Length of Results for Data Type Conversions
When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.
Some functions have an optional length parameter. If this parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), nchar(), nvarchar(), and text() are as follows: