Type Conversion functions

Type conversion functions allow you to convert values from one data type to another. These functions are essential for data manipulation, ensuring compatibility between different data types, and preparing data for specific operations or analyses.

This section covers a wide range of conversion functions, including:

• Conversions between numeric types (e.g., integers to floats)
• Conversions to and from string types
• Date and time conversions
• Boolean conversions
• Special type conversions (e.g., UUID, IPv4/IPv6)

​

ClickHouse function reference

​

toBool

Converts an input value to a value of type Bool. Throws an exception in case of an error.

Syntax:

toBool(expr)

Arguments:

• expr — Expression returning a number or a string.

Supported arguments:

• Values of type (UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256).
• Values of type (Float32, Float64).
• Strings “true” or “false” (case-insensitive).

Returns:

• Returns true or false based on evaluation of the argument. Bool.

Example:

Query:

SELECT
	toBool(toUInt8(1)) AS int_value,
	toBool(toFloat32(1.01)) AS float_value,
	toBool('true') AS string_value,
	toBool('FALSE') AS case_insensitive_value

Result:

| int_value | float_value | string_value | case_insensitive_value |
|-----------|-------------|--------------|------------------------|
| true      | true        | true         | false                  |

In this example:

• int_value converts an integer 1 to true.
• float_value converts a float 1.01 to true.
• string_value converts the string ‘true’ to true.
• case_insensitive_value converts the string ‘FALSE’ to false, demonstrating case-insensitivity.

​

toInt8

Converts an input value to a value of type Int8. Throws an exception in case of an error.

Syntax:

