A function that converts a given expression to a specified data type.
Arguments
type
The data type to convert expression to. The types you can specify depend on whether you are using the InterSystems IRIS CONVERT() syntax or the ODBC {fn CONVERT()} syntax.
CONVERT() Function
The InterSystems IRIS CONVERT() syntax supports the data types described in Data Types. Common data types that you can specify include:
-
Character string data types: CHAR, CHARACTER, VARCHAR. For some character string types, you can optionally specify a maximum length parameter. For example: VARCHAR(10)
-
Numeric data types: INTEGER, DECIMAL, DOUBLE, MONEY. For some numeric types, you can optionally specify precision and scale parameters. For example: DECIMAL(8,4)
-
Data and time data types: DATE, TIME, TIMESTAMP, POSIXTIME
-
Bit and binary data types: BIT, BINARY, VARBINARY
{fn CONVERT()} Function
The ODBC {fn CONVERT()} syntax supports a more limited set of data types than the CONVERT() syntax. The supported data types correspond to the ones you specify for the CONVERT() syntax but must be preceded by the SQL_ keyword.
This table describes the valid data types that you can specify, separated into two groups:
-
The first group converts both the data value and the data type. For example, converting a %Date source to SQL_VARCHAR transforms the date to a text value and the query processes it as a VARCHAR data type.
-
The second group converts the data type but does not convert the data value. For example, converting a %Date source to INTEGER does not transform the %Date source but the query processes the integer form of the date as an INTEGER data type.
Source |
Valid Conversion Types (Type and Value Converted) |
Valid Conversion Types (Only Type Converted) |
Any numeric data type |
SQL_VARCHAR, SQL_DOUBLE, SQL_DATE, SQL_TIME |
n/a |
%String |
SQL_DATE, SQL_TIME, SQL_TIMESTAMP |
n/a |
%Date |
SQL_VARCHAR, SQL_POSIXTIME, SQL_TIMESTAMP |
SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT, SQL_DATE |
%Time |
SQL_VARCHAR, SQL_POSIXTIME, SQL_TIMESTAMP |
SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT, SQL_TIME |
%PosixTime |
SQL_TIMESTAMP, SQL_DATE, SQL_TIME |
SQL_VARCHAR, SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT |
%TimeStamp |
SQL_POSIXTIME, SQL_DATE, SQL_TIME |
SQL_VARCHAR, SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT |
Any non-stream data type |
SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT |
SQL_DOUBLE |
When specifying data types for this, keep these points in mind:
-
SQL_VARCHAR is the standard ODBC representation. When converting to SQL_VARCHAR, dates and times are converted to their appropriate ODBC representations; numeric datatype values are converted to a string representation. When converting from SQL_VARCHAR, the value must be a valid ODBC Time, Timestamp, or Date representation.
-
When converting a time value to SQL_TIMESTAMP or SQL_POSIXTIME, an unspecified date defaults to 1841-01-01. In the CONVERT() syntax, the date defaults to 1900-01-01.
-
When converting a date value to SQL_TIMESTAMP or SQL_POSIXTIME the time defaults to 00:00:00.
-
Fractional seconds can be preceded by either a period (.) or a colon (:). The symbols have different meanings. A period indicates a standard fraction; thus 12:00:00.4 indicates four-tenths of a second, and 12:00:00.004 indicates four-thousandth of a second. A colon indicates that what follows is in thousandths of a second; thus 12:00:00:4 indicates four-thousandth of a second. The permitted number of digits following a colon is limited to three.
-
When converting to an integer data type or the SQL_DOUBLE data type, the CONVERT function converts data values (including dates and times) to a numeric representation. For SQL_DATE, this is the number of days since January 1, 1841. For SQL_TIME, this is the number of seconds since midnight. When CONVERT encounters a nonnumeric character, it truncates the input string at that character. The integer data types also truncate decimal digits, returning the integer portion of the number.
expression
The expression to be converted to a new data type. expression can be scalar, such as a single string value, or nonscalar, such as a table column. The valid values of expression depend on the data type specified by type.
-
If expression does not have a defined data type (for example, a host variable supplied by ObjectScript) its data type defaults to the string data type.
-
If expression contains stream data and you are using the {fn CONVERT(expression,type)} syntax, then CONVERT issues an SQLCODE -37 error.
-
If expression is NULL, the converted value remains NULL, regardless of the specified type.
-
If expression is an empty string ('') or a nonnumeric string value, the returned value depends on the specified type:
-
If type is a string data type, then CONVERT returns the supplied value.
-
If type is a numeric data type or type TIME, SQL_TIME, or SQL_DATE, then CONVERT returns 0 (zero).
Specifying an invalid value given the type results in an SQLCODE -141 error.
Examples
Convert Between Numeric Types
This example compares the conversion of a fractional number using the DECIMAL and DOUBLE data types. It uses the InterSystems IRIS CONVERT() syntax. The conversion to DOUBLE results in a loss of precision.
SELECT CONVERT(DECIMAL,-123456789.0000123456789) AS DecimalVal, -- -123456789.0000123457
CONVERT(DOUBLE,-123456789.0000123456789) AS DoubleVal -- -123456789.00001235306
This statement uses the ODBC {fn CONVERT()} syntax to perform a similar conversion. This syntax does not support a DECIMAL data type, so the statement converts to a DOUBLE data type only.
SELECT {fn CONVERT(-123456789.0000123456789,SQL_DOUBLE) } AS DecimalVal -- -123456789.00001235306
Convert Between Character Strings
This example shows how to truncate a string by performing a VARCHAR-to-VARCHAR conversion, specifying an output string length shorter than the expression string length. Truncation is supported only for the InterSystems IRIS CONVERT() syntax. The only supported character string format for the ODBC {fn CONVERT()} syntax is SQL_VARCHAR.
SELECT CONVERT(VARCHAR(5),'Hello, World') As TruncatedValue -- 'Hello'
If a character data type has no specified length, the default maximum length is 30 characters.
SELECT CONVERT(VARCHAR,'This string is more than 30 characters.') --This string is more than 30 ch
SELECT {fn CONVERT('This string is more than 30 characters.',SQL_VARCHAR) } --This string is more than 30 ch
For the CONVERT() syntax, this maximum length also applies to converts to BINARY or VARBINARY types. Otherwise, these data types with no specified length are mapped to a MAXLEN of 1 character, as shown in the Data Types table.
Convert Stream Data to Character String
This example converts a character stream field to a VARCHAR text string. It also displays the length of the character stream field using CHAR_LENGTH:
SELECT Notes,CONVERT(VARCHAR(80),Notes) AS NoteText,CHAR_LENGTH(Notes) AS TextLen
FROM Sample.Employee WHERE Notes IS NOT NULL
The ODBC {fn CONVERT()} syntax does not support character streams.
Convert Date to Character String
This example converts dates in the "DOB" (Date Of Birth) column to the SQL_VARCHAR data type. The resulting string is in the format: yyyy-mm-dd.
SELECT DOB,CONVERT(VARCHAR,DOB) AS DOBtoVChar
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_VARCHAR)} AS DOBtoVChar
FROM Sample.Person
This example shows several conversions of the date-of-birth field (DOB) to a formatted character string. A sample output date string appears in a comment after each conversion.
SELECT DOB,
CONVERT(VARCHAR(20),DOB) AS DOBDefault, -- Mar 20 1983 12:00AM
CONVERT(VARCHAR(20),DOB,100) AS DOB100, -- Mar 20 1983 12:00AM
CONVERT(VARCHAR(20),DOB,107) AS DOB107, -- Mar 20, 1983
CONVERT(VARCHAR(20),DOB,114) AS DOB114, -- 00:00:00.000
CONVERT(VARCHAR(20),DOB,126) AS DOB126 -- 1983-03-20T00:00:00:
FROM Sample.Person
The default format and the code-100 format are the same. Because the DOB field does not contain a time value, formats that display time (here including the default, 100, 114, and 126) supply a zero value, which represents 12:00AM (midnight). The code-126 format provides a date and time string that contains no spaces.
Only the InterSystems IRIS CONVERT() syntax supports date string formatting, not ODBC {fn CONVERT()} syntax.
Convert Bit Values
You can perform a BIT data type conversion. The permitted values are 1, 0, or NULL. If you specify any other value, InterSystems IRIS issues an SQLCODE -141 error. This example shows two BIT conversions of a NULL:
SET a=""
&sql(SELECT CONVERT(BIT,:a),
CONVERT(BIT,NULL)
INTO :x,:y)
WRITE !,"SQLCODE=",SQLCODE
WRITE !,"the host variable is:",x
WRITE !,"the NULL keyword is:",y
You can specify empty strings in bit conversion only when it is stored in a host variable using embedded SQL. If you specify an empty string directly, as in CONVERT(BIT,''), InterSystems IRIS issues an SQLCODE -141 error.