4. Understanding the Elements of SQL Statements : SQL Functions : Scalar Functions : Conversion Functions
 
Share this page                  
Conversion Functions
Conversion functions convert the expression from one data type into another type. Type conversions can also be specified using the CAST expression (see CAST Expressions).
ANSIDATE
ANSIDATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, ANSIDATE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: ANSIDATE
Converts the expression to internal ANSIDATE representation.
BOOLEAN
BOOLEAN(expr)
Operand type: CHAR, VARCHAR, C, TEXT
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.
C
C(expr [, len])
Operand type: Any except long data types
Result type: C
Converts argument to C 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.
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.
DATE or INGRESDATE
DATE(expr)
or
INGRESDATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, NCHAR, NVARCHAR
Result type: INGRESDATE
Converts a C, CHAR, VARCHAR, TEXT, NCHAR, or NVARCHAR string to internal date representation.
DECIMAL or NUMERIC
DECIMAL(expr [,precision[,scale]])
or
NUMERIC(expr [,precision[,scale]])
Operand type: Any except ingresdate and ANSI date/time
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 Data Type
Default 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.
DOW
DOW(expr)
Operand type: Any absolute date
Result type: C
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
FLOAT4
FLOAT4(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL, INTEGER1, SMALLINT, INTEGER
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: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
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 Ingres 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. For example, hex('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC). Also, 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: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BOOLEAN
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: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BOOLEAN
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
INTEGERr(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BOOLEAN
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: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER, BOOLEAN
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: C, CHAR, VARCHAR, TEXT, INGRESDATE, 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: C, CHAR, VARCHAR, TEXT, INGRESDATE, INTERVAL YEAR TO MONTH
Result type: INTERVAL YEAR TO MONTH
Converts the expression to internal INTERVAL YEAR TO MONTH representation.
LONG_BYTE or LONG_BINARY
LONG_BYTE(expr)
or
LONG_BINARY(expr)
Operand type: Any
Result type: LONG BYTE
Converts the expression to LONG BYTE binary data.
LONG_VARCHAR
LONG_VARCHAR(expr)
Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE
Result type: LONG VARCHAR
Converts the expression to LONG VARCHAR.
LONG_NVARCHAR
LONG_NVARCHAR(expr)
Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE
Result type: LONG NVARCHAR
Converts the expression to LONG NVARCHAR.
MONEY
MONEY(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
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.
OBJECT_KEY
OBJECT_KEY(expr)
Operand type: VARCHAR, CHAR, C, TEXT
Result type: OBJECT_KEY
Converts the operand to an OBJECT_KEY.
TABLE_KEY
TABLE_KEY(expr)
Operand type: VARCHAR, CHAR, C, TEXT
Result type: TABLE_KEY
Converts the operand to a TABLE_KEY.
TEXT
TEXT(expr [, len])
Operand type: Any
Result type: TEXT
Converts argument to TEXT 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.
TIME_LOCAL
TIME_LOCAL(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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.
TIME or TIME_WO_TZ
TIME(expr)
or
TIME_WO_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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.
TIMESTAMP_LOCAL
TIMESTAMP_LOCAL(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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.
TIMESTAMP or TIMESTAMP_WO_TZ
TIMESTAMP(expr)
or
TIMESTAMP_WO_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, 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.
TO_CHAR
TO_CHAR (datetime [,format])
Operand type: ANSIDATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE
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:
Format
Description
punctuation "text"
Preserves literal punctuation and quoted text
AD
A.D.
AD indicator
AM
A.M.
PM
P.M.
AM/PM indicator
CC
Century (Supported in TO_CHAR only.)
D
Number of day of week 1 - 7
DAY
Day name padded to suitable width to retain column
DD
Day of month 1 - 31
DDD
Day of year 1 - 366
DY
Day name in 3 characters
E
EE
Era name (Supported in TO_CHAR only.)
FF [1..9]
Fractional seconds
FM
Toggle whether formats have leading or trailing blanks
FX
Toggle whether exact matching is required
HH
Hour of day 1 - 12
HH12
Hour of day 1 - 12 (Supported in TO_CHAR only.)
HH24
Hour of day 0 - 23
IW
ISO week of year 1 - 53 (Supported in TO_CHAR only.)
IYYY
IYY
IY
I
Last 4, 3, 2, 1 digits of ISO year (Supported in TO_CHAR only.)
J
Julian day (days since January 1, 4712 BC)
MI
Minutes 0 - 59
MM
Month 01 - 12
MON
First 3 characters of month name
MONTH
Month name padded to suitable length
Q
Quarter 1 - 4 (Supported in TO_CHAR only.)
RM
Month in Roman numerals
RR
Round year in 2 digits
RRRR
Round year in 2 or 4 digits
SS
Seconds 0 - 59
SSSS
Seconds since midnight 0 - 86399
TZH
Time zone hour
TZM
Time zone minute
WW
Week of year 1 - 53 (Supported in TO_CHAR only.)
W
Week of month 1 - 5 (Supported in TO_CHAR only.)
X
Decimal
Y,YYY
Year with comma in this position
YEAR
Year spelled out (Supported in TO_CHAR only.)
YYYY
YYY
YY
Y
Last 4, 3, 2, or 1 digits of year
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','YEAR-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 Function (see 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 Function (see 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 Function (see 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 Function (see 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: Any
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:
Data Type of Argument
Result Length
byte
Length of operand
byte varying
Length of operand
c
Length of operand
char
Length of operand
decimal
Depends on precision and scale of column
float & float4
11 characters; 12 characters on IEEE computers
ingresdate
25 characters
integer1 (tinyint)
Maximum 4 characters
integer2 (smallint)
Maximum 6 characters
integer
Maximum 11 characters
integer8 (bigint)
Maximum 19 characters
long byte
Length of operand
long varchar
Length of operand
long nvarchar
2 x length of operand
nchar
2 x length of operand
nvarchar
2 x length of operand
money
20 characters
text
Length of operand
varchar
Length of operand