Previous Next JavaScript must be enabled to correctly display this content

## Casting Datatypes

You can cast one data type to another using the `cast` expression.

In JSON, casting is particularly useful for timestamp information because JSON has no equivalent to the Oracle NoSQL Database Timestamp data type. Instead, the timestamp information is carried in a JSON object as a string. To work with it as a Timestamp, use `cast`.

In Working with Timestamps we showed how to work with the timestamp data type. In this case, what you do is no different except you must cast both sides of the expression. Also, because the left side of the expression is a sequence, you must specify a type quantifier (`*` in this case):

    sql-> SELECT id,
                 j.person.firstname, j.person.lastname, j.person.lastLogin
                 FROM JSONPersons j
                 WHERE CAST(j.person.lastLogin AS TIMESTAMP*) >
                 CAST("2016-11-01" AS TIMESTAMP) AND
                 CAST(j.person.lastLogin AS TIMESTAMP*) <
                 CAST("2016-11-30" AS TIMESTAMP);
     +----+------------+--------------+----------------------------+
     | id | firstname  |   lastname   |       lastLogin            |
     +----+------------+--------------+----------------------------+
     |  3 | John       | Morgan       | 2016-11-29T08:21:35.4971   |
     +----+------------+--------------+----------------------------+
     |  2 | John       | Anderson     | 2016-11-28T13:01:11.2088   |
     +----+------------+--------------+----------------------------+
     |  5 | Dana       | Scully       | 2016-11-08T09:16:46.3929   |
     +----+------------+--------------+----------------------------+

    3 rows returned

As another example, you can cast to an integer and then operate on that number:

    sql-> SELECT id, j.person.mynumber,
                 CAST(j.person.mynumber as integer) * 10 AS TenTimes
                 FROM JSONPersons j WHERE EXISTS j.person.mynumber;
     +----+---------------------+----------+
     | id |      mynumber       | TenTimes |
     +----+---------------------+----------+
     |  7 | 5                   |       50 |
     +----+---------------------+----------+
     |  6 | 5                   |       50 |
     +----+---------------------+----------+

If you want to operate on just the row that contains the number as a string, use IS OF TYPE:

    sql-> SELECT id, j.person.mynumber,
                 CAST(j.person.mynumber as integer) * 10 AS TenTimes
                 FROM JSONPersons j WHERE EXISTS j.person.mynumber
                 AND j.person.mynumber IS OF TYPE (string);
     +----+---------------------+----------+
     | id |      mynumber       | TenTimes |
     +----+---------------------+----------+
     |  7 | 5                   |       50 |
     +----+---------------------+----------+