CAST (SQL)

A function that converts a given expression to a specified data type.

Synopsis

Character Strings

``````CAST(expression AS [ CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR ])

CAST(expression AS [ CHAR VARYING | CHARACTER VARYING ])

CAST(expression AS
[ CHAR(length) | CHARACTER(length) |
VARCHAR(length) | CHAR VARYING(length) |
CHARACTER VARYING(length)])
``````
Numeric Values

``````CAST(expression AS [INT | INTEGER | BIGINT | SMALLINT | TINYINT])

CAST(expression AS [DEC | DECIMAL | NUMERIC])

CAST(expression AS
[ DEC(precision,scale) |
DECIMAL(precision,scale) |
NUMERIC(precision,scale) ])

CAST(expression AS DOUBLE)

CAST(expression AS [MONEY | SMALLMONEY])
``````
Dates and Times

``````CAST(expression AS DATE)

CAST(expression AS TIME)

CAST(expression AS [TIMESTAMP | DATETIME | SMALLDATETIME])

CAST(expression AS POSIXTIME)
``````
Bit Values

``````CAST(expression AS BIT)
``````
Binary Values

``````CAST(expression AS [BINARY | BINARY VARYING | VARBINARY])

CAST(expression AS [BINARY(length) |
BINARY VARYING(length) |
VARBINARY(length)])
``````
Unique Identifiers

``````CAST(expression AS GUID)
``````

Description

The SQL CAST function converts the data type of an expression to the specified data type. For a list of the data types supported by InterSystems SQL, see Data Types.

CAST is similar to CONVERT, 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.

If you specify a CAST with an unsupported data type, InterSystems IRIS® issues an SQLCODE -376.

Character Strings

• CAST(expression AS [CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR]) converts a numeric or string expression to a character string data type. These data types all map to %Library.StringOpens in a new tab.

• CHAR, CHARACTER, and NCHAR are equivalent data types and have a default length of 1 character.

• VARCHAR and NVARCHAR are equivalent data types and have a default length of 30 characters.

This statement returns Name (a character string), Age (a numeric value) and DOB (a date value) as VARCHAR data types.

``````SELECT DISTINCT
CAST(Name AS VARCHAR) AS VarCharName,
CAST(Age AS VARCHAR) AS VarCharAge,
CAST(DOB AS VARCHAR) AS VarCharDOB
FROM Sample.Person``````

This statement casts a string value to a single character string, truncating the additional letters in the original string.

``SELECT CAST('True' AS CHAR) -- T``

Example: Cast Character String Values

• CAST(expression AS [CHAR VARYING | CHARACTER VARYING]) converts the expression and returns the same number of characters in the original value.

This statement returns the floating-point representation of pi as a string value. The string has the same number of characters as digits in the floating-point precision of pi.

``````SELECT CAST({fn PI()} AS CHAR VARYING) AS StringPi -- 3.141592653589793238
``````

Example: Cast Character String Values

• CAST(expression AS [CHAR(length) | CHARACTER(length) | VARCHAR(length) | CHAR VARYING(length) | CHARACTER VARYING(length)]) converts the expression to a character string with the number of characters specified by length. Additional characters are truncated.

This statement returns a string containing the first 8 characters of the input string expression.

``SELECT CAST('Grabscheid,Alfred N.' AS CHAR(8)) -- Grabsche``

Example: Cast Character String Values

Numeric Values

• CAST(expression AS [INT | INTEGER | BIGINT | SMALLINT | TINYINT]) converts the expression to the INT, INTEGER, BIGINT, SMALLINT, or TINYINT data type. In these data types, decimal digits are truncated.

This example presents an average as an integer, not a floating point. CAST truncates the number, so an average age of 42.9 becomes 42.

``````SELECT DISTINCT AVG(Age) AS AvgAge,
CAST(AVG(Age) AS INTEGER) AS IntAvgAge
FROM Sample.Person``````

Example: Cast Numeric Values

• CAST(expression AS [DEC | DECIMAL | NUMERIC]) converts the expression to the DEC, DECIMAL, or NUMERIC data types. These data types preserve the number of digits in the original value. InterSystems IRIS converts these data types using \$DECIMAL function, which converts \$DOUBLE values to \$DECIMAL values. These data types map to the %Library.NumericOpens in a new tab data type.

This statement returns the sin of 1 radian, a floating point value, as a decimal.

