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"}