toInt8(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type Float32/Float64.

Unsupported arguments:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt8('0xc0fe');.

Returns:

• 8-bit integer value. Int8.

Example:

Query:

SELECT
	toInt8(-8) AS int8_value,
	toInt8(-8.8) AS float_to_int8,
	toInt8('-8') AS string_to_int8
FROM
  taco_orders
LIMIT 1;

Result:

| int8_value | float_to_int8 | string_to_int8 |
|------------|---------------|----------------|
| -8         | -8            | -8             |

In this example:

• int8_value shows a direct conversion of an integer to Int8.
• float_to_int8 demonstrates how the function truncates the decimal part.
• string_to_int8 shows conversion from a string representation.

See also:

• toInt8OrZero
• toInt8OrNull
• toInt8OrDefault

​

toInt8OrZero

Converts an input value to a value of type Int8 but returns 0 if the conversion is unsuccessful.

Syntax:

toInt8OrZero(expr)

Arguments:

• expr (String, numeric, or Null): Value to convert.

Returns:

• Integer value converted from the input.
• 0 if the conversion is unsuccessful.

Type: Int8

Example:

Query:

SELECT
	toInt8OrZero(123) AS int_value,
	toInt8OrZero('abc') AS string_value,
	toInt8OrZero(NULL) AS null_value

Result:

| int_value | string_value | null_value |
|-----------|--------------|------------|
| 123       | 0            | 0          |

In this example:

• 123 is successfully converted to 123
• ’abc’ cannot be converted, so 0 is returned
• NULL is converted to 0

See Also

• toInt8
• toInt8OrNull

​

toInt8OrNull

Converts an input value to a value of type Int8, but returns NULL if the conversion is not possible.

Syntax:

toInt8OrNull(expr)

Arguments:

• expr (String, number, or NULL): Value to convert.

Returns:

• An Int8 value if the conversion was successful.
• NULL if the conversion was not possible.

Example:

Query:

SELECT
	toInt8OrNull(123) AS int_value,
	toInt8OrNull('abc') AS string_value,
	toInt8OrNull(NULL) AS null_value,
	toInt8OrNull('42') AS string_number,
	toInt8OrNull(8.5) AS float_value,
	toInt8OrNull(256) AS overflow_value

Result:

| int_value | string_value | null_value | string_number | float_value | overflow_value |
|-----------|--------------|------------|---------------|-------------|----------------|
| 123       | ᴺᵁᴸᴸ         | ᴺᵁᴸᴸ        | 42            | 8           | ᴺᵁᴸᴸ           |

In this example:

• 123 is successfully converted to Int8.
• 'abc' cannot be converted, so NULL is returned.
• NULL input results in NULL output.
• '42' is successfully parsed and converted to Int8.
• 8.5 is truncated to 8 (rounding towards zero).
• 256 is outside the range of Int8 (-128 to 127), so NULL is returned.

See Also

• toInt8
• toInt8OrZero
• toInt8OrDefault

​

toInt8OrDefault

Converts an input value to a value of type Int8, but returns the default value if parsing fails.

Syntax:

toInt8OrDefault(expr[, default])

Arguments:

• expr (numeric or String): Value to convert. Expression returning a number or a string representation of a number.
• default (Int8, optional): Default value to return if parsing fails.

Returns:

• An 8-bit signed integer value if conversion is successful.
• The default value if specified, or 0 if not, when conversion fails.

Example:

Query:

SELECT
	toInt8OrDefault(toString(8), CAST('-1', 'Int8')) AS valid_int,
	toInt8OrDefault('guacamole', CAST('-1', 'Int8')) AS invalid_int

Result:

| valid_int | invalid_int |
|-----------|-------------|
| 8         | -1          |

In this example:

• valid_int successfully converts the string ‘8’ to the Int8 value 8.
• invalid_int fails to convert ‘guacamole’ to an Int8, so it returns the default value -1.

See also:

• toInt8
• toInt8OrZero
• toInt8OrNull

​

toInt16

Converts an input value to a value of type Int16. Throws an exception in case of an error.

Syntax:

toInt16(expr)

Arguments:

• expr — Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt16('0xc0fe');.

Returns:

• 16-bit integer value. Int16.

Example:

SELECT
	toInt16(-16),
	toInt16(-16.16),
	toInt16('-16') AS taco_count

Result:

| toInt16(-16) | toInt16(-16.16) | taco_count |
|--------------|-----------------|------------|
| -16          | -16             | -16        |

In this example, we convert various representations of -16 to Int16. Note how the fractional part is truncated in the second conversion.

See also:

• toInt16OrZero
• toInt16OrNull
• toInt16OrDefault

​

toInt16OrZero

Converts an input value to a value of type Int16 but returns 0 if the conversion is unsuccessful.

Syntax:

toInt16OrZero(x)

Arguments:

• x (String, Integer, Float): Value to convert.

Returns:

• Integer value of type Int16.
• 0, if the conversion is unsuccessful.

Example:

Query:

SELECT
	toInt16OrZero('16') AS taco_count,
	toInt16OrZero('spicy') AS spice_level

Result:

| taco_count | spice_level |
|------------|-------------|
| 16         | 0           |

In this example:

• taco_count successfully converts the string ‘16’ to the Int16 value 16.
• spice_level returns 0 because ‘spicy’ cannot be converted to an Int16.

See also:

• toInt16
• toInt16OrNull
• toInt16OrDefault

​

toInt16OrNull

Converts an input value to a value of type Int16 but returns NULL if an error occurs during the conversion.

Syntax:

toInt16OrNull(x)

Arguments:

• x (String, Integer, Float): Value to convert.

Returns:

• Integer value of type Int16 if conversion is successful.
• NULL if conversion fails.

Example:

Query:

SELECT
	toInt16OrNull('16') AS valid_conversion,
	toInt16OrNull('abc') AS invalid_conversion,
	toInt16OrNull('32768') AS out_of_range

Result:

| valid_conversion  | invalid_conversion  | out_of_range    |
|-------------------|---------------------|-----------------|
| 16                | NULL                | NULL            |

In this example:

• ‘16’ is successfully converted to Int16.
• ‘abc’ cannot be converted, so NULL is returned.
• ‘32768’ is out of range for Int16 (which is -32768 to 32767), so NULL is returned.

See also:

• toInt16
• toInt16OrZero
• toInt16OrDefault

​

toInt16OrDefault

Like toInt16, this function converts an input value to a value of type Int16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toInt16OrDefault(expr[, default])

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.
• default (Int16, optional): The default value to return if parsing to type Int16 is unsuccessful.

Returns:

• 16-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int16.

Example:

SELECT
	toInt16OrDefault('-16', CAST('-1', 'Int16')) AS valid_conversion,
	toInt16OrDefault('abc', CAST('-1', 'Int16')) AS invalid_conversion

Result:

| valid_conversion | invalid_conversion  |
|------------------|---------------------|
| -16              | -1                  |

In this example:

• For the valid input ‘-16’, the function returns -16.
• For the invalid input ‘abc’, it returns the default value -1.

See also:

• toInt16
• toInt16OrZero
• toInt16OrNull

​

toInt32

Converts an input value to a value of type Int32. Throws an exception in case of an error.

Syntax:

toInt32(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type Float32/Float64.

Unsupported arguments:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32(‘0xc0fe’);.

Returns:

• 32-bit integer value. Int32.

Example:

SELECT
	toInt32(-32),
	toInt32(-32.32),
	toInt32('-32')

Result:

| toInt32(-32) | toInt32(-32.32) | toInt32('-32') |
|--------------|-----------------|----------------|
| -32          | -32             | -32            |

See also:

• toInt32OrZero
• toInt32OrNull
• toInt32OrDefault

​

toInt32OrZero

Converts an input value to a value of type Int32 but returns 0 in case of an error.

Syntax:

toInt32OrZero(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrZero('0xc0fe');.

Returns:

• 32-bit integer value if successful, otherwise 0. Int32.

Example:

Query:

SELECT
	toInt32OrZero('-32'),
	toInt32OrZero('abc')
FROM
  taco_orders;

Result:

| toInt32OrZero('-32') | toInt32OrZero('abc') |
|----------------------|----------------------|
| -32                  | 0                    |

In this example:

• -32 is successfully converted to Int32.
• 'abc' cannot be converted, so 0 is returned.

See also:

• toInt32
• toInt32OrNull
• toInt32OrDefault

​

toInt32OrNull

Converts an input value to a value of type Int32 but returns NULL in case of an error.

Syntax:

toInt32OrNull(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return NULL):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrNull('0xc0fe');.

Returns:

• 32-bit integer value if successful, otherwise NULL. Int32 / NULL.

Example:

Query:

SELECT
	toInt32OrNull('-32'),
	toInt32OrNull('abc') AS taco_count

Result:

| toInt32OrNull('-32') | taco_count |
|----------------------|------------|
| -32                  | NULL       |

In this example, ‘-32’ is successfully converted to an Int32 value, while ‘abc’ results in NULL as it cannot be interpreted as a valid integer.

See also:

• toInt32
• toInt32OrZero
• toInt32OrDefault

​

toInt32OrDefault

Converts an input value to a value of type Int32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toInt32OrDefault(expr[, default])

Arguments:

• expr (Expression / String) — Expression returning a number or a string representation of a number.
• default (Int32, optional) — The default value to return if parsing to type Int32 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrDefault('0xc0fe', CAST('-1', 'Int32'));.

Returns:

• 32-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int32.

Example:

Query:

SELECT
	toInt32OrDefault('-32', CAST('-1', 'Int32')) AS valid_conversion,
	toInt32OrDefault('abc', CAST('-1', 'Int32')) AS invalid_conversion,
	toInt32OrDefault('123.45', CAST('-1', 'Int32')) AS float_conversion,
	toInt32OrDefault('0xc0fe', CAST('-1', 'Int32')) AS hex_conversion

Result:

| valid_conversion  | invalid_conversion  | float_conversion  | hex_conversion  |
|-------------------|---------------------|-------------------|-----------------|
| -32               | -1                  | 123               | -1              |

In this example:

• ’-32’ is successfully converted to -32
• ’abc’ cannot be converted, so the default value -1 is returned
• ’123.45’ is truncated to 123 (rounding towards zero)
• ‘0xc0fe’ is treated as an invalid hexadecimal string, so the default value -1 is returned

See also

• toInt32
• toInt32OrZero
• toInt32OrNull

​

toInt64

Converts an input value to a value of type Int64. Throws an exception in case of an error.

Syntax:

toInt64(expr)

Arguments:

• expr — Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type (Float32/Float64).

Unsupported types:

• String representations of (Float32/Float64) values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64('0xc0fe');.

Returns:

• 64-bit integer value. Int64.

Example:

Query:

SELECT
	toInt64(-64),
	toInt64(-64.64),
	toInt64('-64')

Result:

| toInt64(-64) | toInt64(-64.64) | toInt64('-64') |
|--------------|-----------------|----------------|
| -64          | -64             | -64            |

See also

• toInt64OrZero
• toInt64OrNull
• toInt64OrDefault

​

toInt64OrZero

Converts an input value to a value of type Int64 but returns 0 in case of an error.

Syntax:

toInt64OrZero(x)

Arguments:

• x (String, number, or date/time value): Value to convert.

Returns:

• 64-bit integer value if conversion is successful.
• 0 if conversion fails.

Example:

Query:

SELECT
	toInt64OrZero('123') AS good_conversion,
	toInt64OrZero('twelve tacos') AS bad_conversion

Result:

| good_conversion | bad_conversion |
|-----------------|----------------|
| 123             | 0              |

In this example, ‘123’ is successfully converted to 123, while ‘twelve tacos’ fails to convert and returns 0.

See also:

• toInt64
• toInt64OrNull
• toInt64OrDefault

​

toInt64OrNull

Converts an input value to a value of type Int64, but returns NULL if the conversion is unsuccessful.

Syntax:

toInt64OrNull(expr)

Arguments:

• expr (Any): Value to convert. Can be any data type that can be converted to Int64.

Returns:

• A value of type Nullable(Int64).
• NULL if the conversion was unsuccessful.

Example:

Query:

SELECT
	toInt64OrNull('123') AS valid_int,
	toInt64OrNull('12.3') AS invalid_float,
	toInt64OrNull('twelve') AS invalid_string,
	toInt64OrNull('9223372036854775807') AS max_int64,
	toInt64OrNull('9223372036854775808') AS overflow_int64

Result:

| valid_int | invalid_float | invalid_string | max_int64           | overflow_int64 |
|-----------|---------------|----------------|---------------------|----------------|
| 123       | NULL          | NULL           | 9223372036854775807 | NULL           |

In this example:

• ‘123’ is successfully converted to Int64.
• ‘12.3’ fails because it’s a float.
• ‘twelve’ fails because it’s not a numeric string.
• ‘9223372036854775807’ is the maximum value for Int64.
• ‘9223372036854775808’ overflows Int64, so NULL is returned.

See also:

• toInt64
• toInt64OrZero
• toInt64OrDefault

​

toInt64OrDefault

Converts an input value to a value of type Int64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toInt64OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (Int64, optional): The default value to return if parsing to type Int64 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64OrDefault('0xc0fe', CAST('-1', 'Int64'));.

Returns:

• 64-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int64.

Example:

Query:

SELECT
	toInt64OrDefault('-64', CAST('-1', 'Int64')) AS valid_conversion,
	toInt64OrDefault('abc', CAST('-1', 'Int64')) AS invalid_conversion,
	toInt64OrDefault('9223372036854775808', CAST('-1', 'Int64')) AS overflow_conversion

Result:

| valid_conversion  | invalid_conversion  | overflow_conversion  |
|-------------------|---------------------|----------------------|
| -64               | -1                  | -1                   |

In this example:

• ’-64’ is successfully converted to -64
• ’abc’ cannot be converted, so the default value -1 is returned
• ’9223372036854775808’ is too large for Int64, causing overflow, so the default -1 is returned

See also:

• toInt64
• toInt64OrZero
• toInt64OrNull

​

toInt128

Converts an input value to a value of type Int128. Throws an exception in case of an error.

Syntax:

toInt128(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type Float32/Float64.

Unsupported arguments:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128('0xc0fe');.

Returns:

• 128-bit integer value. Int128.

Example:

Query:

SELECT
	toInt128(-128),
	toInt128(-128.8),
	toInt128('-128') AS result

Result:

| toInt128(-128) | toInt128(-128.8)  | result |
|----------------|-------------------|--------|
| -128           | -128              | -128   |

This example demonstrates converting various input types to Int128, including a negative integer, a negative float, and a string representation of a negative integer.

See also:

• toInt128OrZero
• toInt128OrNull
• toInt128OrDefault

​

toInt128OrZero

Converts an input value to a value of type Int128 but returns 0 if the conversion is unsuccessful.

Syntax:

toInt128OrZero(expr)

Arguments:

• expr (Any): Value to convert. Can be any data type that can be converted to Int128.

Returns:

• An Int128 value if conversion is successful.
• 0 if the conversion is unsuccessful.

Example:

Query:

SELECT
	toInt128OrZero('128') AS valid_int,
	toInt128OrZero('big taco') AS invalid_string

Result:

| valid_int | invalid_string |
|-----------|----------------|
| 128       | 0              |

In this example, ‘128’ is successfully converted to an Int128 value, while ‘big taco’ cannot be converted, so 0 is returned.

See also:

• toInt128
• toInt128OrNull
• toInt128OrDefault

​

toInt128OrNull

Converts an input value to a value of type Int128 but returns NULL if an error occurs during the conversion.

Syntax:

toInt128OrNull(x)

Arguments:

• x (Any type convertible to Int128): Value to convert.

Returns:

• A value of type Nullable(Int128).
• NULL if the conversion was not successful.

Example:

Query:

SELECT
	toInt128OrNull('128') AS valid_int,
	toInt128OrNull('hello') AS invalid_string,
	toInt128OrNull('340282366920938463463374607431768211456') AS out_of_range

Result:

| valid_int | invalid_string | out_of_range |
|-----------|----------------|--------------|
| 128       | NULL           | NULL         |

In this example:

• ‘128’ is successfully converted to Int128
• ’hello’ cannot be converted, so NULL is returned
• ’340282366920938463463374607431768211456’ is out of range for Int128, so NULL is returned

This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception.

See Also

• toInt128
• toInt128OrZero

​

toInt128OrDefault

Converts an input value to a value of type Int128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toInt128OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (Int128, optional): The default value to return if parsing to type Int128 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/Float64.

Arguments for which the default value is returned:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128OrDefault('0xc0fe', CAST('-1', 'Int128'));.

Returns:

• 128-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int128.

Example:

Query:

SELECT
	toInt128OrDefault('-128', CAST('-1', 'Int128')) AS valid_conversion,
	toInt128OrDefault('abc', CAST('-1', 'Int128')) AS invalid_conversion,
	toInt128OrDefault('1.23e45', CAST('-1', 'Int128')) AS overflow_conversion

Result:

| valid_conversion  | invalid_conversion  | overflow_conversion  |
|-------------------|---------------------|----------------------|
| -128              | -1                  | -1                   |

In this example:

• ’-128’ is successfully converted to Int128.
• ‘abc’ cannot be converted, so the default value -1 is returned.
• ‘1.23e45’ is too large for Int128, causing overflow, so the default value -1 is returned.

See also:

• toInt128
• toInt128OrZero
• toInt128OrNull

​

toInt256

Converts an input value to a value of type Int256. Throws an exception in case of an error.

Syntax:

toInt256(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type Float32/Float64.

Unsupported arguments:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt256('0xc0fe');.

Returns:

• 256-bit integer value. Int256.

Example:

Query:

SELECT
	toInt256(-256) AS a,
	toTypeName(a) AS type_a,
	toInt256(-256.256) AS b,
	toTypeName(b) AS type_b,
	toInt256('-256') AS c,
	toTypeName(c) AS type_c

Result:

| a    | type_a | b    | type_b | c    | type_c |
|------|--------|------|--------|------|--------|
| -256 | Int256 | -256 | Int256 | -256 | Int256 |

See Also

• toInt256OrZero
• toInt256OrNull
• toInt256OrDefault

​

toInt256OrZero

Converts an input value to a value of type Int256 but returns 0 in case of an error.

Syntax:

toInt256OrZero(x)

Arguments:

• x (String): Value to convert.

Returns:

• Converted integer value, or 0 if the conversion failed.
• Type: Int256.

Example:

Query:

SELECT
	toInt256OrZero('42') AS good_int,
	toInt256OrZero('extra spicy') AS bad_int

Result:

| good_int | bad_int |
|----------|---------|
| 42       | 0       |

In this example:

• '42' is successfully converted to the Int256 value 42.
• 'extra spicy' cannot be converted to an integer, so 0 is returned.

​

toInt256OrNull

Converts an input value to a value of type Int256 but returns NULL if an error occurs during the conversion.

Syntax:

toInt256OrNull(x)

Arguments:

• x (Any): Value to convert. Can be any type that is convertible to Int256.

Returns:

• A value of type Nullable(Int256).
• Returns NULL if the conversion was not successful.

Example:

Query:

SELECT
	toInt256OrNull('256') AS valid_int,
	toInt256OrNull('big taco') AS invalid_int

Result:

| valid_int | invalid_int |
|-----------|-------------|
| 256       | NULL        |

In this example:

• ‘256’ is successfully converted to Int256.
• ‘big taco’ cannot be converted, so NULL is returned.

​

toInt256OrDefault

Converts an input value to a value of type Int256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toInt256OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (Int256, optional): The default value to return if parsing to type Int256 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt256OrDefault('0xc0fe', CAST('-1', 'Int256'));.

Returns:

• 256-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int256.

Example:

Query:

SELECT
	toInt256OrDefault('-256', CAST('-1', 'Int256')) AS valid_conversion,
	toInt256OrDefault('guacamole', CAST('-1', 'Int256')) AS invalid_conversion

Result:

| valid_conversion  | invalid_conversion  |
|-------------------|---------------------|
| -256              | -1                  |

In this example, ‘-256’ is successfully converted to Int256, while ‘guacamole’ cannot be parsed, so the default value of -1 is returned.

See also

• toInt256
• toInt256OrZero
• toInt256OrNull

​

toUInt8

Converts an input value to a value of type UInt8. Throws an exception in case of an error.

Syntax:

toUInt8(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8('0xc0fe').

Returns:

• 8-bit unsigned integer value. UInt8.

Example:

Query:

SELECT
	toUInt8(8) AS int_value,
	toUInt8(8.8) AS float_value,
	toUInt8('8') AS string_value
FROM
  taco_orders
LIMIT 1;

Result:

| int_value | float_value | string_value |
|-----------|-------------|--------------|
| 8         | 8           | 8            |

In this example:

• int_value converts the integer 8 to UInt8.
• float_value converts the float 8.8 to UInt8, truncating the decimal part.
• string_value converts the string ‘8’ to UInt8.

See also:

• toUInt8OrZero
• toUInt8OrNull
• toUInt8OrDefault

​

toUInt8OrZero

Converts an input value to a value of type UInt8 but returns 0 in case of an error.

Syntax:

toUInt8OrZero(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrZero('0xc0fe');.

Returns:

• 8-bit unsigned integer value if successful, otherwise 0. UInt8.

Example:

Query:

SELECT
	toUInt8OrZero('42') AS valid_uint8,
	toUInt8OrZero('abc') AS invalid_uint8,
	toUInt8OrZero('256') AS overflow_uint8
FROM taco_orders;

Result:

| valid_uint8 | invalid_uint8 | overflow_uint8 |
|-------------|---------------|----------------|
| 42          | 0             | 0              |

In this example:

• ‘42’ is successfully converted to UInt8.
• ‘abc’ is not a valid number, so 0 is returned.
• ‘256’ overflows UInt8 (max value 255), so 0 is returned.

See also:

• toUInt8
• toUInt8OrNull
• toUInt8OrDefault

​

toUInt8OrNull

Converts an input value to a value of type UInt8 but returns NULL in case of an error.

Syntax:

toUInt8OrNull(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return NULL):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrNull('0xc0fe');.

Returns:

• 8-bit unsigned integer value if successful, otherwise NULL. UInt8 / NULL.

Example:

Query:

SELECT
	toUInt8OrNull('8') AS valid_uint8,
	toUInt8OrNull('abc') AS invalid_uint8

Result:

| valid_uint8 | invalid_uint8 |
|-------------|---------------|
| 8           | NULL          |

In this example:

• valid_uint8 successfully converts the string ‘8’ to UInt8.
• invalid_uint8 returns NULL because ‘abc’ cannot be converted to UInt8.

See also:

• toUInt8
• toUInt8OrZero
• toUInt8OrDefault

​

toUInt8OrDefault

Converts an input value to a value of type UInt8 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt8OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (UInt8, optional): The default value to return if parsing to type UInt8 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrDefault('0xc0fe', CAST('0', 'UInt8'));.

Returns:

• 8-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt8.

Example:

Query:

SELECT
	toUInt8OrDefault('8', CAST('0', 'UInt8')) AS valid_conversion,
	toUInt8OrDefault('abc', CAST('0', 'UInt8')) AS invalid_conversion,
	toUInt8OrDefault('256', CAST('0', 'UInt8')) AS overflow_conversion

Result:

| valid_conversion  | invalid_conversion  | overflow_conversion  |
|-------------------|---------------------|----------------------|
| 8                 | 0                   | 0                    |

In this example:

• ‘8’ is successfully converted to UInt8.
• ‘abc’ cannot be converted, so the default value 0 is returned.
• ‘256’ overflows UInt8, so the default value 0 is returned.

See Also

• toUInt8
• toUInt8OrZero
• toUInt8OrNull

​

toUInt16

Converts an input value to a value of type UInt16. Throws an exception in case of an error.

Syntax:

toUInt16(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16('0xc0fe');.

Returns:

• 16-bit unsigned integer value. UInt16.

Example:

Query:

SELECT
	toUInt16(16) AS int_value,
	toUInt16(16.16) AS float_value,
	toUInt16('16') AS string_value
FROM
  taco_orders
LIMIT 1;

Result:

| int_value | float_value | string_value |
|-----------|-------------|--------------|
| 16        | 16          | 16           |

In this example:

• int_value shows direct conversion from an integer.
• float_value demonstrates truncation of fractional part.
• string_value illustrates conversion from a string representation.

See also:

• toUInt16OrZero
• toUInt16OrNull
• toUInt16OrDefault

​

toUInt16OrZero

Converts an input value to a value of type UInt16 but returns 0 if the conversion is unsuccessful.

Syntax:

toUInt16OrZero(expr)

Arguments:

• expr (String, integer, or floating-point number): Value to convert.

Returns:

• UInt16 value if conversion is successful.
• 0 if the conversion is unsuccessful.

Example:

Query:

SELECT
	toUInt16OrZero('16') AS taco_count,
	toUInt16OrZero('extra_spicy') AS spice_level

Result:

| taco_count | spice_level |
|------------|-------------|
| 16         | 0           |

In this example, ‘16’ is successfully converted to a UInt16 value, while ‘extra_spicy’ cannot be converted and returns 0.

​

toUInt16OrNull

Converts an input value to a value of type UInt16 but returns NULL if an error occurs during the conversion.

Syntax:

toUInt16OrNull(expr)

Arguments:

• expr (String, integer, or floating-point number): Value to convert.

Returns:

• A UInt16 value if conversion is successful.
• NULL if the conversion fails.

Example:

Query:

SELECT
	toUInt16OrNull('16') AS valid_uint16,
	toUInt16OrNull('taco') AS invalid_uint16

Result:

| valid_uint16 | invalid_uint16 |
|--------------|----------------|
| 16           | NULL           |

In this example:

• ‘16’ is successfully converted to UInt16.
• ‘taco’ cannot be converted, so NULL is returned.

​

toUInt16OrDefault

Like toUInt16, this function converts an input value to a value of type UInt16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt16OrDefault(expr[, default])

Arguments:

• expr (String, numeric): Expression returning a number or a string representation of a number.
• default (UInt16, optional): The default value to return if parsing to type UInt16 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/Float64.

Arguments for which the default value is returned:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrDefault('0xc0fe', CAST('0', 'UInt16'));.

Returns:

• 16-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt16.

Example:

Query:

SELECT
	toUInt16OrDefault('16', CAST('0', 'UInt16')) AS valid_conversion,
	toUInt16OrDefault('abc', CAST('0', 'UInt16')) AS invalid_conversion,
	toUInt16OrDefault('300', CAST('0', 'UInt16')) AS overflow_conversion

Result:

| valid_conversion  | invalid_conversion  | overflow_conversion  |
|-------------------|---------------------|----------------------|
| 16                | 0                   | 300                  |

In this example:

• ‘16’ is successfully converted to UInt16.
• ‘abc’ cannot be converted, so the default value 0 is returned.
• ‘300’ is outside the range of UInt8 (0-255) but within UInt16, so it’s converted successfully.

See Also

• toUInt16
• toUInt16OrZero
• toUInt16OrNull

​

toUInt32

Converts an input value to a value of type UInt32. Throws an exception in case of an error.

Syntax:

toUInt32(expr)

Arguments:

• expr (numeric or string): Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32(‘0xc0fe’);.

Returns:

• 32-bit unsigned integer value. UInt32.

Example:

Query:

SELECT
	toUInt32(32),
	toUInt32(32.32),
	toUInt32('32') AS taco_count
FROM
	taco_orders;

Result:

| toUInt32(32) | toUInt32(32.32) | taco_count |
|--------------|-----------------|------------|
| 32           | 32              | 32         |

In this example, we convert various representations of the number 32 to UInt32, which could represent a count of tacos in an order.

See also:

• toUInt32OrZero
• toUInt32OrNull
• toUInt32OrDefault

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)(Int8/Int16/Int32/Int128/Int256).

Unsupported arguments (return 0):

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrZero(‘0xc0fe’);.

Returns:

• 32-bit unsigned integer value if successful, otherwise 0. (UInt32).

Example:

Query:

SELECT
	toUInt32OrZero('32'),
	toUInt32OrZero('abc')

Result:

| toUInt32OrZero('32') | toUInt32OrZero('abc') |
|----------------------|-----------------------|
| 32                   | 0                     |

See Also

• toUInt32
• toUInt32OrNull
• toUInt32OrDefault

​

toUInt32OrNull

Converts an input value to a value of type UInt32 but returns NULL in case of an error.

Syntax:

toUInt32OrNull(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return NULL):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrNull('0xc0fe');.

Returns:

• 32-bit unsigned integer value if successful, otherwise NULL. UInt32 / NULL.

Example:

Query:

SELECT
	toUInt32OrNull('32') AS valid_uint32,
	toUInt32OrNull('3.14159') AS invalid_float,
	toUInt32OrNull('0xDEADBEEF') AS invalid_hex,
	toUInt32OrNull('') AS empty_string
FROM
  taco_orders
LIMIT 1;

Result:

| valid_uint32 | invalid_float | invalid_hex | empty_string |
|--------------|---------------|-------------|--------------|
| 32           | NULL          | NULL        | NULL         |

In this example:

• ‘32’ is successfully converted to UInt32.
• ‘3.14159’ returns NULL as it’s a float.
• ‘0xDEADBEEF’ returns NULL as it’s a hexadecimal value.
• An empty string returns NULL.

See also:

• toUInt32
• toUInt32OrZero
• toUInt32OrDefault

​

toUInt32OrDefault

Converts an input value to a value of type UInt32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt32OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (UInt32, optional): The default value to return if parsing to type UInt32 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrDefault('0xc0fe', CAST('0', 'UInt32'));.

Returns:

• UInt32: 32-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.

Example:

Query:

SELECT
	toUInt32OrDefault('32', CAST('0', 'UInt32')) AS valid_conversion,
	toUInt32OrDefault('abc', CAST('0', 'UInt32')) AS invalid_conversion,
	toUInt32OrDefault('256.7', CAST('42', 'UInt32')) AS float_conversion

Result:

| valid_conversion  | invalid_conversion  | float_conversion  |
|-------------------|---------------------|-------------------|
| 32                | 0                   | 256               |

In this example:

• ‘32’ is successfully converted to 32
• ’abc’ cannot be converted, so the default value 0 is returned
• ’256.7’ is truncated to 256 (rounding towards zero)

See Also

• toUInt32
• toUInt32OrZero
• toUInt32OrNull

​

toUInt64

Converts an input value to a value of type UInt64. Throws an exception in case of an error.

Syntax:

toUInt64(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported types:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64('0xc0fe');.

Returns:

• 64-bit unsigned integer value. UInt64.

Example:

Query:

SELECT
	toUInt64(64),
	toUInt64(64.64),
	toUInt64('64')

Result:

| toUInt64(64) | toUInt64(64.64) | toUInt64('64') |
|--------------|-----------------|----------------|
| 64           | 64              | 64             |

See also

• toUInt64OrZero
• toUInt64OrNull
• toUInt64OrDefault

​

toUInt64OrZero

Converts an input value to a value of type UInt64 but returns 0 in case of an error.

Syntax:

toUInt64OrZero(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of UInt8, UInt16, UInt32, UInt128, UInt256, Int8, Int16, Int32, Int128, Int256.

Unsupported arguments (return 0):

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrZero('0xc0fe');.

Returns:

• 64-bit unsigned integer value if successful, otherwise 0. UInt64.

Example:

Query:

SELECT
	toUInt64OrZero('64'),
	toUInt64OrZero('abc')

Result:

| toUInt64OrZero('64') | toUInt64OrZero('abc') |
|----------------------|-----------------------|
| 64                   | 0                     |

This example demonstrates converting a valid string representation of a number to UInt64, and an invalid string returning 0.

See Also

• toUInt64
• toUInt64OrNull
• toUInt64OrDefault

​

toUInt64OrNull

Converts an input value to a value of type UInt64 but returns NULL if an error occurs during the conversion.

Syntax:

toUInt64OrNull(x)

Arguments:

• x (any): Value to convert. Can be any type that is convertible to UInt64.

Returns:

• A UInt64 value if the conversion is successful.
• NULL if the conversion fails.

Example:

Query:

SELECT
	toUInt64OrNull('42') AS valid_uint,
	toUInt64OrNull('abc') AS invalid_uint,
	toUInt64OrNull('9223372036854775808') AS large_uint

Result:

| valid_uint | invalid_uint | large_uint |
|------------|--------------|------------|
| 42         | NULL         | NULL       |

In this example:

• ‘42’ is successfully converted to UInt64.
• ‘abc’ cannot be converted, so NULL is returned.
• ‘9223372036854775808’ is too large for UInt64, so NULL is returned.

This function is useful when you need to handle potential conversion errors gracefully without causing query failures.

​

toUInt64OrDefault

Converts an input value to a value of type UInt64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt64OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (UInt64, optional): The default value to return if parsing to type UInt64 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrDefault('0xc0fe', CAST('0', 'UInt64'));.

Returns:

• 64-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt64.

Example:

Query:

SELECT
	toUInt64OrDefault('64', CAST('0', 'UInt64')),
	toUInt64OrDefault('abc', CAST('0', 'UInt64'))

Result:

| toUInt64OrDefault('64', CAST('0', 'UInt64')) | toUInt64OrDefault('abc', CAST('0', 'UInt64')) |
|----------------------------------------------|-----------------------------------------------|
| 64                                           | 0                                             |

This example demonstrates converting a valid string ‘64’ to UInt64, and an invalid string ‘abc’ which returns the default value 0.

See also

• toUInt64
• toUInt64OrZero
• toUInt64OrNull

​

toUInt128

Converts an input value to a value of type UInt128. Throws an exception in case of an error.

Syntax:

toUInt128(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type (Float32/Float64).

Unsupported arguments:

• String representations of (Float32/Float64) values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128('0xc0fe');.

Returns:

• 128-bit unsigned integer value. UInt128.

Example:

Query:

SELECT
	toUInt128(128) AS int_value,
	toUInt128(128.8) AS float_value,
	toUInt128('128') AS string_value

Result:

| int_value | float_value | string_value |
|-----------|-------------|--------------|
| 128       | 128         | 128          |

In this example, we convert various values to UInt128:

SELECT
	toUInt128(42) AS taco_count,
	toUInt128(3.99) AS taco_price,
	toUInt128('1000000') AS total_tacos_sold

Result:

| taco_count | taco_price | total_tacos_sold |
|------------|------------|------------------|
| 42         | 3          | 1000000          |

​

toUInt128OrZero

Converts an input value to a value of type UInt128 but returns 0 in case of an error.

Syntax:

toUInt128OrZero(expr)

Arguments:

• expr (String): Expression returning a number or a string representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrZero('0xc0fe');.

Returns:

• 128-bit unsigned integer value if successful, otherwise 0. UInt128.

Example:

Query:

SELECT
	toUInt128OrZero('128') AS valid_uint128,
	toUInt128OrZero('guacamole') AS invalid_uint128

Result:

| valid_uint128 | invalid_uint128 |
|---------------|-----------------|
| 128           | 0               |

In this example, ‘128’ is successfully converted to UInt128, while ‘guacamole’ returns 0 as it’s an invalid input.

See also:

• toUInt128
• toUInt128OrNull
• toUInt128OrDefault

​

toUInt128OrNull

Converts an input value to a value of type UInt128 but returns NULL if an error occurs during the conversion.

Syntax:

toUInt128OrNull(expr)

Arguments:

• expr (any): Value to convert. Can be any data type that can be converted to UInt128.

Returns:

• A UInt128 value if the conversion was successful.
• NULL if the conversion failed.

Example:

Query:

SELECT
	toUInt128OrNull('340282366920938463463374607431768211455') AS max_uint128,
	toUInt128OrNull('taco') AS invalid_input

Result:

| max_uint128                             | invalid_input |
|-----------------------------------------|---------------|
| 340282366920938463463374607431768211455 | NULL          |

In this example:

• The first value is successfully converted to the maximum UInt128 value.
• The second value ‘taco’ cannot be converted, so NULL is returned.

​

toUInt128OrDefault

Like toUInt128, this function converts an input value to a value of type UInt128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt128OrDefault(expr[, default])

Arguments:

• expr (String, numeric): Expression returning a number or a string representation of a number.
• default (UInt128, optional): The default value to return if parsing to type UInt128 is unsuccessful.

Supported arguments:

• (U)Int8/16/32/64/128/256
• Float32/64
• String representations of (U)Int8/16/32/128/256

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrDefault('0xc0fe', CAST('0', 'UInt128'));

Returns:

• 128-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. (UInt128).

Example:

Query:

SELECT
	toUInt128OrDefault('128', CAST('0', 'UInt128')) AS valid_conversion,
	toUInt128OrDefault('abc', CAST('0', 'UInt128')) AS invalid_conversion,
	toTypeName(valid_conversion) AS valid_type,
	toTypeName(invalid_conversion) AS invalid_type

Result:

| valid_conversion  | invalid_conversion  | valid_type | invalid_type |
|-------------------|---------------------|------------|--------------|
| 128               | 0                   | UInt128    | UInt128      |

In this example:

• ‘128’ is successfully converted to UInt128
• ’abc’ cannot be converted, so the default value 0 is returned
• Both results are of type UInt128

See Also

• toUInt128
• toUInt128OrZero
• toUInt128OrNull

​

toUInt256

Converts an input value to a value of type UInt256. Throws an exception in case of an error.

Syntax:

toUInt256(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type (U)(Int8/Int16/Int32/Int64/Int128/Int256).
• Values of type Float32/Float64.

Unsupported arguments:

• String representations of Float32/Float64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256('0xc0fe');.

Returns:

• 256-bit unsigned integer value. UInt256.

Example:

Query:

SELECT
	toUInt256(256) AS a,
	toTypeName(a) AS type_a,
	toUInt256(256.256) AS b,
	toTypeName(b) AS type_b,
	toUInt256('256') AS c,
	toTypeName(c) AS type_c

Result:

| a   | type_a  | b   | type_b  | c   | type_c  |
|-----|---------|-----|---------|-----|---------|
| 256 | UInt256 | 256 | UInt256 | 256 | UInt256 |

This example demonstrates converting various input types (integer, float, and string) to UInt256.

See also:

• toUInt256OrZero
• toUInt256OrNull
• toUInt256OrDefault

​

toUInt256OrZero

Converts an input value to a value of type UInt256 but returns 0 in case of an error.

Syntax:

toUInt256OrZero(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrZero('0xc0fe');.

Returns:

• 256-bit unsigned integer value if successful, otherwise 0. UInt256.

Example:

Query:

SELECT
	toUInt256OrZero('256') AS valid_conversion,
	toUInt256OrZero('abc') AS invalid_conversion,
	toUInt256OrZero('18446744073709551616') AS overflow_conversion

Result:

| valid_conversion  | invalid_conversion  | overflow_conversion  |
|-------------------|---------------------|----------------------|
| 256               | 0                   | 18446744073709551616 |

In this example:

• ‘256’ is successfully converted to UInt256.
• ‘abc’ is an invalid input, so it returns 0.
• ‘18446744073709551616’ (2^64) demonstrates overflow behavior, returning the value as-is since it’s within UInt256 range.

See Also

• toUInt256
• toUInt256OrNull
• toUInt256OrDefault

​

toUInt256OrNull

Converts an input value to a value of type UInt256 but returns NULL in case of an error.

Syntax:

toUInt256OrNull(x)

Arguments:

• x (String): A String representation of a number.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return NULL):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrNull('0xc0fe');.

Returns:

• 256-bit unsigned integer value if successful, otherwise NULL. UInt256 / NULL.

Example:

Query:

SELECT
	toUInt256OrNull('256'),
	toUInt256OrNull('abc')

Result:

| toUInt256OrNull('256') | toUInt256OrNull('abc') |
|------------------------|------------------------|
| 256                    | NULL                   |

See Also

• toUInt256
• toUInt256OrZero
• toUInt256OrDefault

​

toUInt256OrDefault

Converts an input value to a value of type UInt256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toUInt256OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• default (UInt256, optional): The default value to return if parsing to type UInt256 is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrDefault('0xc0fe', CAST('0', 'UInt256'));.

Returns:

• UInt256: 256-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.

Example:

Query:

SELECT
	toUInt256OrDefault('256', CAST('0', 'UInt256')) AS valid_conversion,
	toUInt256OrDefault('abc', CAST('0', 'UInt256')) AS invalid_conversion,
	toUInt256OrDefault('-256', CAST('0', 'UInt256')) AS negative_conversion

Result:

| valid_conversion  | invalid_conversion  | negative_conversion  |
|-------------------|---------------------|----------------------|
| 256               | 0                   | 0                    |

In this example:

• ‘256’ is successfully converted to UInt256.
• ‘abc’ cannot be converted, so the default value 0 is returned.
• ’-256’ is outside the range of UInt256, so the default value 0 is returned.

See Also

• toUInt256
• toUInt256OrZero
• toUInt256OrNull

​

toFloat32

Converts an input value to a value of type Float32. Throws an exception in case of an error.

Syntax:

toFloat32(expr)

Arguments:

• expr — Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
• Values of type Float32, Float64
• String representations of Float32, Float64, including NaN and Inf (case-insensitive)

Unsupported arguments:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat32('0xc0fe');

Returns:

• 32-bit floating point value. Type: Float32.

Example:

Query:

SELECT
	toFloat32(42.7) AS a,
	toFloat32('42.7') AS b,
	toFloat32('NaN') AS c,
	toTypeName(a) AS type_a,
	toTypeName(b) AS type_b,
	toTypeName(c) AS type_c

Result:

| Row | a    | b    | c   | type_a  | type_b  | type_c  |
|-----|------|------|-----|---------|---------|---------|
| 1   | 42.7 | 42.7 | nan | Float32 | Float32 | Float32 |

In this example:

• a converts a numeric literal to Float32
• b converts a string representation to Float32
• c demonstrates handling of special values like NaN

See Also

• toFloat32OrZero
• toFloat32OrNull
• toFloat32OrDefault

​

toFloat32OrZero

Converts an input value to a value of type Float32 but returns 0 if an error occurs during the conversion.

Syntax:

toFloat32OrZero(x)

Arguments:

• x (String, integer, or floating-point number): Value to convert.

Returns:

• A Float32 value if conversion is successful.
• 0 if the conversion fails.

Example:

Query:

SELECT
	toFloat32OrZero('3.14159') AS pi,
	toFloat32OrZero('extra_spicy') AS failed_conversion

Result:

| pi      | failed_conversion |
|---------|-------------------|
| 3.14159 | 0                 |

In this example:

• pi successfully converts the string ‘3.14159’ to a Float32 value.
• failed_conversion returns 0 because ‘extra_spicy’ cannot be converted to a Float32.

​

toFloat32OrNull

Converts an input value to a value of type Float32 but returns NULL if an error occurs during the conversion.

Syntax:

toFloat32OrNull(x)

Arguments:

• x (String, integer, or floating-point number): Value to be converted.

Returns:

• A Float32 value if conversion is successful.
• NULL if the conversion fails.

Example:

Query:

SELECT
	toFloat32OrNull('42.7') AS valid_float,
	toFloat32OrNull('al pastor') AS invalid_float

Result:

| valid_float | invalid_float |
|-------------|---------------|
| 42.7        | NULL          |

In this example:

• ‘42.7’ is successfully converted to a Float32 value.
• ‘al pastor’ cannot be converted, so NULL is returned.

Syntax:

toFloat32OrDefault(expr[, default])

Arguments:

• expr (Any data type that can be converted to Float32): Value to convert.
• default (Float32, optional): Value returned if the conversion fails. If not specified, 0 is returned.

Returns:

• A Float32 value if the conversion succeeds.
• The default value (or 0 if not specified) if the conversion fails.

Example:

SELECT
	toFloat32OrDefault('3.14159', CAST('0', 'Float32')) AS valid_float,
	toFloat32OrDefault('not_a_number', CAST('99.9', 'Float32')) AS invalid_float

Result:

| valid_float | invalid_float |
|-------------|---------------|
| 3.14159     | 99.9          |

In this example:

• ‘3.14159’ is successfully converted to a Float32.
• ‘not_a_number’ cannot be converted, so the default value 99.9 is returned.

This function is useful when you need to handle potential conversion errors gracefully, especially when dealing with user input or data from external sources.

​

toFloat64

Converts an input value to a value of type Float64. Throws an exception in case of an error.

Syntax:

toFloat64(expr)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.

Supported arguments:

• Values or string representations of type UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256.
• Values or string representations of type Float32, Float64.
• String representations of Float32, Float64, including NaN and Inf (case-insensitive).

Unsupported arguments:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat64('0xc0fe');.

Returns:

• 64-bit floating point value. Float64.

Example:

SELECT
	toFloat64(42.7),
	toFloat64('42.7'),
	toFloat64('NaN')

Result:

| toFloat64(42.7) | toFloat64('42.7')  | toFloat64('NaN')   |
|-----------------|--------------------|--------------------|
| 42.7            | 42.7               | nan                |

See also:

• toFloat64OrZero
• toFloat64OrNull
• toFloat64OrDefault

​

toFloat64OrZero

Converts an input value to a value of type Float64 but returns 0 if an error occurs during the conversion.

Syntax:

toFloat64OrZero(x)

Arguments:

• x (String, number, or date/time value): Value to convert.

Returns:

• Float64 value if conversion is successful.
• 0 if conversion fails.

Example:

Query:

SELECT
	toFloat64OrZero('42.5') AS valid_conversion,
	toFloat64OrZero('not a number') AS invalid_conversion,
	toFloat64OrZero('inf') AS infinity_conversion;

Result:

| valid_conversion | invalid_conversion  | infinity_conversion |
|------------------|---------------------|---------------------|
| 42.5             | 0                   | inf                 |

This example demonstrates:

• Successful conversion of a valid float string
• Zero returned for an invalid input
• Special handling of ‘inf’ as a valid float value

See also:

• toFloat64 — Throws an exception on conversion error.
• toFloat64OrNull — Returns NULL on conversion error.

​

toFloat64OrNull

Converts an input value to a value of type Float64, but returns NULL if the conversion is unsuccessful.

Syntax:

toFloat64OrNull(x)

Arguments:

• x (String, number, or date/time value): Value to convert.

Returns:

• A Float64 value if the conversion is successful.
• NULL if the conversion fails.

Examples

SELECT
	toFloat64OrNull('42.5') AS valid_float,
	toFloat64OrNull('abc') AS invalid_float

Result:

| valid_float | invalid_float |
|-------------|---------------|
| 42.5        | NULL          |

This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception for invalid input.

See Also

• toFloat64
• toFloat64OrZero
• toFloat64OrDefault

​

toFloat64OrDefault

Converts an input value to a value of type Float64, but returns a default value if the conversion fails.

Syntax:

toFloat64OrDefault(expr[, default])

Arguments:

• expr (Any data type that can be converted to Float64): Value to convert.
• default (Float64, optional): Value returned if the conversion fails. If not specified, 0 is returned.

Returns:

• A Float64 value if the conversion succeeds.
• The default value (or 0 if not specified) if the conversion fails.

Examples

SELECT
	toFloat64OrDefault('3.14159') AS pi,
	toFloat64OrDefault('not a number', 99.9) AS invalid_with_default,
	toFloat64OrDefault('also not a number') AS invalid_without_default

Result:

| pi      | invalid_with_default | invalid_without_default |
|---------|----------------------|-------------------------|
| 3.14159 | 99.9                 | 0                       |

In this example:

• ‘pi’ is successfully converted to a Float64.
• ‘invalid_with_default’ fails to convert and returns the specified default of 99.9.
• ‘invalid_without_default’ fails to convert and returns 0 since no default was specified.

​

toDate

Converts an input value to the Date data type.

Syntax:

toDate(expr)

Arguments:

• expr (Any): Value to convert. Can be any data type that can be converted to a date.

Returns:

• A value of Date type.

Examples

Converting from DateTime:

SELECT
	toDate(now()) AS date_value

Result:

| date_value |
|------------|
| 2023-09-15 |

Converting from a string:

SELECT
	toDate('2023-09-15') AS date_from_string

Result:

| date_from_string |
|------------------|
| 2023-09-15       |

Converting from a Unix timestamp:

SELECT
	toDate(1694736000) AS date_from_timestamp

Result:

| date_from_timestamp |
|---------------------|
| 2023-09-15          |

See also: toDateTime, toDate32

​

toDateOrZero

Converts an input value to a value of type Date. This function behaves similarly to toDate, but returns the lower boundary of the Date type (1970-01-01) if an invalid argument is received.

Syntax:

toDateOrZero(expr)

Arguments:

• expr — Value to be converted. Can be any data type that can be converted to [Date].

Returns:

• A value of type [Date].
• Returns 1970-01-01 if the argument cannot be parsed as a valid date.

Example:

Query:

SELECT
	toDateOrZero('2023-05-15') AS valid_date,
	toDateOrZero('not a date') AS invalid_date,
	toDateOrZero('2023-13-32') AS out_of_range_date;

Result:

| valid_date | invalid_date | out_of_range_date |
|------------|--------------|-------------------|
| 2023-05-15 | 1970-01-01   | 1970-01-01        |

In this example:

• valid_date shows a correctly parsed date
• invalid_date returns the minimum Date value because the input is not a valid date string
• out_of_range_date also returns the minimum Date value because the input is out of the valid range for dates

​

toDateOrNull

Converts the input value to the Date data type. This function is similar to toDate, but returns NULL if the conversion is unsuccessful.

Syntax:

toDateOrNull(expr)

Arguments:

• expr (Any): Value to be converted. Can be any data type that can be converted to a date.

Returns:

• A value in the Date data type if conversion is successful.
• NULL if the conversion fails.

Example:

Query:

SELECT
	toDateOrNull('2023-09-15') AS valid_date,
	toDateOrNull('2023-09-31') AS invalid_date,
	toDateOrNull('not a date') AS non_date_string;

Result:

| valid_date | invalid_date | non_date_string |
|------------|--------------|-----------------|
| 2023-09-15 | NULL         | NULL            |

In this example:

• ‘2023-09-15’ is successfully converted to a Date.
• ‘2023-09-31’ is an invalid date (September has 30 days), so it returns NULL.
• ‘not a date’ cannot be interpreted as a date, so it returns NULL.

This function is particularly useful when dealing with potentially invalid date inputs, as it allows for graceful handling of conversion errors without throwing exceptions.

​

toDateOrDefault

Converts an input value to a value of type Date. If the conversion is unsuccessful, it returns a default value.

Syntax:

toDateOrDefault(expr[, default_value])

Arguments:

• expr (Expression): Expression returning a date or a string representation of a date.
• default_value (Date, optional): The default value to return if parsing to Date is unsuccessful.

Returns:

• A Date value if the conversion is successful.
• The specified default value if provided, or 1970-01-01 if not, when the conversion fails.

Example:

Query:

SELECT
	toDateOrDefault('2023-05-15') AS valid_date,
	toDateOrDefault('not a date') AS invalid_date,
	toDateOrDefault('not a date', toDate('2023-01-01')) AS invalid_date_with_default

Result:

| valid_date | invalid_date | invalid_date_with_default |
|------------|--------------|---------------------------|
| 2023-05-15 | 1970-01-01   | 2023-01-01                |

In this example:

• valid_date successfully converts the string to a Date.
• invalid_date fails to convert and returns the default date (1970-01-01).
• invalid_date_with_default fails to convert but returns the specified default date.

This function is useful when you need to handle potentially invalid date inputs without causing query errors, providing a fallback value for further processing.

​

toDateTime

Converts an input value to DateTime.

Syntax:

toDateTime(expr[, time_zone])

Arguments:

• expr (String, Int, Date, DateTime): The value to convert.
• time_zone (String, optional): The time zone to use for the conversion.

Returns:

• A date with time. DateTime.

Example:

SELECT
	toDateTime('2022-12-30 13:44:17'),
	toDateTime(1685457500, 'UTC');

Result:

| toDateTime('2022-12-30 13:44:17') | toDateTime(1685457500, 'UTC') |
|-----------------------------------|-------------------------------|
| 2022-12-30 13:44:17               | 2023-05-30 14:38:20           |

In this example, we convert a string representation of a date and time to a DateTime value. We also convert a Unix timestamp to a DateTime value, specifying the UTC time zone.

​

toDateTimeOrZero

Converts an input value to a value of type DateTime but returns the lower boundary of DateTime (1970-01-01 00:00:00) if an invalid argument is received.

Syntax:

toDateTimeOrZero(expr[, time_zone])

Arguments:

• expr (String): Value to be converted.
• time_zone (String, optional): Timezone for the returned value.

Returns:

• DateTime value if conversion is successful, otherwise 1970-01-01 00:00:00.
• Type: DateTime.

Example:

Query:

SELECT
	toDateTimeOrZero('2022-12-30 13:44:17') AS valid_datetime,
	toDateTimeOrZero('invalid_date') AS invalid_datetime;

Result:

| valid_datetime        | invalid_datetime     |
|-----------------------|----------------------|
| 2022-12-30 13:44:17   | 1970-01-01 00:00:00  |

In this example:

• valid_datetime shows a correctly parsed DateTime.
• invalid_datetime shows the result of an invalid input, which is the lower boundary of DateTime.

​

toDateTimeOrNull

Converts an input value to a value of type DateTime but returns NULL if an error occurs during the conversion.

Syntax:

toDateTimeOrNull(expr[, timezone])

Arguments:

• expr (any): Value to be converted. Can be any data type that can be converted to DateTime.
• timezone (String, optional): Timezone for the returned value. If not specified, the server’s timezone is used.

Returns:

• A value of DateTime type if the conversion is successful.
• NULL if the conversion fails.

Example:

SELECT
	toDateTimeOrNull('2023-09-15 14:30:00') AS valid_datetime,
	toDateTimeOrNull('invalid_date') AS invalid_datetime

Result:

| valid_datetime        | invalid_datetime |
|-----------------------|------------------|
| 2023-09-15 14:30:00   | NULL             |

In this example:

• ‘2023-09-15 14:30:00’ is successfully converted to a DateTime value.
• ‘invalid_date’ cannot be converted, so NULL is returned.

This function is useful when you need to handle potentially invalid date/time inputs without causing query errors.

​

toDateTimeOrDefault

Converts an input value to a value of type DateTime but returns the default value in case of an error. If no default value is passed then the lower boundary of DateTime is returned in case of an error.

Syntax:

toDateTimeOrDefault(expr [, time_zone [, default_value]])

Arguments:

• expr (Expression / String): Expression returning a number or a string representation of a number.
• time_zone (String, optional): Timezone name for the returned value.
• default_value (DateTime, optional): The default value to return if parsing to type DateTime is unsuccessful.

Returns:

• A date and time value if successful, otherwise returns the default value if passed or the lower boundary of DateTime if not. DateTime.

Example:

Query:

SELECT
	toDateTimeOrDefault('2022-12-30 13:44:17') AS valid_datetime,
	toDateTimeOrDefault('', 'UTC', '2023-01-01'::DateTime('UTC')) AS invalid_datetime

Result:

| Row 1:            |                        |
|-------------------|------------------------|
| valid_datetime:   | 2022-12-30 13:44:17    |
| invalid_datetime: | 2023-01-01 00:00:00    |

In this example:

• valid_datetime successfully converts the string to a DateTime value.
• invalid_datetime returns the specified default value for an invalid input.

This function is useful when you need to handle potentially invalid datetime inputs without causing query errors, providing a fallback value for further processing.

​

toDate32

Converts an input value to a value of type Date32. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, its borders are taken into account.

Syntax:

toDate32(expr)

Arguments:

• expr (String, UInt32, or Date): The value to convert.

Returns:

• A calendar date. Type: Date32.

Example:

Query:

SELECT
	toDate32('1955-01-01') AS value,
	toTypeName(value);

Result:

| value      | toTypeName(toDate32('1925-01-01'))  |
|------------|-------------------------------------|
| 1955-01-01 | Date32                              |

Query:

SELECT
	toDate32('1899-01-01') AS value,
	toTypeName(value);

Result:

| value      | toTypeName(toDate32('1899-01-01'))  |
|------------|-------------------------------------|
| 1900-01-01 | Date32                              |

Query:

SELECT
	toDate32(toDate('1899-01-01')) AS value,
	toTypeName(value);

Result:

| value      | toTypeName(toDate32(toDate('1899-01-01')))  |
|------------|---------------------------------------------|
| 1970-01-01 | Date32                                      |

​

toDate32OrZero

Converts an input value to a value of type Date32 but returns the minimum value of Date32 (1900-01-01) if an invalid argument is received.

Syntax:

toDate32OrZero(expr)

Arguments:

• expr (Any): Value to be converted. Can be any data type that can be converted to Date32.

Returns:

• A [Date32] value if the conversion is successful.
• 1900-01-01 if the conversion fails.

Example:

Query:

SELECT
	toDate32OrZero('2023-05-15') AS valid_date,
	toDate32OrZero('not a date') AS invalid_date,
	toDate32OrZero('1899-12-31') AS out_of_range_date

Result:

| valid_date | invalid_date | out_of_range_date |
|------------|--------------|-------------------|
| 2023-05-15 | 1900-01-01   | 1900-01-01        |

In this example:

• ‘2023-05-15’ is successfully converted to a Date32 value.
• ‘not a date’ is an invalid input, so it returns the minimum Date32 value.
• ‘1899-12-31’ is out of the valid range for Date32, so it also returns the minimum value.

This function is useful when you want to handle invalid date inputs gracefully in your taco sales analysis, replacing them with a known default value instead of throwing an error.

​

toDate32OrNull

Converts an input value to a value of type Date32 but returns NULL if an invalid argument is received.

Syntax:

toDate32OrNull(expr)

Arguments:

• expr (String, DateTime, Date, Date32, Int32, UInt32): Expression returning a number or a string representation of a date. Expression.

Returns:

• A Date32 value if the conversion was successful, otherwise NULL.
• Returns Date32 / Nullable(Date32).

Example:

Query:

SELECT
	toDate32OrNull('1955-01-01') AS valid_date,
	toDate32OrNull('not a date') AS invalid_date

Result:

| valid_date | invalid_date |
|------------|--------------|
| 1955-01-01 | NULL         |

In this example:

• ‘1955-01-01’ is successfully converted to a Date32 value.
• ‘not a date’ cannot be converted, so NULL is returned.

This function is useful when you need to handle potentially invalid date inputs without causing query errors.

​

toDate32OrDefault

Converts an input value to a value of type Date32 but returns the default value in case of an error. If no default value is passed then the minimum value of Date32 (1900-01-01) is returned in case of an error.

Syntax:

toDate32OrDefault(expr[, default])

Arguments:

• expr (Expression / String): Expression returning a date or a string representation of a date.
• default (Date32, optional): The default value to return if parsing to type Date32 is unsuccessful.

Returns:

• A Date32 value if successful, otherwise returns the default value if passed or 1900-01-01 if not.

Example:

Query:

SELECT
	toDate32OrDefault('1930-01-01', toDate32('2020-01-01')) AS valid_date,
	toDate32OrDefault('not_a_date', toDate32('2020-01-01')) AS invalid_date

Result:

| valid_date | invalid_date |
|------------|--------------|
| 1930-01-01 | 2020-01-01   |

In this example:

• valid_date successfully converts ‘1930-01-01’ to a Date32 value.
• invalid_date fails to parse ‘not_a_date’, so it returns the specified default value ‘2020-01-01’.

​

toDateTime64

Converts an input value to a value of type DateTime64.

Syntax:

toDateTime64(expr, scale, [timezone])

Arguments:

• expr (String, UInt32, Float, or DateTime): Expression returning a number or a string representation of a number.
• scale (numeric): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].
• timezone (String, optional): Time zone of the specified DateTime64 object.

Returns:

• A calendar date and time of day, with sub-second precision. DateTime64.

Example:

Query:

SELECT
	toDateTime64('1955-01-01 00:00:00.000', 3) AS value,
	toTypeName(value);

Result:

| value                   | toTypeName(toDateTime64('1955-01-01 00:00:00.000', 3)) |
|-------------------------|--------------------------------------------------------|
| 1955-01-01 00:00:00.000 | DateTime64(3)                                          |

Query:

SELECT
	toDateTime64(1546300800.000, 3) AS value,
	toTypeName(value);

Result:

| value                   | toTypeName(toDateTime64(1546300800., 3)) |
|-------------------------|------------------------------------------|
| 2019-01-01 00:00:00.000 | DateTime64(3)                            |

Query:

SELECT
	toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS value,
	toTypeName(value);

Result:

| value                   | toTypeName(toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul')) |
|-------------------------|---------------------------------------------------------------------|
| 2019-01-01 00:00:00.000 | DateTime64(3, 'Asia/Istanbul')                                      |

SELECT
	toDateTime64(1546300800000, 3) AS value,
	toTypeName(value);

Result:

| value                   | toTypeName(toDateTime64(1546300800000, 3))  |
|-------------------------|---------------------------------------------|
| 2282-12-31 00:00:00.000 | DateTime64(3)                               |

This function is particularly useful when you need to work with timestamps that require sub-second precision, such as in financial applications or high-frequency event logging.

​

toDateTime64OrZero

Converts an input value to a value of type DateTime64 but returns the minimum value of DateTime64 if an invalid argument is received.

Syntax:

toDateTime64OrZero(expr, scale[, timezone])

Arguments:

• expr (String, UInt32, Float, DateTime): Expression returning a number or a string representation of a number.
• scale (UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].
• timezone (String, optional): Time zone of the specified DateTime64 object.

Returns:

• A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64: 1970-01-01 00:00:00.000. DateTime64.

Example:

Query:

SELECT
	toDateTime64OrZero('2008-10-12 00:00:00 00:30:30', 3) AS invalid_arg

Result:

| invalid_arg              |
|--------------------------|
| 1970-01-01 00:00:00.000  |

This example demonstrates how the function handles an invalid input by returning the minimum DateTime64 value.

See Also

• toDateTime64
• toDateTime64OrNull
• toDateTime64OrDefault

​

toDateTime64OrNull

Converts an input value to a value of type DateTime64 but returns NULL if an invalid argument is received.

Syntax:

toDateTime64OrNull(expr, scale[, timezone])

Arguments:

• expr (String, Number): Expression returning a number or a string representation of a number.
• scale (UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].
• timezone (String, optional): Time zone of the specified DateTime64 object.

Returns:

• A calendar date and time of day, with sub-second precision, otherwise NULL. DateTime64 / Nullable(DateTime64).

Example:

Query:

SELECT
	toDateTime64OrNull('1976-10-18 00:00:00.30', 3) AS valid_arg,
	toDateTime64OrNull('1976-10-18 00:00:00 30', 3) AS invalid_arg

Result:

| valid_arg                | invalid_arg |
|--------------------------|-------------|
| 1976-10-18 00:00:00.300  | NULL        |

This example demonstrates converting a valid date string to DateTime64 and returning NULL for an invalid date string.

See Also

• toDateTime64
• toDateTime64OrZero
• toDateTime64OrDefault

​

toDateTime64OrDefault

Converts an input value to a value of type DateTime64, but returns either the default value of DateTime64 or the provided default if an invalid argument is received.

Syntax:

toDateTime64OrDefault(expr, scale[, timezone[, default]])

Arguments:

• expr (String, Expression): Expression returning a number or a string representation of a number.
• scale (UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].
• timezone (String, optional): Time zone of the specified DateTime64 object.
• default (DateTime64, optional): Default value to return if an invalid argument is received.

Returns:

• A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64 or the default value if provided. DateTime64.

Example:

Query:

SELECT
	toDateTime64OrDefault('1976-10-18 00:00:00 30', 3) AS invalid_arg,
	toDateTime64OrDefault('1976-10-18 00:00:00 30', 3, 'UTC', toDateTime64('2001-01-01 00:00:00.00',3)) AS invalid_arg_with_default

Result:

| invalid_arg                | invalid_arg_with_default |
|----------------------------|--------------------------|
| 1970-01-01 01:00:00.000    | 2000-12-31 23:00:00.000  |

This example demonstrates how toDateTime64OrDefault handles invalid input. When given an invalid date string, it returns the minimum DateTime64 value if no default is specified, or the provided default value if one is given.

See Also

• toDateTime64
• toDateTime64OrZero
• toDateTime64OrNull

​

toDecimal32

Converts an input value to a value of type Decimal(9, S) with scale of S. Throws an exception in case of an error.

Syntax:

toDecimal32(expr, S)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number.
• S (UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.

Returns:

• Value of type Decimal(9, S). Decimal32(S).

Example:

SELECT
	toDecimal32(2, 1) AS a,
	toTypeName(a) AS type_a,
	toDecimal32(4.2, 2) AS b,
	toTypeName(b) AS type_b,
	toDecimal32('4.2', 3) AS c,
	toTypeName(c) AS type_c

Result:

| a        | type_a        | b        | type_b        |
|----------|---------------|----------|---------------|
| -1.11100 | Decimal(9, 5) | 0.00000  | Decimal(9, 5) |

See also: toDecimal32OrZero, toDecimal32OrNull, toDecimal32OrDefault.

​

toDecimal32OrZero

Converts an input value to a value of type Decimal(9, S) but returns 0 if an error occurs during the conversion.

Syntax:

toDecimal32OrZero(expr, S)

Arguments:

• expr (String): Value to convert to Decimal(9, S).
• S (UInt8): Number of decimal places, from 0 to 9.

Returns:

• A value of type Decimal(9, S) if the conversion is successful.
• 0 with S decimal places if the conversion fails.

Type: Decimal(9, S)

Example:

Query:

SELECT
	toDecimal32OrZero(toString(-1.111), 5) AS a,
	toTypeName(a) AS type_a,
	toDecimal32OrZero('not a number', 5) AS b,
	toTypeName(b) AS type_b

Result:

| a        | type_a            | b    | type_b                  |
|----------|-------------------|------|-------------------------|
| -1.11100 | Decimal(9, 5)     | NULL | Nullable(Decimal(9, 5)) |

In this example:

• The string “-1.111” is successfully converted to a Decimal(9, 5).
• The string “not a number” cannot be converted, so it returns 0.00000.

See Also

• toDecimal32
• toDecimal32OrNull
• toDecimal32OrDefault

​

toDecimal32OrNull

Converts an input value to a value of type Nullable(Decimal(9, S)) but returns NULL if an error occurs during the conversion.

Syntax:

toDecimal32OrNull(expr, S)

Arguments:

• expr (String): A string representation of a number.
• S (UInt8): Scale, the number of decimal places. Range: 0 to 9.

Returns:

• A value of type Nullable(Decimal(9, S)) if the conversion is successful.
• NULL if the conversion fails.

Examples

Query:

SELECT
	toDecimal32OrNull(toString(-1.111), 5) AS a,
	toTypeName(a),
	toDecimal32OrNull(toString('Inf'), 5) AS b,
	toTypeName(b)

Result:

| a       | toTypeName(a)           | b    | toTypeName(b)           |
|---------|-------------------------|------|-------------------------|
| -1.11100| Nullable(Decimal(9, 5)) | NULL | Nullable(Decimal(9, 5)) |

See Also

• toDecimal32
• toDecimal32OrZero
• toDecimal32OrDefault

​

toDecimal32OrDefault

Like toDecimal32, this function converts an input value to a value of type Decimal(9, S) but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax:

toDecimal32OrDefault(expr, S[, default])

Arguments:

• expr (String, numeric): Expression returning a number or a string representation of a number.
• S (UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.
• default (Decimal32(S), optional): The default value to return if parsing to type Decimal32(S) is unsuccessful.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal32OrDefault('0xc0fe', CAST('-1', 'Decimal32(0)'));.

Returns:

• Value of type Decimal(9, S) if successful, otherwise returns the default value if passed or 0 if not.

Example:

Query:

SELECT
	toDecimal32OrDefault(toString(0.0001), 5) AS a,
	toTypeName(a),
	toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)')) AS b,
	toTypeName(b)

Result:

| a       | toTypeName(a)  | b   | toTypeName(b)  |
|---------|----------------|-----|----------------|
| 0.00010 | Decimal(9, 5)  | -1  | Decimal(9, 0)  |

See also

• toDecimal32
• toDecimal32OrZero
• toDecimal32OrNull

​

toDecimal64

Converts an input value to a value of type Decimal(18, S) with scale of S. Throws an exception in case of an error.

Syntax:

toDecimal64(expr, S)

Arguments:

• expr (numeric or String): Expression returning a number or a string representation of a number. Expression.
• S (UInt8): Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values or string representations of type Float32/64.

Unsupported arguments:

• Values or string representations of Float32/64 values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal64('0xc0fe', 1);.

Returns:

• Value of type Decimal(18, S). Decimal64(S).

Example:

SELECT
	toDecimal64(2, 1) AS a,
	toTypeName(a) AS type_a,
	toDecimal64(4.2, 2) AS b,
	toTypeName(b) AS type_b,
	toDecimal64('4.2', 3) AS c,
	toTypeName(c) AS type_c