``SELECT CAST({fn SIN(1)} AS DECIMAL) AS DecimalValue -- 0.841470984807897``

Example: Cast Numeric Values

• CAST(expression AS [DEC(precision,scale) | DECIMAL(precision,scale) | NUMERIC(precision,scale)]) specifies the precision and scale of the data type.

• precision specifies the total number of digits that a data type can specify. If specified, precision does not affect the value returned by CAST but it is retained as part of the defined type.

• scale specifies the total number of decimal digits in the data type. CAST rounds numbers to this specified value.

This statement returns the sin of 1 radian as a decimal value, with four digits after the decimal point.

``SELECT CAST({fn SIN(1)} AS DECIMAL(8,4)) AS ScaledDecimalValue -- 0.8415``

Example: Cast Numeric Values

• CAST(expression AS DOUBLE) converts the expression to the DOUBLE data type, which follows the IEEE floating point standard. For further details, refer to the ObjectScript \$DOUBLE function.

This statement returns the sin of 1 radian as a double value.

``SELECT CAST({fn SIN(1)} AS DOUBLE) AS DoubleValue -- .84147098480789650487``

Example: Cast Numeric Values

• CAST(expression AS [MONEY | SMALLMONEY]) converts the expression to a currency numeric data type: MONEY or SMALLMONEY. The scale for currency data types is always 4.

This statements returns the integer 10 as a currency value.

``````SELECT CAST(10 AS MONEY) AS MoneyValue -- 10.0000 (Display Mode)
``````

Dates and Times

• CAST(expression AS DATE) converts a formatted date expression to the DATE date type. InterSystems IRIS represents dates in these formats, depending on context:

• The display date format for your locale (for example, mm/dd/yyyy)

• The ODBC date format (yyyy-mm-dd)

You must specify the \$HOROLOG date part value as an integer, not a numeric string.

This statement casts a character string to the DATE data type.

``SELECT CAST('1936-11-26' As DATE) AS StringToDate``
• CAST(expression AS TIME) converts a formatted time expression to the TIME data type. InterSystems IRIS represents times in these formats, depending on context:

• The display time format for your locale (for example, hh:mm:ss)

• The ODBC date format (hh:mm:ss)

You must specify the \$HOROLOG date part value as an integer, not a numeric string.

This statement casts a character string to the TIME data type.

``SELECT CAST('14:33:45.78' AS TIME) AS StringToTime``
• CAST(expression AS [TIMESTAMP | DATETIME | SMALLDATETIME]) represents a date and timestamp with the format YYYY-MM-DD hh:mm:ss.nnn. This value corresponds to the ObjectScript \$ZTIMESTAMP special variable.

This statement casts a date and time string to the TIMESTAMP data type.

``SELECT CAST('November 26, 1936 14:33:45.78' AS TIMESTAMP) AS StringToTS``
• CAST(expression AS POSIXTIME) converts an expression representing a date and timestamp to an encoded 64-bit signed integer. For more details on this encoding format, see Date, Time, PosixTime, and TimeStamp Data Types.

This statement casts a date and time string to the POSIXTIME data type.

``````SELECT CAST('November 26, 1936 14:33:45.78' AS POSIXTIME) AS StringToPosix
``````

Example: Cast Date to POSIXTIME

Bit Values

• CAST(expression AS BIT) converts the expression to a single boolean value of data type BIT.

This statement returns BIT values of 1 and 0, respectively.

``````SELECT
CAST('1' AS BIT) As BitTrue,
CAST('0' AS BIT) As BitFalse``````

Example: Cast Bit Values

Binary Values

• CAST(expression AS [BINARY | BINARY VARYING | VARBINARY]) converts the expression to one of three data types that map to %Library.BinaryOpens in a new tab (SQLType data type BINARY).

• BINARY has a default length of 1

• BINARY VARYING and VARBINARY have a default length of 30.

When casting to a binary value, CAST does not convert the data but it does truncate the length of the value to the specified length.

• CAST(expression AS [BINARY(length) | BINARY VARYING(length) | VARBINARY(length)]) sets the maximum character length of the returns binary data type.

Arguments

expression

An SQL expression, commonly a literal or a data field of a table, that is being converted.

length

An integer indicating the maximum number of characters to return after casting.

• If length is less than the length of expression, the returned data is truncated to the first length characters.

• If length is greater than the length of expression, CAST performs no truncation or padding.

precision

Maximum number of total digits returned in the cast data type, specified as an integer. precision is retained as part of the defined data type but does not affect the value returned by CAST.

For more details about precision, see Precision and Scale.

scale

Maximum number of decimal digits returned in the cast data type, specified as an integer. CAST rounds the returned value to scale number of digits.

• If you specify scale = 0, the numeric value is rounded to an integer.

• If you specify scale = -1, the numeric value is truncated to an integer.

• If you do not specify scale, the scale of the numeric value defaults to 15.

If scale is greater than the number of digits in the value being cast, the returned value displays the appropriate number of trailing zeros for Display mode but truncates these digits for Logical and ODBC mode.

For more details about scale, see Precision and Scale.

Examples

Cast Character String Values

You can cast character strings to various numeric, date, time, and character string values.

Cast Between Character Strings

When you cast a character string to another character data type, returning either a single character, the first length characters, or the entire character string.

``````SELECT
CAST('Hello World' AS CHAR) AS StringToChar, -- H
CAST('Hello World' AS CHAR(5)) AS StringToCharLength, -- Hello
CAST('Hello World' AS CHAR VARYING) AS StringToCharVary -- Hello World``````

Before a cast is performed, InterSystems SQL resolves embedded quote characters and string concatenation. Leading and trailing blanks are retained.

``````SELECT
CAST('Can''t' AS VARCHAR) AS EmbeddedQuote, -- Can't
CAST('Can'||'not' AS VARCHAR) AS StringConcatenation -- Cannot``````

Cast Character String to Numeric Type

When you cast a character string to a numeric type, InterSystems SQL returns the single digit zero (0).

``````SELECT CAST('Hello World' AS DOUBLE) AS StringToNumeric -- 0
``````

This example shows what happens when you use the CAST function to convert Name (a character string) to different numeric data types. In every case, the value returned is 0 (zero).

``````SELECT DISTINCT
CAST(Name As INT) As IntName,
CAST(Name As SMALLINT) As SmallIntName,
CAST(Name As DEC) As DecName,
CAST(Name As NUMERIC) As NumericName
FROM Sample.Person``````

Cast Formatted Character String to Date

You can cast strings of the format 'yyyy-mm-dd' to the DATE data type. This string format corresponds to ODBC date format. InterSystems SQL performs value and range checking on the input expression, where:

• The year (yyyy) must be between 00001 and 9999 (inclusive).

• The month (mm) must be between 01 and 12 (inclusive).

• The day (dd) must be valid for that month.

InterSystems SQL inserts any missing leading zeros. For example:

``SELECT CAST('2022-3-1' AS DATE) AS DateValue -- 03/01/2022 (Display Mode)``

An invalid date returns 1840-12-31 (logical date 0). For example, 2/29 is valid only on leap days.

``SELECT CAST('2021-02-29' AS DATE) AS InvalidDate -- 12/31/1840 (Display Mode)``

The display mode and the locale's date display format determines the display of the cast. For example, '2004-11-23' might display as '11/23/2004'.

Embedded SQL returns the cast as the corresponding \$HOROLOG date integer. An invalid ODBC date or a non-numeric string is represented as 0 in logical mode when cast to DATE. Date 0 is displayed as 1840-12-31.

Cast Formatted Character String to Time

You can cast strings of the format 'hh:mm', 'hh:mm:ss' or 'hh:mm:ss.nn', with any number of length fractional second digits, to the TIME data type. This string format corresponds to ODBC time format. InterSystems SQL performs value and range checking on the input expression, where:

• The hour (hh) must be from 00 to 23 (inclusive).

• The minute (mm) must be from 00 to 59 (inclusive).

• The day (ss) must be from 00 and to up but not including 60. Fractional seconds are permitted but truncated.

InterSystems SQL adds missing zeros. For example:

``````SELECT
CAST('2:45' AS TIME) AS StringToTime1, -- 02:45:00 (Display Mode)
CAST('2:45:59' AS TIME) AS StringToTime2, -- 02:45:59 (Display Mode)
CAST('2:45:59.98' AS TIME) AS StringToTime3 -- 02:45:59.98 (Display Mode)``````

An invalid time returns 00:00:00 (logical time 0).

``SELECT CAST('11:52:60' AS TIME) AS InvalidTime -- 00:00:00 (Display Mode)``

