Type conversion functions

See also:

- [toDateTime64](#toDateTime64).
- [toDateTime64OrNull](#toDateTime64OrNull).
- [toDateTime64OrDefault](#toDateTime64OrDefault).

**Syntax**

```
toDateTime64OrZero(x)
```

**Arguments**

- `x` — A string representation of a date with time and subsecond precision. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a DateTime64 value if successful, otherwise the lower boundary of DateTime64 (`1970-01-01 00:00:00.000`). [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT toDateTime64OrZero('2025-12-30 13:44:17.123'), toDateTime64OrZero('invalid')
```

```
┌─toDateTime64OrZero('2025-12-30 13:44:17.123')─┬─toDateTime64OrZero('invalid')─┐
│                         2025-12-30 13:44:17.123 │             1970-01-01 00:00:00.000 │
└─────────────────────────────────────────────────┴─────────────────────────────────────┘
```

## toDateTimeOrDefault[​](#toDateTimeOrDefault "Direct link to toDateTimeOrDefault")

Introduced in: v21.11.0

Like [toDateTime](#toDateTime) but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of [DateTime](/docs/sql-reference/data-types/datetime).

**Syntax**

```
toDateTimeOrDefault(expr[, timezone, default])
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `timezone` — Optional. Time zone. [`String`](/docs/sql-reference/data-types/string)
- `default` — Optional. The default value to return if parsing is unsuccessful. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Value of type DateTime if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00 if not. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Successful conversion**

```
SELECT toDateTimeOrDefault('2022-12-30 13:44:17')
```

```
2022-12-30 13:44:17
```

**Failed conversion**

```
SELECT toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))
```

```
2023-01-01 00:00:00
```

## toDateTimeOrNull[​](#toDateTimeOrNull "Direct link to toDateTimeOrNull")

Introduced in: v1.1.0

Converts an input value to a value of type `DateTime` but returns `NULL` if an invalid argument is received.
The same as [`toDateTime`](#toDateTime) but returns `NULL` if an invalid argument is received.

**Syntax**

```
toDateTimeOrNull(x)
```

**Arguments**

- `x` — A string representation of a date with time. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `DateTime` value if successful, otherwise `NULL`. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT toDateTimeOrNull('2025-12-30 13:44:17'), toDateTimeOrNull('invalid')
```

```
┌─toDateTimeOrNull('2025-12-30 13:44:17')─┬─toDateTimeOrNull('invalid')─┐
│                     2025-12-30 13:44:17 │                        ᴺᵁᴸᴸ │
└─────────────────────────────────────────┴─────────────────────────────┘
```

## toDateTimeOrZero[​](#toDateTimeOrZero "Direct link to toDateTimeOrZero")

Introduced in: v1.1.0

Converts an input value to a value of type [DateTime](/docs/sql-reference/data-types/datetime) but returns the lower boundary of [DateTime](/docs/sql-reference/data-types/datetime) if an invalid argument is received.
The same as [toDateTime](#toDateTime) but returns lower boundary of [DateTime](/docs/sql-reference/data-types/datetime) if an invalid argument is received.

**Syntax**

```
toDateTimeOrZero(x)
```

**Arguments**

- `x` — A string representation of a date with time. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a DateTime value if successful, otherwise the lower boundary of DateTime (`1970-01-01 00:00:00`). [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT toDateTimeOrZero('2025-12-30 13:44:17'), toDateTimeOrZero('invalid')
```

```
┌─toDateTimeOrZero('2025-12-30 13:44:17')─┬─toDateTimeOrZero('invalid')─┐
│                     2025-12-30 13:44:17 │         1970-01-01 00:00:00 │
└─────────────────────────────────────────┴─────────────────────────────┘
```

## toDecimal128[​](#toDecimal128 "Direct link to toDecimal128")

Introduced in: v18.12.0

Converts an input value to a value of type [`Decimal(38, S)`](/docs/sql-reference/data-types/decimal) with scale of `S`.
Throws an exception in case of an error.

Supported arguments:

- Values or string representations of type (U)Int\*.
- Values or string representations of type Float\*.

Unsupported arguments:

- Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
- String representations of binary and hexadecimal values, e.g. `SELECT toDecimal128('0xc0fe', 1);`.

Note

An overflow can occur if the value of `expr` exceeds the bounds of `Decimal128`:`(-1*10^(38 - S), 1*10^(38 - S))`.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.

Note

Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal128(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal128('1.15', 2) = 1.15`

**Syntax**

```
toDecimal128(expr, S)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of type `Decimal(38, S)` [`Decimal128(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

```
SELECT
    toDecimal128(99, 1) AS a, toTypeName(a) AS type_a,
    toDecimal128(99.67, 2) AS b, toTypeName(b) AS type_b,
    toDecimal128('99.67', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical
```

```
Row 1:
──────
a:      99
type_a: Decimal(38, 1)
b:      99.67
type_b: Decimal(38, 2)
c:      99.67
type_c: Decimal(38, 3)
```

## toDecimal128OrDefault[​](#toDecimal128OrDefault "Direct link to toDecimal128OrDefault")

Introduced in: v21.11.0

Like [`toDecimal128`](#toDecimal128), this function converts an input value to a value of type [Decimal(38, S)](/docs/sql-reference/data-types/decimal) but returns the default value in case of an error.

**Syntax**

```
toDecimal128OrDefault(expr, S[, default])
```

**Arguments**

- `expr` — A String representation of a number. [`String`](/docs/sql-reference/data-types/string)
- `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `default` — Optional. The default value to return if parsing to type Decimal128(S) is unsuccessful. [`Decimal128(S)`](/docs/sql-reference/data-types/decimal)

**Returned value**

Value of type Decimal(38, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal128(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Successful conversion**

```
SELECT toDecimal128OrDefault(toString(1/42), 18)
```

```
0.023809523809523808
```

**Failed conversion**

```
SELECT toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)'))
```

```
-1
```

## toDecimal128OrNull[​](#toDecimal128OrNull "Direct link to toDecimal128OrNull")

Introduced in: v20.1.0

Converts an input value to a value of type [`Decimal(38, S)`](/docs/sql-reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal128`](#toDecimal128) but returns `NULL` instead of throwing an exception on conversion errors.

Supported arguments: