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:
-
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:
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