SQL Language Guide : 4. Elements of SQL Statements : SQL 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 Expressions.
ANSIDATE
ANSIDATE(expr)
Operand type: CHAR, VARCHAR, ANSIDATE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
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.
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
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
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
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
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
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
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
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
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
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
SSSSS
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. 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
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
char
Length of operand
decimal
Depends on precision and scale of column
float & float4
11 characters; 12 characters on IEEE computers
integer1 (tinyint)
Maximum 4 characters
integer2 (smallint)
Maximum 6 characters
integer
Maximum 11 characters
integer8 (bigint)
Maximum 19 characters
nchar
2 x length of operand
nvarchar
2 x length of operand
money
20 characters
varchar
Length of operand