CAST (SQL)

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)

  • The $HOROLOG integer date storage format (nnnnn)

  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

  

  Example: Cast Formatted Character String to Date

• 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)

  • The $HOROLOG integer time storage format (nnnnn)

  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

  

  Example: Cast Formatted Character String to Time

• 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

  

  Example: Cast Formatted Character String to Timestamp

• 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.

Unique Identifiers

• CAST(expression AS GUID) GUID represents a 36-character value of data type %Library.UniqueIdentifierOpens in a new tab. If you supply an expression longer than 36 characters, CAST returns the first 36 characters of expression. To generate a GUID value, use the %SYSTEM.Util.CreateGUID()Opens in a new tab method.

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.

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.