Embedded SQL returns the cast as the corresponding \$HOROLOG time integer. An invalid ODBC time or a non-numeric string is represented as 0 in logical mode when cast to TIME. Time 0 is displayed as 00:00:00.

Cast Formatted Character String to Timestamp

You can cast a string consisting of a valid date and time, a valid date, or a valid time to the TIMESTAMP data type. The date portion can be in a variety of formats, as described in the TO_TIMESTAMP function. The resulting timestamp is in the format: YYYY-MM-DD hh:mm:ss.

``````SELECT
CAST('1 MAR 2022 1:33pm' AS TIMESTAMP) As DateToTS1, -- 2022-03-01 13:33:00
CAST('3/1/2022 13:33:00' AS TIMESTAMP) As DateToTS2 -- 2022-03-01 13:33:00 ``````

CAST resolves formatted dates as follows:

• Set the date portion (if omitted) to 1841-01-01 (logical date 1).

• Set the time portion (if omitted) to 00:00:00.

• Insert leading zeros (if omitted) for the month and day.

You can precede fractional seconds (if specified) with either a period (.) or a colon (:).

• A period indicates a standard fraction. For example:

• 12:00:00.4 = four-tenths of a second

• 12:00:00.004 = four-thousandths of a second

• A colon indicates that what follows is in thousandths of a second. 12:00:00:4 indicates four-thousandths of a second. The permitted number of digits following a colon is limited to three.

You can also cast a character string from one time data type to another. This example casts a character string to the TIME data type, then casts the resulting time to the TIMESTAMP data type. The date is set to the current system date.

``SELECT CAST(CAST('14:33:45.78' AS TIME) As TIMESTAMP) AS TimeToTstamp``

Cast Date Values

You can cast a date to a character string data type, numeric data type, or to another date data type.

Cast Date to Character String

Casting a date to a character data type returns either the complete date or as much of the date as the length of the data type permits. For example, instead of returning the current date as "yyyy-mm-dd", this query returns only "yyyy-".

``SELECT CAST(CURRENT_DATE AS CHAR(5)) AS TruncatedDate``

The CHAR VARYING and CHARACTER VARYING data types return the complete display format.

``SELECT CAST(CURRENT_TIME AS CHAR VARYING) AS FullDate``

If a date displays in a different format, such as mm/dd/yyyy, character string data types return the date in ODBC date format (yyyy-mm-dd). For example:

``SELECT CAST(TO_DATE('03/01/2022','MM/DD/YYYY') AS VARCHAR) AS DateFormat -- 2022-03-01``

Cast Date to Numeric Type

Casting a date to a numeric data type returns the \$HOROLOG value for the date. This is an integer value representing the number of days since Dec. 31, 1840. For example:

``SELECT CAST(TO_DATE('01 MAR 2022') AS DECIMAL) AS DateToNumeric -- 66169``

Cast Date to POSIXTIME

Casting a date to the POSIXTIME data type returns a timestamp as an encoded 64-bit signed integer. Since a date does not have a time portion, the time portion is supplied to the timestamp encoding as 00:00:00.

``````SELECT CAST(CURRENT_DATE AS POSIXTIME) As PosixDate
``````

CAST performs date validation. If the expression value is not a valid date, it issues an SQLCODE -400 error.

Cast Date to TIMESTAMP, DATETIME, or SMALLDATETIME

Casting a date to the TIMESTAMP, DATETIME, or SMALLDATETIME data type returns a timestamp of the format YYYY-MM-DD hh:mm:ss. Since a date does not have a time portion, the time portion of the resulting timestamp is always 00:00:00. CAST performs date validation. If the expression value is not a valid date, it issues an SQLCODE -400 error.

This example casts a DATE data type column to TIMESTAMP. The POSIXTIME data type is included for comparison.

``````SELECT TOP 5
DOB,
CAST(DOB AS TIMESTAMP) AS TStamp,
CAST(DOB AS POSIXTIME) AS Posix
FROM Sample.Person
``````

Cast Numeric Values

You can cast numeric values to a numeric or character data type. When casting a numeric value results in a shortened value, the numeric is truncated, not rounded.

``````SELECT
CAST(98.765 AS INT) AS TruncatedInt, -- 98
CAST(98.765 AS CHAR) AS TruncatedChar1, -- 9
CAST(98.765 AS CHAR(4)) AS TruncatedChar2 --98.7``````

