Skip to main content
Previous sectionNext section

CAST

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

Synopsis

CAST(expr AS CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR)
CAST(expr AS CHAR(n) | CHARACTER(n) | VARCHAR(n))
CAST(expr AS CHAR VARYING | CHARACTER VARYING)
CAST(expr AS INT | INTEGER | BIGINT | SMALLINT | TINYINT)
CAST(expr AS DEC | DECIMAL | NUMERIC)
CAST(expr AS DEC(p[,s]) | DECIMAL(p[,s]) | NUMERIC(p[,s])
CAST(expr AS DOUBLE)
CAST(expr AS MONEY | SMALLMONEY)
CAST(expr AS DATE)
CAST(expr AS TIME)
CAST(expr AS POSIXTIME)
CAST(expr AS TIMESTAMP | DATETIME | SMALLDATETIME)
CAST(expr AS BIT)
CAST(expr AS BINARY | BINARY VARYING | VARBINARY)
CAST(expr AS BINARY(n) | BINARY VARYING(n) | VARBINARY(n))
CAST(expr AS GUID)

Arguments

expr An SQL expression, commonly a literal or a data field of a table.
n An integer, indicating the maximum number of characters to return. If n is smaller than the expr data, the returned data is truncated to n characters. If n is larger than the expr data, no padding is performed.
p,s Optionalp=Precision (maximum number of total digits), expressed as an integer. s=Scale (maximum number of decimal digits), expressed as an integer. If scale is not specified, it defaults to 15.

Description

The SQL CAST function converts the data type of an expression to the specified data type. CAST can convert the data type of expr when that data type is a standard data type or a subclass of a standard data type such as %Library.String, %Library.Time, %Library.Date, or %Library.TimeStamp.

You can cast expr to any of the following data types:

  • CHAR or CHARACTER: represent a numeric or a string by its initial character. VARCHAR with no n defaults to a length of 30 characters when specified to CAST or CONVERT. Otherwise, the VARCHAR data type (with no specified size) is mapped to a MAXLEN of 1 character, as shown in the Data Types table. NCHAR is equivalent to CHAR; NVARCHAR is equivalent to VARCHAR.

  • CHAR(n), CHARACTER(n), or VARCHAR(n): represent a numeric or a string by the number of characters specified by n.

  • CHAR VARYING or CHARACTER VARYING: represent a numeric or a string by the number of characters in the original value.

  • INT, INTEGER, BIGINT, SMALLINT, and TINYINT: represent a numeric by its integer portion. Decimal digits are truncated.

  • DEC, DECIMAL, and NUMERIC: represent a numeric by the number of digits in the original value. Converts using the InterSystems IRIS $DECIMAL function, which converts $DOUBLE values to $DECIMAL values. The p (precision), if specified, is retained as part of the defined data type, but does not affect the value returned by CAST. If you specify a s (scale) value of a positive integer, the decimal value is rounded to the specified number of digits. (The appropriate number of trailing zeros are included for Display mode, but are truncated for Logical mode and ODBC mode.) If you specify s=0, the numeric value is rounded to an integer. If you specify s=-1, the numeric value is truncated to an integer.

  • DOUBLE represents the IEEE floating point standard. For further details, refer to the ObjectScript $DOUBLE function.

  • MONEY and SMALLMONEY are currency numeric data types. The scale for currency data types is always 4.

  • DATE: represents a date. Dates can be represented in any of the following formats, depending on context: the display date format for your locale (for example, MM/DD/YYYY); the ODBC date format (YYYY-MM-DD); or the $HOROLOG integer date storage format (nnnnn). You must specify the $HOROLOG date part value as an integer, not a numeric string.

  • TIME: represents a time. Times can be represented in any of the following formats, depending on context: the display time format for your locale (for example, hh:mm:ss); the ODBC date format (hh:mm:ss); or the $HOROLOG integer time storage format (nnnnn). You must specify the $HOROLOG time part value as an integer, not a numeric string.

  • POSIXTIME: represents a date and time stamp as an encoded 64-bit signed integer. Refer to Date, Time, PosixTime, and TimeStamp Data Types in Data Types for details.

  • TIMESTAMP, DATETIME, and SMALLDATETIME: represents a date and time stamp with the format YYYY-MM-DD hh:mm:ss.nnn. This corresponds to the ObjectScript $ZTIMESTAMP special variable.

  • BIT represents a single binary value.

  • BINARY, BINARY VARYING, and VARBINARY represent a value of data type %Library.Binary (xDBC data type BINARY). The optional n length defaults to 1 for BINARY, 30 for BINARY VARYING and VARBINARY. No conversion of the data is actually performed when casting to a binary value. InterSystems IRIS does truncate the length of the value at the specified n length.

  • GUID represents a 36-character value of data type %Library.UniqueIdentifier. If you supply an expr longer than 36 characters, CAST returns the first 36 characters of expr. To generate a GUID value, use the %SYSTEM.Util.CreateGUID() method.

For a list of the data types supported by InterSystems SQL, see Data Types. For other data type conversions, refer to the CONVERT function. If you specify a CAST with an unsupported data type, InterSystems IRIS issues an SQLCODE -376.

Casting Numerics

A numeric value can be cast to a numeric data type or to a character data type.

When casting a numeric results in a shortened value, the numeric is truncated, not rounded. For example, casting 98.765 to INT returns 98, to CHAR returns 9, and to CHAR(4) returns 98.7. Note that casting a negative number to CHAR returns just the negative sign, and casting a fractional number to CHAR returns just the decimal point.

A numeric value can consist of the digits 0 through 9, a decimal point, one or more leading signs (+ or –), and the exponent sign (the letter E or e) followed by, at most, one + or – sign. A numeric cannot contain group separator characters (commas). For further details, see the literals section of “Language Elements” in Using InterSystems SQL.

Before a cast is performed, InterSystems SQL resolves a numeric to its canonical form: Exponentiation is performed. InterSystems IRIS strips leading and trailing zeros, a leading plus sign, and a trailing decimal point. Multiple signs are resolved before casting a numeric. However, 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.

An InterSystems IRIS floating point number can take a DEC, DECIMAL, or NUMERIC data type. The DOUBLE data type represents floating point numbers according to the IEEE floating point standard. The InterSystems IRIS floating point data types have greater precision than the DOUBLE data type, and are preferable for most applications. You cannot use CAST to cast a floating point number to the DOUBLE data type; instead, use the ObjectScript $DOUBLE function.

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

Casting a Character String

You can cast a character string to another character data type, returning either a single character, the first n characters, or the entire character string.

Before a cast is performed, InterSystems SQL resolves embedded quote characters ('can''t'=can't) and string concatenation ('can'||'not'=cannot). Leading and trailing blanks are retained.

When a character string is cast to a numeric type, it always returns the single digit zero (0).

Casting to DATE, TIME, and TIMESTAMP

You can cast a character string to the DATE, TIME, or TIMESTAMP data type. The following operations result in a valid value:

  • DATE: A string of the format 'yyyy-mm-dd' can be cast to DATE. This string format corresponds to ODBC date format. Value and range checking are performed. The year must be between 0001 and 9999 (inclusive), the month 01 through 12, and the day appropriate for that month (for example, 02/29 is only valid on leap years). An invalid date, such as 2013–02–29 returns 1840–12–31 (logical date 0).

    Missing leading zeros in month and day fields are added. How this cast is displayed depends on the display mode and the locale's date display format. For example, '2004–11–23' might display as '11/23/2004'. In Embedded SQL, this cast is returned 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.

  • TIME: A string of the format 'hh:mm', 'hh:mm:ss' or 'hh:mm:ss.nn' (with any number of n fractional second digits) can be cast to TIME. This string format corresponds to ODBC time format. Value and range checking are performed. Missing leading zeros are added. In embedded SQL, this cast is returned 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.

  • TIMESTAMP: A string consisting of a valid date and time, a valid date, or a valid time can be cast to TIMESTAMP. The date portion can be in a variety of formats. A missing date portion defaults to 1841–01–01. A missing time portion defaults to 00:00:00. Missing leading zeros are added for month and day. Fractional seconds (if specified) 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.

Casting NULL and the Empty String

NULL can be cast to any data type and returns NULL.

The empty string ('') casts as follows:

  • All character data types return NULL.

  • All numeric data types return 0 (zero), with the appropriate number of trailing fractional zeros. The DOUBLE data type returns zero with no trailing fractional zeros.

  • The DATE data type returns 12/31/1840.

  • The TIME data type returns 00:00:00.

  • The TIMESTAMP, DATETIME, and SMALLDATETIME data types return NULL.

  • The BIT data type returns 0.

  • All binary data types return NULL.

Casting Dates

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

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. CAST performs date validation; if the expr value is not a valid date, it issues an SQLCODE -400 error.

Casting a date to the TIMESTAMP, DATETIME, or SMALLDATETIME data type returns a timestamp with 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 expr value is not a valid date, it issues an SQLCODE -400 error.

The following Dynamic SQL example casts a field of DATE data type to TIMESTAMP and POSIXTIME:

  SET myquery=2
  SET myquery(1)="SELECT TOP 5 DOB,CAST(DOB AS TIMESTAMP) AS TStamp,"
  SET myquery(2)="CAST(DOB AS POSIXTIME) AS Posix FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
Copy code to clipboard

The following Dynamic SQL example casts a field of TIMESTAMP data type to DATE and POSIXTIME:

  SET myquery=2
  SET myquery(1)="SELECT TOP 5 EventDate,CAST(EventDate AS DATE) AS Horolog,"
  SET myquery(2)="CAST(EventDate AS POSIXTIME) AS Posix FROM Aviation.Event"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
Copy code to clipboard

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.

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. However, the display format is not the same for all character data types. The CHAR VARYING and CHARACTER VARYING data types return the complete date in display format. For example, if a date displays as MM/DD/YYYY, these data types return the date as a character string with the same format. The other character data types return the date (or a part thereof) as a character string in ODBC date format. For example, if a date displays as mm/dd/yyyy, these data types return the date as a character string with the format YYYY-MM-DD. Thus for the date 04/24/2004, the CHAR data type returns '2' (the first character of the year), and a CHAR(8) returns '2004–04–'.

Casting a Bit Value

You can cast an expr value AS BIT to return a 0 or 1. If expr is 1 or any other non-zero numeric value, it returns 1. If expr is “TRUE”, “True”, or “true”, it returns 1. (The word “True” can be represented in any combination of uppercase and lowercase, but cannot be abbreviated as “T”.) If expr is any other non-numeric value, it returns 0. If expr is 0, it returns 0.

In the following example, the first five CAST operations return 1, the second five CAST operations return 0:

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(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     
Copy code to clipboard

Examples

The following example uses the CAST function to present an average as an integer, not a floating point. Note that the CAST truncates the number, rather than rounding it:

SELECT DISTINCT AVG(Age) AS AvgAge,
   CAST(AVG(Age) AS INTEGER) AS IntAvgAge
      FROM Sample.Person
Copy code to clipboard

The following example shows how the CAST function converts pi (a floating point number) to different numeric data types:

SELECT 
   CAST({fn PI()} As INTEGER) As IntegerPi,
   CAST({fn PI()} As SMALLINT) As SmallIntPi,
   CAST({fn PI()} As DECIMAL) As DecimalPi,
   CAST({fn PI()} As NUMERIC) As NumericPi,
   CAST({fn PI()} As DOUBLE) As DoublePi
Copy code to clipboard

Note in the following example that the precision and scale values are parsed, but do not change the value returned by CAST:

SELECT 
   CAST({fn PI()} As DECIMAL) As DecimalPi,
   CAST({fn PI()} As DECIMAL(6,3)) As DecimalPSPi
Copy code to clipboard

The following example shows how the CAST function converts pi (a floating point number) to different character data types:

SELECT 
   CAST({fn PI()} As CHAR) As CharPi,
   CAST({fn PI()} As CHAR(4)) As CharNPi,
   CAST({fn PI()} As CHAR VARYING) As CharVaryingPi,
   CAST({fn PI()} As VARCHAR(4)) As VarCharNPi
Copy code to clipboard

The following example shows how the CAST function converts Name (a character string) to different character data types:

SELECT DISTINCT 
   CAST(Name As CHAR) As CharName,
   CAST(Name As CHAR(4)) As CharNName,
   CAST(Name As CHAR VARYING) As CharVaryingName,
   CAST(Name As VARCHAR(4)) As VarCharNName
      FROM Sample.Person
Copy code to clipboard

The following example shows what happens when you use the CAST function to converts 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
Copy code to clipboard

The following example casts a date field (DOB) to a numeric data type and several character data types. Casting a date to a numeric returns the $HOROLOG integer equivalent. Casting a date to a character data type returns either a date string in input format (CHAR VARYING or CHARACTER VARYING) or the date (partial or full) in ODBC date string format:

SELECT DISTINCT DOB,
   CAST(DOB As INT) AS IntDate,
   CAST(DOB As CHAR) AS CharDate,
   CAST(DOB As CHAR(6)) AS CharNDate,
   CAST(DOB As CHAR VARYING) AS CharVaryDate,
   CAST(DOB As VARCHAR(10)) AS VarCharNDate
      FROM Sample.Person
Copy code to clipboard

The following example casts character strings to the DATE and TIME data types:

SELECT CAST('1936-11-26' As DATE) AS StringToDate,
       CAST('14:33:45.78' AS TIME) AS StringToTime
Copy code to clipboard

Only a string with the format YYYY-MM-DD can be converted to a date. Strings with other formats return 0. Note that fractional seconds are truncated (not rounded) when converting a string to the TIME data type.

The following example casts a date to the TIMESTAMP data type:

SELECT DISTINCT DOB,
   CAST(DOB As TIMESTAMP) AS DateToTstamp
      FROM Sample.Person
Copy code to clipboard

The resulting timestamp is in the format: YYYY-MM-DD hh:mm:ss.

The following example casts a character string to the TIME data type, then casts the resulting time to the TIMESTAMP data type:

SELECT CAST(CAST('14:33:45.78' AS TIME) As TIMESTAMP) AS TimeToTstamp
Copy code to clipboard

The resulting timestamp is in the format: YYYY-MM-DD hh:mm:ss. The time portion is supplied by the nested CAST; the date portion is the current system date.

See Also