CONVERT (SQL)
Synopsis
CONVERT(type,expression)
CONVERT(type,expression,formatCcode)
{fn CONVERT(expression,type)}
Description
The CONVERT function converts an expression in one data type to a corresponding value in another data type. CONVERT is similar to CAST, with these differences:
-
CONVERT is more flexible than CAST. For example, CONVERT supports the conversion of stream data and enables formatting of date and time values.
-
CAST provides more database compatibility than CONVERT. Whereas CAST is implemented using the ANSI SQL-92 standard, CONVERT implementations are database-specific. InterSystems SQL provides CONVERT implementations that are compatible with MS SQL Server and ODBC.
MS SQL Server Compatibility
This implementation of CONVERT is a general InterSystems IRIS® scalar function that is compatible with MS SQL Server. This function supports the formatting of dates and times and the conversion of stream data.
-
CONVERT(type,expression) converts an expression to the specified data type. For a list of the data types supported by InterSystems SQL, see Data Types.
This statement converts a decimal number (an approximation of pi) to a character string, truncating the number to four characters.
SELECT CONVERT(CHAR(4),3.14159) -- '3.14'
Examples:
-
CONVERT(type,expression,formatCode) converts the expression to the specified data type and formats the returned value based on the specified format code.
This statement converts a date string to the TIMESTAMP data type. The function converts the input based on format code 103, which represents the mm/dd/yy format. For a complete list of format codes, see the formatCode argument.
SELECT CONVERT(TIMESTAMP,'1/1/99',103) -- '01/01/1999 00:00:00'
Example: Convert Date to Character String
ODBC Compatibility
This implementation of CONVERT is a general InterSystems IRIS ODBC scalar function. This function does not support the formatting of dates and times. It also does not support the conversion of stream data.
-
{fn CONVERT(expression,type)} converts the expression to the specified data type. In this implementation of CONVERT, you must precede each data type argument with the SQL_ keyword. These data types do not accept parameter. For example, for string data types, you cannot set a maximum length. For numeric data types, you cannot set the precision (maximum number of digits) and scale (maximum number of decimal digits).
This statement converts a decimal number to a character string. The returned string performs no truncation. Maximum length specifications such as SQL_VARCHAR(4) are not permitted.
SELECT {fn CONVERT(3.14159,SQL_VARCHAR) } -- '3.14159'
Examples:
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.
formatCode
An integer code that specifies date, datetime, and time formats.
Use formatCode to define the output when converting from a date/time/timestamp data type to a character string. For example:
SELECT CONVERT(VARCHAR,TO_DATE('22 FEB 2022'),1) -- '02/22/22'
You can also use formatCode to define the input when converting from a character string to a date/time/timestamp data type. For example:
SELECT CONVERT(DATE,'22 FEB 2022',106) -- '02/22/2022'
Only the CONVERT() syntax supports formatCode.
Specifying an expression with an invalid format or a format that does not match the formatCode generates an SQLCODE -141 error. Specifying a non-existent formatCode returns 1900-01-01 00:00:00.
This table describes the supported format codes, where:
-
The first column lists codes that output a two-digit year.
-
The second column lists code that output a four-digit year or do not output a year.
Two-Digit Year Codes | Four-Digit Year Codes | Format |
---|---|---|
n/a | 0 or 100 | Mon dd yyyy hh:mmAM (or PM) |
1 | 101 | mm/dd/yy |
2 | 102 | yy.mm.dd |
3 | 103 | dd/mm/yy |
4 | 104 | dd.mm.yy |
5 | 105 | dd-mm-yy |
6 | 106 | dd Mon yy |
7 | 107 | Mon dd, yy (no leading zero when dd < 10) |
n/a | 8 or 108 | hh:mm:ss |
n/a | 9 or 109 | Mon dd yyyy hh:mm:ss:nnnAM (or PM) |
10 | 110 | mm-dd-yy |
11 | 111 | yy/mm/dd |
12 | 112 | yymmdd |
n/a | 13 or 113 | dd Mon yyyy hh:mm:ss:nnn (24 hour) |
n/a | 14 or 114 | hh:mm:ss.nnn (24 hour) |
n/a | 20 or 120 | yyyy-mm-dd hh:mm:ss (24 hour) |
n/a | 21 or 121 | yyyy-mm-dd hh:mm:ss.nnn (24 hour) |
n/a | 126 | yyyy-mm-ddThh:mm:ss.nnn (24 hour) |
n/a | 130 | dd Mon yyyy hh:mm:ss:nnnAM (or PM) |
n/a | 131 | dd/mm/yyyy hh:mm:ss:nnnAM (or PM) |
Range of Values
The range of permitted dates is 0001-01-01 through 9999-12-31.
Default Values
For the CONVERT() syntax, when converting a time value to TIMESTAMP, POSIXTIME, DATETIME, or SMALLDATETIME, the date defaults to 1900-01-01. For the {fn CONVERT()} syntax, the date defaults to 1841-01-01.
When converting a date value to TIMESTAMP, POSIXTIME, DATETIME, or SMALLDATETIME, the time defaults to 00:00:00.
Default Format
If you do not specify formatCode, CONVERT tries to determine the format from the specified value. If it cannot, it defaults to formatCode 100 (mm-dd-yy).
Two-Digit Years
Two-digit years from 00 through 49 are converted to 21st century dates (2000 through 2049).
Two-digit years from 50 through 99 are converted to 20th century dates (1950 through 1999).
Fractional Seconds
You can precede fractional seconds by either a period (.) or a colon (:). The symbols have different meanings:
-
Period (default) — Valid for all formatCode values. A period indicates a standard fraction. For example, 12:00:00.4 indicates four-tenths of a second and 12:00:00.004 indicates four-thousandth of a second. CONVERT has no limit on the number of digits of fractional precision.
-
Colon — Valid only for formatCode values 9/109, 13/113, 14/114, 130, and 131. A colon indicates that the number that follows is in thousandths of a second. For example, 12:00:00:4 indicates four-thousandth of a second (12:00:00.004). You can specify a maximum of three digits of fractional precision.
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 Character String to Numeric Type
This example converts a mixed string to an integer. InterSystems IRIS truncates the string at the first nonnumeric character and then converts the resulting numeric to canonical form:
SELECT CONVERT(INTEGER,'007 James Bond') -- 7
SELECT {fn CONVERT('007 James Bond',SQL_INTEGER)} -- 7
Convert Date to Timestamp
This example converts dates in the "DOB" (Date Of Birth) column to timestamp data types. The resulting timestamp is in the format yyyy-mm-dd hh:mm:ss.
SELECT DOB,CONVERT(TIMESTAMP,DOB) AS DOBtoTstamp
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_TIMESTAMP)} AS DOBtoTstamp
FROM Sample.Person
Convert Date to Numeric Type
This example converts dates in the "DOB" (Date Of Birth) column to integer data types. The resulting integer is the $HOROLOG count of days since December 31, 1840.
SELECT DOB,CONVERT(INTEGER,DOB) AS DOBtoInt
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_INTEGER)} AS DOBtoInt
FROM Sample.Person
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.
More About
CONVERT Class Method
You can also perform data type conversions using the CONVERT()Opens in a new tab method call, specifying data types as SQL_ keywords, as shown in this syntax:
$SYSTEM.SQL.Functions.CONVERT(expression,SQL_convertToType,SQL_convertFromType)
For example:
write $SYSTEM.SQL.Functions.CONVERT(66225,"SQL_VARCHAR","SQL_DATE")