Casting a negative number to CHAR returns just the negative sign. Casting a fractional number to CHAR returns just the decimal point.

``````SELECT
CAST(-50 AS CHAR) AS Negative, -- negative sign: -
CAST(1/4 AS CHAR) AS Fraction -- decimal point: .``````

A numeric value can contain these values:

• Digits 0 through 9

• A decimal point

• One or more leading signs (+ or –)

• The exponent sign (the letter E or e) followed by, at most, one + or – sign

Before a cast is performed, InterSystems SQL resolves a numeric to its canonical form by performing exponentiation, resolving multiple signs, and stripping the leading plus sign, trailing decimal point, and any leading or trailing zeros. For example:

``````SELECT
CAST(1e2 AS DECIMAL(6,2)) AS PositiveExponent, -- 100.000
CAST(1e-2 AS DECIMAL(6,2)) AS NegativeExponent, -- 0.01
CAST(+1000 AS DECIMAL(6,2)) AS LeadingSign, -- 1000.00
CAST(-+1000 AS DECIMAL(6,2)) AS MultipleSigns, -- -1000.00
CAST(00.100 AS DECIMAL(6,2)) AS LeadingTrailingZeros -- 0.10``````

InterSystems SQL treats double negative signs as a comment indicator. Encountering double negative signs in a number results in InterSystems IRIS processing the remainder of that line of code as a comment. A numeric cannot contain group separator characters (commas). For more details, see Literals.

You can convert numeric values to a variety of numeric types. This example shows how CAST converts a floating point number, pi, to different numeric data types. For the integer data types, InterSystems SQL applies truncation.

``````SELECT
CAST({fn PI()} As INTEGER) As IntegerPi, -- 3
CAST({fn PI()} As SMALLINT) As SmallIntPi, -- 3
CAST({fn PI()} As DECIMAL) As DecimalPi, -- 3.141592653589793
CAST({fn PI()} As NUMERIC) As NumericPi, -- 3.141592653589793
CAST({fn PI()} As DOUBLE) As DoublePi -- 3.1415926535897931159``````

In this example, InterSystems IRIS parses the precision and scale values and changes the value returned by CAST.

``````SELECT
CAST({fn PI()} As DECIMAL) As DecimalPi, -- 3.141592653589793
CAST({fn PI()} As DECIMAL(6,3)) As DecimalPiPS -- 3.142``````

When a numeric value is cast to a date or time data type, it displays in SQL as zero (0). When a numeric that is cast as a date or time is passed out of embedded SQL to ObjectScript, it displays as the corresponding \$HOROLOG value.

Cast Bit Values

To return expression as a 0 or 1, you can cast it to a BIT value.

CAST returns 1 (true) when expression is one of these values:

• The number 1 or any other non-zero numeric value.

• The word "TRUE", "True", "true", or any other combination of uppercase and lowercase letters that spell the word true. It cannot be abbreviated to "T".

These CAST operations all return 1.

``````SELECT CAST(1 AS BIT) AS One,
CAST(7 AS BIT) AS Num,
CAST(743.6 AS BIT) AS Frac,
CAST(0.3 AS BIT) AS Zerofrac,
CAST('tRuE' AS BIT) AS TrueWord
``````

CAST returns 0 (false) when expression is one of these values:

• Any non-numeric value other than the word true and its various uppercase and lowercase combinations.

• The empty string ('').

• The number 0.

These CAST operations all return 0.

``````SELECT CAST(0 AS BIT) AS Zero,
CAST('FALSE' AS BIT) AS FalseWord,
CAST('T' AS BIT) AS T,
CAST('F' AS BIT) AS F,
CAST(0.0 AS BIT) AS Zerodot,
CAST('' AS BIT) AS EmptyString``````

Cast NULL and Empty String Values

Casting NULL to any data type returns NULL.

``SELECT CAST(NULL AS DATE) AS NullValue``

The result of casting an empty string ('') depends on the data type.

Data Type Return Value of Empty String
Character data types Empty string ('')
Numeric data type 0 (zero), with the appropriate number of trailing fractional zeros. The DOUBLE data type returns zero with no trailing fractional zeros.
DATE data type 12/31/1840 (logical date 0)
TIME data type 00:00:00 (logical time 0)
TIMESTAMP, DATETIME, and SMALLDATETIME data types Empty string ('')
BIT data type 0
All binary data types Empty string ('')