Previous Next JavaScript must be enabled to correctly display this content

## Cast Expression

The cast expression creates, if possible, new items of a given target type from the items of its input sequence. For example, a STRING can be converted to TIMESTAMP(0) using CAST expression.

Rules followed in a CAST expression:

- If the type of the input item is equal to the target item type, the cast is a no-op: the input item itself is returned.
- If the target type is a wildcard type other than JSON and the type of the input item is a subtype of the wild card type, the cast is a no-op.
- If the target type is JSON, then an error is raised if the input item is a non-json atomic type.
- If the target type is an array type, an error is raised if the input item is not an array.
- If the target type is string, the input item may be of any type. That means every item can be cast to a string. For timestamps, their string value is in UTC and has the format `uuuu-MM-dd['T'HH:mm:ss]`.
- If the target type is an atomic type other than string, the input item must also be atomic.
  - - Integers and longs can be cast to timestamps. The input value is interpreted as the number of milliseconds since January 1, 1970, 00:00:00 GMT.
    - String items may be castable to all other atomic types. Whether the cast succeeds or not depends on whether the actual string value can be parsed into a value that belongs to the domain of the target type.
    - Timestamp items are castable to all the timestamp types. If the target type has a smaller precision that the input item, the resulting timestamp is the one closest to the input timestamp in the target precision.
- To cast a STRING to TIMESTAMP, if the input has STRING values in ISO-8601 format, then it will be automatically converted by the SQL runtime into TIMESTAMP data type.

  Note:

  ISO8601 describes an internationally accepted way to represent dates, times, and durations.

  Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

  where
  - YYYY specifies the year, as four decimal digits
  - MM specifies the month, as two decimal digits, 00 to 12
  - DD specifies the day, as two decimal digits, 00 to 31
  - hh specifies the hour, as two decimal digits, 00 to 23
  - mm specifies the minutes, as two decimal digits, 00 to 59
  - ss[.s[s[s[s[s]]]]] specifies the seconds, as two decimal digits, 00 to 59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
  - Z specifies UTC time (time zone 0). (It can also be specified by +00:00, but not by –00:00.)
  - (+|-)hh:mm specifies the time-zone as difference from UTC. (One of + or – is required.)

Example 1: Fetch the bag arrival date for the passenger with a reservation code DN3I4Q in TIMESTAMP(3) format.

    SELECT CAST (bag.bagInfo.bagArrivalDate AS Timestamp(3))
    AS BAG_ARRIVING_DATE
    FROM BaggageInfo bag WHERE bag.confNo=DN3I4Q

Explanation: The `bagArrivalDate` is a STRING. Using CAST you are converting this field into a TIMESTAMP format.

Output:

    {"BAG_ARRIVING_DATE":"2019-02-15T21:21:00.000Z"}

Example 2: Fetch the full name and tag number for all customer baggage shipped after 2019.

    SELECT fullName, bag.ticketNo,
    bag.bagInfo[].bagArrivalDate
    FROM BaggageInfo bag WHERE
    exists bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

Explanation: You want to filter and display details of the baggage that are shipped after 2019. The bag arrival date for every element in the `flightLegs` array is compared with the given timestamp (`2019-01-01T00:00:00`). Here the casting is implicit as `bagArrivalDate` is a STRING and is directly compared with a static Timestamp value. An explicit CAST function is not needed when an implicit casting can be done. However, your data should be in the format `YYYY-MM-DDTHH:MI:SS` . You then use the EXISTS condition to check if the bagInfo is present for this timestamp condition.

Output:

    {"fullName":"Kendal Biddle","ticketNo":1762377974281,"bagArrivalDate":"2019-03-05T12:00:00Z"}
    {"fullName":"Lucinda Beckman","ticketNo":1762320569757,"bagArrivalDate":"2019-03-12T15:05:00Z"}
    {"fullName":"Adelaide Willard","ticketNo":1762392135540,"bagArrivalDate":"2019-02-15T21:21:00Z"}
    {"fullName":"Raymond Griffin","ticketNo":1762399766476,"bagArrivalDate":"2019-02-03T08:09:00Z"}
    {"fullName":"Elane Lemons","ticketNo":1762324912391,"bagArrivalDate":"2019-03-15T10:13:00Z"}
    {"fullName":"Zina Christenson","ticketNo":1762390789239,"bagArrivalDate":"2019-02-04T10:08:00Z"}
    {"fullName":"Zulema Martindale","ticketNo":1762340579411,"bagArrivalDate":"2019-02-25T20:15:00Z"}
    {"fullName":"Dierdre Amador","ticketNo":1762376407826,"bagArrivalDate":"2019-03-07T13:51:00Z"}
    {"fullName":"Henry Jenkins","ticketNo":176234463813,"bagArrivalDate":"2019-03-02T13:18:00Z"}
    {"fullName":"Rosalia Triplett","ticketNo":1762311547917,"bagArrivalDate":"2019-02-12T07:04:00Z"}
    {"fullName":"Lorenzo Phil","ticketNo":1762320369957,"bagArrivalDate":["2019-03-12T15:05:00Z","2019-03-12T16:25:00Z"]}
    {"fullName":"Gerard Greene","ticketNo":1762341772625,"bagArrivalDate":"2019-03-07T16:01:00Z"}
    {"fullName":"Adam Phillips","ticketNo":1762344493810,"bagArrivalDate":"2019-02-01T16:13:00Z"}
    {"fullName":"Doris Martin","ticketNo":1762355527825,"bagArrivalDate":"2019-03-22T10:17:00Z"}
    {"fullName":"Joanne Diaz","ticketNo":1762383911861,"bagArrivalDate":"2019-02-16T16:13:00Z"}
    {"fullName":"Teena Colley","ticketNo":1762357254392,"bagArrivalDate":"2019-02-13T11:15:00Z"}
    {"fullName":"Michelle Payne","ticketNo":1762330498104,"bagArrivalDate":"2019-02-02T23:59:00Z"}
    {"fullName":"Mary Watson","ticketNo":1762340683564,"bagArrivalDate":"2019-03-14T06:22:00Z"}
    {"fullName":"Omar Harvey","ticketNo":1762348904343,"bagArrivalDate":"2019-03-02T16:09:00Z"}
    {"fullName":"Fallon Clements","ticketNo":1762350390409,"bagArrivalDate":"2019-02-21T14:08:00Z"}
    {"fullName":"Lisbeth Wampler","ticketNo":1762355854464,"bagArrivalDate":"2019-02-10T10:01:00Z"}