# Data type conversion

[Read this page in HTML](https://www.scopedb.io/docs/reference/data-types/conversion)

A cast converts a value from one data type to another. Use explicit casts when an input does not have the type required by a function or operator.

## Explicit casting vs implicit casting

An explicit cast names the target type. Implicit casting, or coercion, adapts a compatible value without a written cast.

### Explicit casting

Use `CAST(<expr> AS <type>)`, `<expr>::<type>`, or a typed literal. Convert an order amount and event time from text, then filter by time:

```scopeql
VALUES
    ('o-17', '7900', '2026-06-01T10:05:00Z'),
    ('o-42', '4900', '2026-06-01T09:55:00Z')
SELECT $0 AS order_id, CAST($1 AS int) AS total_cents, $2::timestamp AS event_time
WHERE event_time >= timestamp '2026-06-01T10:00:00Z'
SELECT order_id, total_cents, event_time
```

```text
+----------+-------------+----------------------+
| order_id | total_cents | event_time           |
+----------+-------------+----------------------+
| o-17     | 7900        | 2026-06-01T10:05:00Z |
+----------+-------------+----------------------+
```

Invalid values raise an error. Wrap the expression in [`TRY(...)`](https://www.scopedb.io/docs/reference/functions/conversion.md#try-cast) to return NULL when conversion fails.

### Implicit casting (coercion)

When matching function arguments, ScopeQL accepts these conversions without an explicit cast:

* NULL can match any argument type. The function may still reject NULL values.
* A non-negative `int` constant can match `uint`.
* A `uint` constant within the signed integer range can match `int`.
* An integer constant can match `float`.

These constant conversions do not apply to numeric columns. Cast an integer column explicitly when a function requires `float`. For example, summarize integer latency measurements as a percentile:

```scopeql
VALUES (95), (120), (245), (1800)
SELECT $0 AS latency_ms
AGGREGATE quantile(latency_ms::float, quantile => 0.95) AS p95_ms
```

```text
+--------+
| p95_ms |
+--------+
| 1800.0 |
+--------+
```

## Casting and precedence

The `::` operator binds more tightly than arithmetic and unary minus. Use parentheses to cast a calculated value, such as the total for an order line:

```scopeql
VALUES (4900, 3)
SELECT $0 AS price_cents, $1 AS quantity
SELECT (price_cents * quantity)::string || ' cents' AS line_total
```

```text
+-------------+
| line_total  |
+-------------+
| 14700 cents |
+-------------+
```

Similarly, write `(-0.0)::string` to convert a negative number. Writing `-0.0::string` tries to negate a string and fails.

## Data types that can be cast

| Source                                            | Target                                            | Behavior                                                                                                                     |
| ------------------------------------------------- | ------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `int`, `uint`, `float`                            | Another numeric type                              | The value must fit the target range. Float-to-integer casts truncate toward zero; integer-to-float casts may lose precision. |
| `int`                                             | `timestamp`, `interval`                           | Interpret the integer as nanoseconds from the Unix epoch or as a duration.                                                   |
| `timestamp`, `interval`                           | `int`                                             | Return the corresponding nanosecond count.                                                                                   |
| `string`                                          | Numeric types, `boolean`, `timestamp`, `interval` | Parse the text; invalid input raises an error.                                                                               |
| Numeric types, `boolean`, `timestamp`, `interval` | `string`                                          | Format the value as text.                                                                                                    |
| `binary`                                          | `string`                                          | Format bytes as uppercase hexadecimal text.                                                                                  |
| `array`, `object`                                 | `string`                                          | Format the value as text. Use `to_json` when JSON serialization is required.                                                 |
| Any supported type                                | `any`                                             | Preserve the value and its contained type.                                                                                   |
| `any`                                             | Another supported type                            | Convert the contained value if it is compatible; otherwise raise an error.                                                   |
| `NULL`                                            | Any supported type                                | Preserve NULL.                                                                                                               |

Casting a string to `any` keeps it as a string; it does not parse JSON. Use [`parse_json`](https://www.scopedb.io/docs/reference/functions/semi-structured-data/parse_json.md) to parse JSON text, then cast to `array` or `object` when a function requires that type:

```scopeql
SELECT length(parse_json('["sku-17","sku-42"]')::array) AS item_count
```

```text
+------------+
| item_count |
+------------+
| 2          |
+------------+
```