CONVERT (SQL) | InterSystems SQL Reference | InterSystems IRIS Data Platform 2025.2

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 Between Numeric Types

Convert Between Character Strings

Convert Stream Data to Character String

Convert Character String to Numeric Type

Convert Date to Timestamp

Convert Date to Numeric Type

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:

Convert Between Numeric Types

Convert Between Character Strings

Convert Character String to Numeric Type

Convert Date to Timestamp

Convert Date to Numeric 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 %String SQL_DATE, SQL_TIME, SQL_TIMESTAMP %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.

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.

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.

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")
© 2025 InterSystems Corporation, Boston, MA. All rights reserved.
PrivacyOpens in a new tab
 & TermsOpens in a new tab
GuaranteeOpens in a new tab
AccessibilityOpens in a new tab
Cookies SettingsCookie List
Content date/time: 2025-11-13 22:30:54