Skip to main content
Skip to main content
Edit this page

Type conversion functions

Common issues with data conversion​

ClickHouse generally uses the same behavior as C++ programs.

to<type> functions and cast behave differently in some cases, for example in case of LowCardinality: cast removes LowCardinality trait to<type> functions don't. The same with Nullable, this behaviour is not compatible with SQL standard, and it can be changed using cast_keep_nullable setting.

Note

Be aware of potential data loss if values of a datatype are converted to a smaller datatype (for example from Int64 to Int32) or between incompatible datatypes (for example from String to Int). Make sure to check carefully if the result is as expected.

Example:

SELECT
    toTypeName(toLowCardinality('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type────────────┬─to_type_result_type────┬─cast_result_type─┐
│ LowCardinality(String) │ LowCardinality(String) │ String           │
└────────────────────────┴────────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ String           │
└──────────────────┴─────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type
SETTINGS cast_keep_nullable = 1

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ Nullable(String) │
└──────────────────┴─────────────────────┴──────────────────┘

Notes on toString functions​

The toString family of functions allows for converting between numbers, strings (but not fixed strings), dates, and dates with times. All of these functions accept one argument.

  • When converting to or from a string, the value is formatted or parsed using the same rules as for the TabSeparated format (and almost all other text formats). If the string can't be parsed, an exception is thrown and the request is canceled.
  • When converting dates to numbers or vice versa, the date corresponds to the number of days since the beginning of the Unix epoch.
  • When converting dates with times to numbers or vice versa, the date with time corresponds to the number of seconds since the beginning of the Unix epoch.
  • The toString function of the DateTime argument can take a second String argument containing the name of the time zone, for example: Europe/Amsterdam. In this case, the time is formatted according to the specified time zone.

Notes on toDate/toDateTime functions​

The date and date-with-time formats for the toDate/toDateTime functions are defined as follows:

YYYY-MM-DD
YYYY-MM-DD hh:mm:ss

As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date. This allows support for the common occurrence of writing toDate(unix_timestamp), which otherwise would be an error and would require writing the more cumbersome toDate(toDateTime(unix_timestamp)).

Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.

Conversion between numeric types uses the same rules as assignments between different numeric types in C++.

Example

Query:

SELECT
    now() AS ts,
    time_zone,
    toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10

Result:

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam  │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra    │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan  │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens     │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast    │ 2023-09-08 20:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belgrade   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Berlin     │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bratislava │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Brussels   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bucharest  │ 2023-09-08 22:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘

Also see the toUnixTimestamp function.

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. Expression.

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • Values of type Float32/64.
  • Strings true or false (case-insensitive).

Returned value

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

Example

Query:

SELECT
    toBool(toUInt8(1)),
    toBool(toInt8(-1)),
    toBool(toFloat32(1.01)),
    toBool('true'),
    toBool('false'),
    toBool('FALSE')
FORMAT Vertical

Result:

toBool(toUInt8(1)):      true
toBool(toInt8(-1)):      true
toBool(toFloat32(1.01)): true
toBool('true'):          true
toBool('false'):         false
toBool('FALSE'):         false

toInt8​

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

Syntax

toInt8(expr)

Arguments

  • expr — 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 toInt8('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toInt8(128) == -128;.

Returned value

  • 8-bit integer value. Int8.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt8(-8),
    toInt8(-8.8),
    toInt8('-8')
FORMAT Vertical;

Result:

Row 1:
──────
toInt8(-8):   -8
toInt8(-8.8): -8
toInt8('-8'): -8

See also

toInt8OrZero​

Like toInt8, this function converts an input value to a value of type Int8 but returns 0 in case of an error.

Syntax

toInt8OrZero(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return 0):

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

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 8-bit integer value if successful, otherwise 0. Int8.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt8OrZero('-8'),
    toInt8OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt8OrZero('-8'):  -8
toInt8OrZero('abc'): 0

See also

toInt8OrNull​

Like toInt8, this function converts an input value to a value of type Int8 but returns NULL in case of an error.

Syntax

toInt8OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 8-bit integer value if successful, otherwise NULL. Int8 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt8OrNull('-8'),
    toInt8OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt8OrNull('-8'):  -8
toInt8OrNull('abc'): ᴺᵁᴸᴸ

See also

toInt8OrDefault​

Like toInt8, this function converts an input value to a value of type Int8 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

toInt8OrDefault(expr[, default])

Arguments

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

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 toInt8OrDefault('0xc0fe', CAST('-1', 'Int8'));.
Note

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 8-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int8.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt8OrDefault('-8', CAST('-1', 'Int8')),
    toInt8OrDefault('abc', CAST('-1', 'Int8'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt8OrDefault('-8', CAST('-1', 'Int8')):  -8
toInt8OrDefault('abc', CAST('-1', 'Int8')): -1

See also

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. 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 toInt16('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toInt16(32768) == -32768;.

Returned value

  • 16-bit integer value. Int16.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt16(-16),
    toInt16(-16.16),
    toInt16('-16')
FORMAT Vertical;

Result:

Row 1:
──────
toInt16(-16):    -16
toInt16(-16.16): -16
toInt16('-16'):  -16

See also

toInt16OrZero​

Like toInt16, this function converts an input value to a value of type Int16 but returns 0 in case of an error.

Syntax

toInt16OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toInt16OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered as an error.

Returned value

  • 16-bit integer value if successful, otherwise 0. Int16.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt16OrZero('-16'),
    toInt16OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt16OrZero('-16'): -16
toInt16OrZero('abc'): 0

See also

toInt16OrNull​

Like toInt16, this function converts an input value to a value of type Int16 but returns NULL in case of an error.

Syntax

toInt16OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 16-bit integer value if successful, otherwise NULL. Int16 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt16OrNull('-16'),
    toInt16OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt16OrNull('-16'): -16
toInt16OrNull('abc'): ᴺᵁᴸᴸ

See also

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 — Expression returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type Int16 is unsuccessful. Int16.

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 toInt16OrDefault('0xc0fe', CAST('-1', 'Int16'));.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 16-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int16.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt16OrDefault('-16', CAST('-1', 'Int16')),
    toInt16OrDefault('abc', CAST('-1', 'Int16'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt16OrDefault('-16', CAST('-1', 'Int16')): -16
toInt16OrDefault('abc', CAST('-1', 'Int16')): -1

See also

toInt32​

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

Syntax

toInt32(expr)

Arguments

  • expr — 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 toInt32('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int32, the result over or under flows. This is not considered an error. For example: SELECT toInt32(2147483648) == -2147483648;

Returned value

  • 32-bit integer value. Int32.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt32(-32),
    toInt32(-32.32),
    toInt32('-32')
FORMAT Vertical;

Result:

Row 1:
──────
toInt32(-32):    -32
toInt32(-32.32): -32
toInt32('-32'):  -32

See also

toInt32OrZero​

Like toInt32, this function converts an input value to a value of type Int32 but returns 0 in case of an error.

Syntax

toInt32OrZero(x)

Arguments

  • x — A String representation of a number. String.

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');.
Note

If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncate fractional digits of numbers.

Example

Query:

SELECT
    toInt32OrZero('-32'),
    toInt32OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt32OrZero('-32'): -32
toInt32OrZero('abc'): 0

See also

toInt32OrNull​

Like toInt32, this function converts an input value to a value of type Int32 but returns NULL in case of an error.

Syntax

toInt32OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt32OrNull('-32'),
    toInt32OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt32OrNull('-32'): -32
toInt32OrNull('abc'): ᴺᵁᴸᴸ

See also

toInt32OrDefault​

Like toInt32, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type Int32 is unsuccessful. Int32.

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'));.
Note

If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 32-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int32.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt32OrDefault('-32', CAST('-1', 'Int32')),
    toInt32OrDefault('abc', CAST('-1', 'Int32'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt32OrDefault('-32', CAST('-1', 'Int32')): -32
toInt32OrDefault('abc', CAST('-1', 'Int32')): -1

See also

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. Expression.

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 toInt64('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int64, the result over or under flows. This is not considered an error. For example: SELECT toInt64(9223372036854775808) == -9223372036854775808;

Returned value

  • 64-bit integer value. Int64.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt64(-64),
    toInt64(-64.64),
    toInt64('-64')
FORMAT Vertical;

Result:

Row 1:
──────
toInt64(-64):    -64
toInt64(-64.64): -64
toInt64('-64'):  -64

See also

toInt64OrZero​

Like toInt64, this function converts an input value to a value of type Int64 but returns 0 in case of an error.

Syntax

toInt64OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toInt64OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 64-bit integer value if successful, otherwise 0. Int64.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt64OrZero('-64'),
    toInt64OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt64OrZero('-64'): -64
toInt64OrZero('abc'): 0

See also

toInt64OrNull​

Like toInt64, this function converts an input value to a value of type Int64 but returns NULL in case of an error.

Syntax

toInt64OrNull(x)

Arguments

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 64-bit integer value if successful, otherwise NULL. Int64 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt64OrNull('-64'),
    toInt64OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt64OrNull('-64'): -64
toInt64OrNull('abc'): ᴺᵁᴸᴸ

See also

toInt64OrDefault​

Like toInt64, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type Int64 is unsuccessful. Int64.

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'));.
Note

If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 64-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int64.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt64OrDefault('-64', CAST('-1', 'Int64')),
    toInt64OrDefault('abc', CAST('-1', 'Int64'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt64OrDefault('-64', CAST('-1', 'Int64')): -64
toInt64OrDefault('abc', CAST('-1', 'Int64')): -1

See also

toInt128​

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

Syntax

toInt128(expr)

Arguments

  • expr — 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 toInt128('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int128, the result over or under flows. This is not considered an error.

Returned value

  • 128-bit integer value. Int128.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt128(-128),
    toInt128(-128.8),
    toInt128('-128')
FORMAT Vertical;

Result:

Row 1:
──────
toInt128(-128):   -128
toInt128(-128.8): -128
toInt128('-128'): -128

See also

toInt128OrZero​

Like toInt128, this function converts an input value to a value of type Int128 but returns 0 in case of an error.

Syntax

toInt128OrZero(expr)

Arguments

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

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 toInt128OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 128-bit integer value if successful, otherwise 0. Int128.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt128OrZero('-128'),
    toInt128OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt128OrZero('-128'): -128
toInt128OrZero('abc'):  0

See also

toInt128OrNull​

Like toInt128, this function converts an input value to a value of type Int128 but returns NULL in case of an error.

Syntax

toInt128OrNull(x)

Arguments

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 128-bit integer value if successful, otherwise NULL. Int128 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt128OrNull('-128'),
    toInt128OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt128OrNull('-128'): -128
toInt128OrNull('abc'):  ᴺᵁᴸᴸ

See also

toInt128OrDefault​

Like toInt128, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type Int128 is unsuccessful. Int128.

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 toInt128OrDefault('0xc0fe', CAST('-1', 'Int128'));.
Note

If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 128-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int128.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt128OrDefault('-128', CAST('-1', 'Int128')),
    toInt128OrDefault('abc', CAST('-1', 'Int128'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt128OrDefault('-128', CAST('-1', 'Int128')): -128
toInt128OrDefault('abc', CAST('-1', 'Int128')):  -1

See also

toInt256​

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

Syntax

toInt256(expr)

Arguments

  • expr — 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 toInt256('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int256, the result over or under flows. This is not considered an error.

Returned value

  • 256-bit integer value. Int256.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt256(-256),
    toInt256(-256.256),
    toInt256('-256')
FORMAT Vertical;

Result:

Row 1:
──────
toInt256(-256):     -256
toInt256(-256.256): -256
toInt256('-256'):   -256

See also

toInt256OrZero​

Like toInt256, this function converts an input value to a value of type Int256 but returns 0 in case of an error.

Syntax

toInt256OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toInt256OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 256-bit integer value if successful, otherwise 0. Int256.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt256OrZero('-256'),
    toInt256OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt256OrZero('-256'): -256
toInt256OrZero('abc'):  0

See also

toInt256OrNull​

Like toInt256, this function converts an input value to a value of type Int256 but returns NULL in case of an error.

Syntax

toInt256OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 256-bit integer value if successful, otherwise NULL. Int256 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toInt256OrNull('-256'),
    toInt256OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toInt256OrNull('-256'): -256
toInt256OrNull('abc'):  ᴺᵁᴸᴸ

See also

toInt256OrDefault​

Like toInt256, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type Int256 is unsuccessful. Int256.

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'));
Note

If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 256-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int256.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toInt256OrDefault('-256', CAST('-1', 'Int256')),
    toInt256OrDefault('abc', CAST('-1', 'Int256'))
FORMAT Vertical;

Result:

Row 1:
──────
toInt256OrDefault('-256', CAST('-1', 'Int256')): -256
toInt256OrDefault('abc', CAST('-1', 'Int256')):  -1

See also

toUInt8​

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

Syntax

toUInt8(expr)

Arguments

  • expr — 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 toUInt8('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt8(256) == 0;.

Returned value

  • 8-bit unsigned integer value. UInt8.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt8(8),
    toUInt8(8.8),
    toUInt8('8')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt8(8):   8
toUInt8(8.8): 8
toUInt8('8'): 8

See also

toUInt8OrZero​

Like toUInt8, this function converts an input value to a value of type UInt8 but returns 0 in case of an error.

Syntax

toUInt8OrZero(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return 0):

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

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt8OrZero('-8'),
    toUInt8OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt8OrZero('-8'):  0
toUInt8OrZero('abc'): 0

See also

toUInt8OrNull​

Like toUInt8, this function converts an input value to a value of type UInt8 but returns NULL in case of an error.

Syntax

toUInt8OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt8OrNull('8'),
    toUInt8OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt8OrNull('8'):   8
toUInt8OrNull('abc'): ᴺᵁᴸᴸ

See also

toUInt8OrDefault​

Like toUInt8, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type UInt8 is unsuccessful. UInt8.

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'));.
Note

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 8-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt8.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt8OrDefault('8', CAST('0', 'UInt8')),
    toUInt8OrDefault('abc', CAST('0', 'UInt8'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt8OrDefault('8', CAST('0', 'UInt8')):   8
toUInt8OrDefault('abc', CAST('0', 'UInt8')): 0

See also

toUInt16​

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

Syntax

toUInt16(expr)

Arguments

  • expr — 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 toUInt16('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt16(65536) == 0;.

Returned value

  • 16-bit unsigned integer value. UInt16.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt16(16),
    toUInt16(16.16),
    toUInt16('16')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt16(16):    16
toUInt16(16.16): 16
toUInt16('16'):  16

See also

toUInt16OrZero​

Like toUInt16, this function converts an input value to a value of type UInt16 but returns 0 in case of an error.

Syntax

toUInt16OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toUInt16OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered as an error.

Returned value

  • 16-bit unsigned integer value if successful, otherwise 0. UInt16.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt16OrZero('16'),
    toUInt16OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt16OrZero('16'):  16
toUInt16OrZero('abc'): 0

See also

toUInt16OrNull​

Like toUInt16, this function converts an input value to a value of type UInt16 but returns NULL in case of an error.

Syntax

toUInt16OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 16-bit unsigned integer value if successful, otherwise NULL. UInt16 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt16OrNull('16'),
    toUInt16OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt16OrNull('16'):  16
toUInt16OrNull('abc'): ᴺᵁᴸᴸ

See also

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 — Expression returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type UInt16 is unsuccessful. UInt16.

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 toUInt16OrDefault('0xc0fe', CAST('0', 'UInt16'));.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 16-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt16.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt16OrDefault('16', CAST('0', 'UInt16')),
    toUInt16OrDefault('abc', CAST('0', 'UInt16'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt16OrDefault('16', CAST('0', 'UInt16')):  16
toUInt16OrDefault('abc', CAST('0', 'UInt16')): 0

See also

toUInt32​

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

Syntax

toUInt32(expr)

Arguments

  • expr — 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');.
Note

If the input value cannot be represented within the bounds of UInt32, the result over or under flows. This is not considered an error. For example: SELECT toUInt32(4294967296) == 0;

Returned value

  • 32-bit unsigned integer value. UInt32.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt32(32),
    toUInt32(32.32),
    toUInt32('32')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt32(32):    32
toUInt32(32.32): 32
toUInt32('32'):  32

See also

toUInt32OrZero​

Like toUInt32, this function converts an input value to a value of type UInt32 but returns 0 in case of an error.

Syntax

toUInt32OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toUInt32OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero , meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt32OrZero('32'),
    toUInt32OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt32OrZero('32'):  32
toUInt32OrZero('abc'): 0

See also

toUInt32OrNull​

Like toUInt32, this function converts an input value to a value of type UInt32 but returns NULL in case of an error.

Syntax

toUInt32OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero , meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt32OrNull('32'),
    toUInt32OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt32OrNull('32'):  32
toUInt32OrNull('abc'): ᴺᵁᴸᴸ

See also

toUInt32OrDefault​

Like toUInt32, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type UInt32 is unsuccessful. UInt32.

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'));.
Note

If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 32-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt32.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt32OrDefault('32', CAST('0', 'UInt32')),
    toUInt32OrDefault('abc', CAST('0', 'UInt32'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt32OrDefault('32', CAST('0', 'UInt32')):  32
toUInt32OrDefault('abc', CAST('0', 'UInt32')): 0

See also

toUInt64​

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

Syntax

toUInt64(expr)

Arguments

  • expr — 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 types:

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

If the input value cannot be represented within the bounds of UInt64, the result over or under flows. This is not considered an error. For example: SELECT toUInt64(18446744073709551616) == 0;

Returned value

  • 64-bit unsigned integer value. UInt64.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt64(64),
    toUInt64(64.64),
    toUInt64('64')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt64(64):    64
toUInt64(64.64): 64
toUInt64('64'):  64

See also

toUInt64OrZero​

Like toUInt64, this function converts an input value to a value of type UInt64 but returns 0 in case of an error.

Syntax

toUInt64OrZero(x)

Arguments

  • x — A String representation of a number. String.

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 toUInt64OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt64OrZero('64'),
    toUInt64OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt64OrZero('64'):  64
toUInt64OrZero('abc'): 0

See also

toUInt64OrNull​

Like toUInt64, this function converts an input value to a value of type UInt64 but returns NULL in case of an error.

Syntax

toUInt64OrNull(x)

Arguments

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 64-bit unsigned integer value if successful, otherwise NULL. UInt64 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt64OrNull('64'),
    toUInt64OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt64OrNull('64'):  64
toUInt64OrNull('abc'): ᴺᵁᴸᴸ

See also

toUInt64OrDefault​

Like toUInt64, this function 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 returning a number or a string representation of a number. Expression / String.
  • defauult (optional) — The default value to return if parsing to type UInt64 is unsuccessful. UInt64.

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'));.
Note

If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 64-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt64.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt64OrDefault('64', CAST('0', 'UInt64')),
    toUInt64OrDefault('abc', CAST('0', 'UInt64'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt64OrDefault('64', CAST('0', 'UInt64')):  64
toUInt64OrDefault('abc', CAST('0', 'UInt64')): 0

See also

toUInt128​

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

Syntax

toUInt128(expr)

Arguments

  • expr — 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 toUInt128('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt128, the result over or under flows. This is not considered an error.

Returned value

  • 128-bit unsigned integer value. UInt128.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt128(128),
    toUInt128(128.8),
    toUInt128('128')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt128(128):   128
toUInt128(128.8): 128
toUInt128('128'): 128

See also

toUInt128OrZero​

Like toUInt128, this function converts an input value to a value of type UInt128 but returns 0 in case of an error.

Syntax

toUInt128OrZero(expr)

Arguments

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

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');.
Note

If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt128OrZero('128'),
    toUInt128OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt128OrZero('128'): 128
toUInt128OrZero('abc'): 0

See also

toUInt128OrNull​

Like toUInt128, this function converts an input value to a value of type UInt128 but returns NULL in case of an error.

Syntax

toUInt128OrNull(x)

Arguments

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 128-bit unsigned integer value if successful, otherwise NULL. UInt128 / NULL.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt128OrNull('128'),
    toUInt128OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt128OrNull('128'): 128
toUInt128OrNull('abc'): ᴺᵁᴸᴸ

See also

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 — Expression returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type UInt128 is unsuccessful. UInt128.

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'));.
Note

If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 128-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt128.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt128OrDefault('128', CAST('0', 'UInt128')),
    toUInt128OrDefault('abc', CAST('0', 'UInt128'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt128OrDefault('128', CAST('0', 'UInt128')): 128
toUInt128OrDefault('abc', CAST('0', 'UInt128')): 0

See also

toUInt256​

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

Syntax

toUInt256(expr)

Arguments

  • expr — 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 toUInt256('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt256, the result over or under flows. This is not considered an error.

Returned value

  • 256-bit unsigned integer value. Int256.
Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt256(256),
    toUInt256(256.256),
    toUInt256('256')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt256(256):     256
toUInt256(256.256): 256
toUInt256('256'):   256

See also

toUInt256OrZero​

Like toUInt256, this function converts an input value to a value of type UInt256 but returns 0 in case of an error.

Syntax

toUInt256OrZero(x)

Arguments

  • x — A String representation of a number. String.

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');.
Note

If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt256OrZero('256'),
    toUInt256OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt256OrZero('256'): 256
toUInt256OrZero('abc'): 0

See also

toUInt256OrNull​

Like toUInt256, this function converts an input value to a value of type UInt256 but returns NULL in case of an error.

Syntax

toUInt256OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N)

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

If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

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

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Example

Query:

SELECT
    toUInt256OrNull('256'),
    toUInt256OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toUInt256OrNull('256'): 256
toUInt256OrNull('abc'): ᴺᵁᴸᴸ

See also

toUInt256OrDefault​

Like toUInt256, this function 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 returning a number or a string representation of a number. Expression / String.
  • default (optional) — The default value to return if parsing to type UInt256 is unsuccessful. UInt256.

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'));
Note

If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.

Returned value

  • 256-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt256.
Note
  • The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
  • The default value type should be the same as the cast type.

Example

Query:

SELECT
    toUInt256OrDefault('-256', CAST('0', 'UInt256')),
    toUInt256OrDefault('abc', CAST('0', 'UInt256'))
FORMAT Vertical;

Result:

Row 1:
──────
toUInt256OrDefault('-256', CAST('0', 'UInt256')): 0
toUInt256OrDefault('abc', CAST('0', 'UInt256')):  0

See also

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. Expression.

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float32/64, including NaN and Inf.
  • String representations of Float32/64, including NaN and Inf (case-insensitive).

Unsupported arguments:

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

Returned value

  • 32-bit floating point value. Float32.

Example

Query:

SELECT
    toFloat32(42.7),
    toFloat32('42.7'),
    toFloat32('NaN')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat32(42.7):   42.7
toFloat32('42.7'): 42.7
toFloat32('NaN'):  nan

See also

toFloat32OrZero​

Like toFloat32, this function converts an input value to a value of type Float32 but returns 0 in case of an error.

Syntax

toFloat32OrZero(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return 0):

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

Returned value

  • 32-bit Float value if successful, otherwise 0. Float32.

Example

Query:

SELECT
    toFloat32OrZero('42.7'),
    toFloat32OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat32OrZero('42.7'): 42.7
toFloat32OrZero('abc'):  0

See also

toFloat32OrNull​

Like toFloat32, this function converts an input value to a value of type Float32 but returns NULL in case of an error.

Syntax

toFloat32OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N):

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

Returned value

  • 32-bit Float value if successful, otherwise \N. Float32.

Example

Query:

SELECT
    toFloat32OrNull('42.7'),
    toFloat32OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat32OrNull('42.7'): 42.7
toFloat32OrNull('abc'):  ᴺᵁᴸᴸ

See also

toFloat32OrDefault​

Like toFloat32, this function converts an input value to a value of type Float32 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

toFloat32OrDefault(expr[, default])

Arguments

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

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float32/64, including NaN and Inf.
  • String representations of Float32/64, including NaN and Inf (case-insensitive).

Arguments for which the default value is returned:

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

Returned value

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

Example

Query:

SELECT
    toFloat32OrDefault('8', CAST('0', 'Float32')),
    toFloat32OrDefault('abc', CAST('0', 'Float32'))
FORMAT Vertical;

Result:

Row 1:
──────
toFloat32OrDefault('8', CAST('0', 'Float32')):   8
toFloat32OrDefault('abc', CAST('0', 'Float32')): 0

See also

toFloat64​

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

Syntax

toFloat64(expr)

Arguments

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

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float32/64, including NaN and Inf.
  • String representations of type Float32/64, including NaN and Inf (case-insensitive).

Unsupported arguments:

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

Returned value

  • 64-bit floating point value. Float64.

Example

Query:

SELECT
    toFloat64(42.7),
    toFloat64('42.7'),
    toFloat64('NaN')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat64(42.7):   42.7
toFloat64('42.7'): 42.7
toFloat64('NaN'):  nan

See also

toFloat64OrZero​

Like toFloat64, this function converts an input value to a value of type Float64 but returns 0 in case of an error.

Syntax

toFloat64OrZero(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return 0):

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

Returned value

  • 64-bit Float value if successful, otherwise 0. Float64.

Example

Query:

SELECT
    toFloat64OrZero('42.7'),
    toFloat64OrZero('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat64OrZero('42.7'): 42.7
toFloat64OrZero('abc'):  0

See also

toFloat64OrNull​

Like toFloat64, this function converts an input value to a value of type Float64 but returns NULL in case of an error.

Syntax

toFloat64OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

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

Unsupported arguments (return \N):

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

Returned value

  • 64-bit Float value if successful, otherwise \N. Float64.

Example

Query:

SELECT
    toFloat64OrNull('42.7'),
    toFloat64OrNull('abc')
FORMAT Vertical;

Result:

Row 1:
──────
toFloat64OrNull('42.7'): 42.7
toFloat64OrNull('abc'):  ᴺᵁᴸᴸ

See also

toFloat64OrDefault​

Like toFloat64, this function converts an input value to a value of type Float64 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

toFloat64OrDefault(expr[, default])

Arguments

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

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float32/64, including NaN and Inf.
  • String representations of Float32/64, including NaN and Inf (case-insensitive).

Arguments for which the default value is returned:

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

Returned value

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

Example

Query:

SELECT
    toFloat64OrDefault('8', CAST('0', 'Float64')),
    toFloat64OrDefault('abc', CAST('0', 'Float64'))
FORMAT Vertical;

Result:

Row 1:
──────
toFloat64OrDefault('8', CAST('0', 'Float64')):   8
toFloat64OrDefault('abc', CAST('0', 'Float64')): 0

See also

toBFloat16​

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

Syntax

toBFloat16(expr)

Arguments

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

Supported arguments:

  • Values of type (U)Int8/16/32/64/128/256.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float32/64, including NaN and Inf.
  • String representations of Float32/64, including NaN and Inf (case-insensitive).

Returned value

Example

SELECT toBFloat16(toFloat32(42.7))

42.5

SELECT toBFloat16(toFloat32('42.7'));

42.5

SELECT toBFloat16('42.7');

42.5

See also

toBFloat16OrZero​

Converts a String input value to a value of type BFloat16. If the string does not represent a floating point value, the function returns zero.

Syntax

toBFloat16OrZero(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

  • String representations of numeric values.

Unsupported arguments (return 0):

  • String representations of binary and hexadecimal values.
  • Numeric values.

Returned value

  • 16-bit brain-float value, otherwise 0. BFloat16.
Note

The function allows a silent loss of precision while converting from the string representation.

Example

SELECT toBFloat16OrZero('0x5E'); -- unsupported arguments

0

SELECT toBFloat16OrZero('12.3'); -- typical use

12.25

SELECT toBFloat16OrZero('12.3456789');

12.3125 -- silent loss of precision

See also

toBFloat16OrNull​

Converts a String input value to a value of type BFloat16 but if the string does not represent a floating point value, the function returns NULL.

Syntax

toBFloat16OrNull(x)

Arguments

  • x — A String representation of a number. String.

Supported arguments:

  • String representations of numeric values.

Unsupported arguments (return NULL):

  • String representations of binary and hexadecimal values.
  • Numeric values.

Returned value

  • 16-bit brain-float value, otherwise NULL (\N). BFloat16.
Note

The function allows a silent loss of precision while converting from the string representation.

Example

SELECT toBFloat16OrNull('0x5E'); -- unsupported arguments

\N

SELECT toBFloat16OrNull('12.3'); -- typical use

12.25

SELECT toBFloat16OrNull('12.3456789');

12.3125 -- silent loss of precision

See also

toDate​

Converts the argument to Date data type.

If the argument is DateTime or DateTime64, it truncates it and leaves the date component of the DateTime:

SELECT
    now() AS x,
    toDate(x)

┌───────────────────x─┬─toDate(now())─┐
│ 2022-12-30 13:44:17 │    2022-12-30 │
└─────────────────────┴───────────────┘

If the argument is a String, it is parsed as Date or DateTime. If it was parsed as DateTime, the date component is being used:

SELECT
    toDate('2022-12-30') AS x,
    toTypeName(x)

┌──────────x─┬─toTypeName(toDate('2022-12-30'))─┐
│ 2022-12-30 │ Date                             │
└────────────┴──────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

SELECT
    toDate('2022-12-30 01:02:03') AS x,
    toTypeName(x)

┌──────────x─┬─toTypeName(toDate('2022-12-30 01:02:03'))─┐
│ 2022-12-30 │ Date                                      │
└────────────┴───────────────────────────────────────────┘

If the argument is a number and looks like a UNIX timestamp (is greater than 65535), it is interpreted as a DateTime, then truncated to Date in the current timezone. The timezone argument can be specified as a second argument of the function. The truncation to Date depends on the timezone:

SELECT
    now() AS current_time,
    toUnixTimestamp(current_time) AS ts,
    toDateTime(ts) AS time_Amsterdam,
    toDateTime(ts, 'Pacific/Apia') AS time_Samoa,
    toDate(time_Amsterdam) AS date_Amsterdam,
    toDate(time_Samoa) AS date_Samoa,
    toDate(ts) AS date_Amsterdam_2,
    toDate(ts, 'Pacific/Apia') AS date_Samoa_2

Row 1:
──────
current_time:     2022-12-30 13:51:54
ts:               1672404714
time_Amsterdam:   2022-12-30 13:51:54
time_Samoa:       2022-12-31 01:51:54
date_Amsterdam:   2022-12-30
date_Samoa:       2022-12-31
date_Amsterdam_2: 2022-12-30
date_Samoa_2:     2022-12-31

The example above demonstrates how the same UNIX timestamp can be interpreted as different dates in different time zones.

If the argument is a number and it is smaller than 65536, it is interpreted as the number of days since 1970-01-01 (the first UNIX day) and converted to Date. It corresponds to the internal numeric representation of the Date data type. Example:

SELECT toDate(12345)

┌─toDate(12345)─┐
│    2003-10-20 │
└───────────────┘

This conversion does not depend on timezones.

If the argument does not fit in the range of the Date type, it results in an implementation-defined behavior, that can saturate to the maximum supported date or overflow:

SELECT toDate(10000000000.)

┌─toDate(10000000000.)─┐
│           2106-02-07 │
└──────────────────────┘

The function toDate can be also written in alternative forms:

SELECT
    now() AS time,
    toDate(time),
    DATE(time),
    CAST(time, 'Date')

┌────────────────time─┬─toDate(now())─┬─DATE(now())─┬─CAST(now(), 'Date')─┐
│ 2022-12-30 13:54:58 │    2022-12-30 │  2022-12-30 │          2022-12-30 │
└─────────────────────┴───────────────┴─────────────┴─────────────────────┘

toDateOrZero​

The same as toDate but returns lower boundary of Date if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrZero('2022-12-30'), toDateOrZero('');

Result:

┌─toDateOrZero('2022-12-30')─┬─toDateOrZero('')─┐
│                 2022-12-30 │       1970-01-01 │
└────────────────────────────┴──────────────────┘

toDateOrNull​

The same as toDate but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrNull('2022-12-30'), toDateOrNull('');

Result:

┌─toDateOrNull('2022-12-30')─┬─toDateOrNull('')─┐
│                 2022-12-30 │             ᴺᵁᴸᴸ │
└────────────────────────────┴──────────────────┘

toDateOrDefault​

Like toDate but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of Date.

Syntax

toDateOrDefault(expr [, default_value])

Example

Query:

SELECT toDateOrDefault('2022-12-30'), toDateOrDefault('', '2023-01-01'::Date);

Result:

┌─toDateOrDefault('2022-12-30')─┬─toDateOrDefault('', CAST('2023-01-01', 'Date'))─┐
│                    2022-12-30 │                                      2023-01-01 │
└───────────────────────────────┴─────────────────────────────────────────────────┘

toDateTime​

Converts an input value to DateTime.

Syntax

toDateTime(expr[, time_zone ])

Arguments

Note

If expr is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp). If expr is a String, it may be interpreted as a Unix timestamp or as a string representation of date / date with time. Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string '1999' may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.

Returned value

Example

Query:

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 │
└───────────────────────────────────┴───────────────────────────────┘

toDateTimeOrZero​

The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('');

Result:

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

toDateTimeOrNull​

The same as toDateTime but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('');

Result:

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

toDateTimeOrDefault​

Like toDateTime but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of DateTime.

Syntax

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

Example

Query:

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

Result:

┌─toDateTimeOrDefault('2022-12-30 13:44:17')─┬─toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))─┐
│                        2022-12-30 13:44:17 │                                                     2023-01-01 00:00:00 │
└────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘

toDate32​

Converts the argument to the Date32 data type. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, it's borders are taken into account.

Syntax

toDate32(expr)

Arguments

Returned value

  • A calendar date. Type Date32.

Example

  1. The value is within the range:
SELECT toDate32('1955-01-01') AS value, toTypeName(value);

┌──────value─┬─toTypeName(toDate32('1925-01-01'))─┐
│ 1955-01-01 │ Date32                             │
└────────────┴────────────────────────────────────┘
  1. The value is outside the range:
SELECT toDate32('1899-01-01') AS value, toTypeName(value);

┌──────value─┬─toTypeName(toDate32('1899-01-01'))─┐
│ 1900-01-01 │ Date32                             │
└────────────┴────────────────────────────────────┘
  1. With Date argument:
SELECT toDate32(toDate('1899-01-01')) AS value, toTypeName(value);

┌──────value─┬─toTypeName(toDate32(toDate('1899-01-01')))─┐
│ 1970-01-01 │ Date32                                     │
└────────────┴────────────────────────────────────────────┘

toDate32OrZero​

The same as toDate32 but returns the min value of Date32 if an invalid argument is received.

Example

Query:

SELECT toDate32OrZero('1899-01-01'), toDate32OrZero('');

Result:

┌─toDate32OrZero('1899-01-01')─┬─toDate32OrZero('')─┐
│                   1900-01-01 │         1900-01-01 │
└──────────────────────────────┴────────────────────┘

toDate32OrNull​

The same as toDate32 but returns NULL if an invalid argument is received.

Example

Query:

SELECT toDate32OrNull('1955-01-01'), toDate32OrNull('');

Result:

┌─toDate32OrNull('1955-01-01')─┬─toDate32OrNull('')─┐
│                   1955-01-01 │               ᴺᵁᴸᴸ │
└──────────────────────────────┴────────────────────┘

toDate32OrDefault​

Converts the argument to the Date32 data type. If the value is outside the range, toDate32OrDefault returns the lower border value supported by Date32. If the argument has Date type, it's borders are taken into account. Returns default value if an invalid argument is received.

Example

Query:

SELECT
    toDate32OrDefault('1930-01-01', toDate32('2020-01-01')),
    toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'));

Result:

┌─toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))─┬─toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))─┐
│                                              1930-01-01 │                                                2020-01-01 │
└─────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘

toDateTime64​

Converts an input value to a value of type DateTime64.

Syntax

toDateTime64(expr, scale, [timezone])

Arguments

  • expr — The value. String, UInt32, Float or DateTime.
  • scale - Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].
  • timezone (optional) - Time zone of the specified datetime64 object.

Returned value

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

Example

  1. The value is within the range:
SELECT toDateTime64('1955-01-01 00:00:00.000', 3) AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(toDateTime64('1955-01-01 00:00:00.000', 3))─┐
│ 1955-01-01 00:00:00.000 │ DateTime64(3)                                          │
└─────────────────────────┴────────────────────────────────────────────────────────┘
  1. As decimal with precision:
SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800., 3))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3)                            │
└─────────────────────────┴──────────────────────────────────────────┘

Without the decimal point the value is still treated as Unix Timestamp in seconds:

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

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘
  1. With timezone:
SELECT toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul')                                      │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘

toDateTime64OrZero​

Like toDateTime64, this function converts an input value to a value of type DateTime64 but returns the min value of DateTime64 if an invalid argument is received.

Syntax

toDateTime64OrZero(expr, scale, [timezone])

Arguments

  • expr — The value. String, UInt32, Float or DateTime.
  • scale - Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].
  • timezone (optional) - Time zone of the specified DateTime64 object.

Returned value

  • A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64: 1970-01-01 01: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 01:00:00.000 │
└─────────────────────────┘

See also

toDateTime64OrNull​

Like toDateTime64, this function 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 — The value. String, UInt32, Float or DateTime.
  • scale - Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].
  • timezone (optional) - Time zone of the specified DateTime64 object.

Returned value

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

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 │        ᴺᵁᴸᴸ │
└─────────────────────────┴─────────────┘

See also

toDateTime64OrDefault​

Like toDateTime64, this function 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

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

Arguments

  • expr — The value. String, UInt32, Float or DateTime.
  • scale - Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].
  • timezone (optional) - Time zone of the specified DateTime64 object.
  • default (optional) - Default value to return if an invalid argument is received. DateTime64.

Returned value

  • 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 │
└─────────────────────────┴──────────────────────────┘

See also

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 — Expression returning a number or a string representation of a number. Expression.
  • S — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

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 toDecimal32('0xc0fe', 1);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal32: ( -1 * 10^(9 - S), 1 * 10^(9 - 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: toDecimal32(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: toDecimal32('1.15', 2) = 1.15

Returned value

Example

Query:

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
FORMAT Vertical;

Result:

Row 1:
──────
a:      2
type_a: Decimal(9, 1)
b:      4.2
type_b: Decimal(9, 2)
c:      4.2
type_c: Decimal(9, 3)

See also

toDecimal32OrZero​

Like toDecimal32, this function converts an input value to a value of type Decimal(9, S) but returns 0 in case of an error.

Syntax

toDecimal32OrZero(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Decimal(9, S) if successful, otherwise 0 with S decimal places. Decimal32(S).

Example

Query:

SELECT
    toDecimal32OrZero(toString(-1.111), 5) AS a,
    toTypeName(a),
    toDecimal32OrZero(toString('Inf'), 5) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             -1.111
toTypeName(a): Decimal(9, 5)
b:             0
toTypeName(b): Decimal(9, 5)

See also

toDecimal32OrNull​

Like toDecimal32, this function converts an input value to a value of type Nullable(Decimal(9, S)) but returns 0 in case of an error.

Syntax

toDecimal32OrNull(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Nullable(Decimal(9, S)) if successful, otherwise value NULL of the same type. Decimal32(S).

Examples

Query:

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

Result:

Row 1:
──────
a:             -1.111
toTypeName(a): Nullable(Decimal(9, 5))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(9, 5))

See also

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.

Syntax

toDecimal32OrDefault(expr, S[, default])

Arguments

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

Supported arguments:

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

Unsupported arguments:

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

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

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: toDecimal32OrDefault(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: toDecimal32OrDefault('1.15', 2) = 1.15

Returned value

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

Examples

Query:

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

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(9, 5)
b:             -1
toTypeName(b): Decimal(9, 0)

See also

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 — Expression returning a number or a string representation of a number. Expression.
  • S — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

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);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal64: ( -1 * 10^(18 - S), 1 * 10^(18 - 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: toDecimal64(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: toDecimal64('1.15', 2) = 1.15

Returned value

Example

Query:

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
FORMAT Vertical;

Result:

Row 1:
──────
a:      2
type_a: Decimal(18, 1)
b:      4.2
type_b: Decimal(18, 2)
c:      4.2
type_c: Decimal(18, 3)

See also

toDecimal64OrZero​

Like toDecimal64, this function converts an input value to a value of type Decimal(18, S) but returns 0 in case of an error.

Syntax

toDecimal64OrZero(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Decimal(18, S) if successful, otherwise 0 with S decimal places. Decimal64(S).

Example

Query:

SELECT
    toDecimal64OrZero(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrZero(toString('Inf'), 18) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(18, 18)
b:             0
toTypeName(b): Decimal(18, 18)

See also

toDecimal64OrNull​

Like toDecimal64, this function converts an input value to a value of type Nullable(Decimal(18, S)) but returns 0 in case of an error.

Syntax

toDecimal64OrNull(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Nullable(Decimal(18, S)) if successful, otherwise value NULL of the same type. Decimal64(S).

Examples

Query:

SELECT
    toDecimal64OrNull(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrNull(toString('Inf'), 18) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Nullable(Decimal(18, 18))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(18, 18))

See also

toDecimal64OrDefault​

Like toDecimal64, this function converts an input value to a value of type Decimal(18, S) but returns the default value in case of an error.

Syntax

toDecimal64OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.
  • default (optional) — The default value to return if parsing to type Decimal64(S) is unsuccessful. Decimal64(S).

Supported arguments:

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

Unsupported arguments:

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

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

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: toDecimal64OrDefault(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: toDecimal64OrDefault('1.15', 2) = 1.15

Returned value

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

Examples

Query:

SELECT
    toDecimal64OrDefault(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)')) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(18, 18)
b:             -1
toTypeName(b): Decimal(18, 0)

See also

toDecimal128​

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

Syntax

toDecimal128(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression.
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

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

Returned value

Example

Query:

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;

Result:

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)

See also

toDecimal128OrZero​

Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns 0 in case of an error.

Syntax

toDecimal128OrZero(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float32/64 values NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrZero('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 error.

Returned value

  • Value of type Decimal(38, S) if successful, otherwise 0 with S decimal places. Decimal128(S).

Example

Query:

SELECT
    toDecimal128OrZero(toString(0.0001), 38) AS a,
    toTypeName(a),
    toDecimal128OrZero(toString('Inf'), 38) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(38, 38)
b:             0
toTypeName(b): Decimal(38, 38)

See also

toDecimal128OrNull​

Like toDecimal128, this function converts an input value to a value of type Nullable(Decimal(38, S)) but returns 0 in case of an error.

Syntax

toDecimal128OrNull(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float32/64 values NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrNull('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 error.

Returned value

  • Value of type Nullable(Decimal(38, S)) if successful, otherwise value NULL of the same type. Decimal128(S).

Examples

Query:

SELECT
    toDecimal128OrNull(toString(1/42), 38) AS a,
    toTypeName(a),
    toDecimal128OrNull(toString('Inf'), 38) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Nullable(Decimal(38, 38))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(38, 38))

See also

toDecimal128OrDefault​

Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns the default value in case of an error.

Syntax

toDecimal128OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.
  • default (optional) — The default value to return if parsing to type Decimal128(S) is unsuccessful. Decimal128(S).

Supported arguments:

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

Unsupported arguments:

  • String representations of Float32/64 values NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrDefault('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 error.

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: toDecimal128OrDefault(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: toDecimal128OrDefault('1.15', 2) = 1.15

Returned value

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

Examples

Query:

SELECT
    toDecimal128OrDefault(toString(1/42), 18) AS a,
    toTypeName(a),
    toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)')) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Decimal(38, 18)
b:             -1
toTypeName(b): Decimal(38, 0)

See also

toDecimal256​

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

Syntax

toDecimal256(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression.
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

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 toDecimal256('0xc0fe', 1);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal256: ( -1 * 10^(76 - S), 1 * 10^(76 - 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: toDecimal256(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: toDecimal256('1.15', 2) = 1.15

Returned value

Example

Query:

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

Result:

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

See also

toDecimal256OrZero​

Like toDecimal256, this function converts an input value to a value of type Decimal(76, S) but returns 0 in case of an error.

Syntax

toDecimal256OrZero(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Decimal(76, S) if successful, otherwise 0 with S decimal places. Decimal256(S).

Example

Query:

SELECT
    toDecimal256OrZero(toString(0.0001), 76) AS a,
    toTypeName(a),
    toDecimal256OrZero(toString('Inf'), 76) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(76, 76)
b:             0
toTypeName(b): Decimal(76, 76)

See also

toDecimal256OrNull​

Like toDecimal256, this function converts an input value to a value of type Nullable(Decimal(76, S)) but returns 0 in case of an error.

Syntax

toDecimal256OrNull(expr, S)

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

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

Unsupported arguments:

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

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

Returned value

  • Value of type Nullable(Decimal(76, S)) if successful, otherwise value NULL of the same type. Decimal256(S).

Examples

Query:

SELECT
    toDecimal256OrNull(toString(1/42), 76) AS a,
    toTypeName(a),
    toDecimal256OrNull(toString('Inf'), 76) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Nullable(Decimal(76, 76))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(76, 76))

See also

toDecimal256OrDefault​

Like toDecimal256, this function converts an input value to a value of type Decimal(76, S) but returns the default value in case of an error.

Syntax

toDecimal256OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String.
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.
  • default (optional) — The default value to return if parsing to type Decimal256(S) is unsuccessful. Decimal256(S).

Supported arguments:

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

Unsupported arguments:

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

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

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: toDecimal256OrDefault(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: toDecimal256OrDefault('1.15', 2) = 1.15

Returned value

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

Examples

Query:

SELECT
    toDecimal256OrDefault(toString(1/42), 76) AS a,
    toTypeName(a),
    toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)')) AS b,
    toTypeName(b)
FORMAT Vertical;

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Decimal(76, 76)
b:             -1
toTypeName(b): Decimal(76, 0)

See also

toString​

Converts values to their string representation. For DateTime arguments, the function can take a second String argument containing the name of the time zone.

Syntax

toString(value[, timezone])

Arguments

  • value: Value to convert to string. Any.
  • timezone: Optional. Timezone name for DateTime conversion. String.

Returned value

  • Returns a string representation of the input value. String.

Examples

Usage example

SELECT
    now() AS ts,
    time_zone,
    toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10;

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam  │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra    │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan  │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens     │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast    │ 2023-09-08 20:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘

toFixedString​

Converts a String type argument to a FixedString(N) type (a string of fixed length N). If the string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.

Syntax

toFixedString(s, N)

Arguments

  • s — A String to convert to a fixed string. String.
  • N — Length N. UInt8

Returned value

Example

Query:

SELECT toFixedString('foo', 8) AS s;

Result:

┌─s─────────────┐
│ foo\0\0\0\0\0 │
└───────────────┘

toStringCutToZero​

Accepts a String or FixedString argument. Returns the String with the content truncated at the first zero byte found.

Syntax

toStringCutToZero(s)

Example

Query:

SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut;

Result:

┌─s─────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo   │
└───────────────┴───────┘

Query:

SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut;

Result:

┌─s──────────┬─s_cut─┐
│ foo\0bar\0 │ foo   │
└────────────┴───────┘

toDecimalString​

Converts a numeric value to String with the number of fractional digits in the output specified by the user.

Syntax

toDecimalString(number, scale)

Arguments

  • number — Value to be represented as String, Int, UInt, Float, Decimal,
  • scale — Number of fractional digits, UInt8.
    • Maximum scale for Decimal and Int, UInt types is 77 (it is the maximum possible number of significant digits for Decimal),
    • Maximum scale for Float is 60.

Returned value

  • Input value represented as String with given number of fractional digits (scale). The number is rounded up or down according to common arithmetic in case requested scale is smaller than original number's scale.

Example

Query:

SELECT toDecimalString(CAST('64.32', 'Float64'), 5);

Result:

┌toDecimalString(CAST('64.32', 'Float64'), 5)─┐
│ 64.32000                                    │
└─────────────────────────────────────────────┘

reinterpretAsUInt8​

Performs byte reinterpretation by treating the input value as a value of type UInt8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt8(x)

Parameters

Returned value

  • Reinterpreted value x as UInt8. UInt8.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt8(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ UInt8           │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt16​

Performs byte reinterpretation by treating the input value as a value of type UInt16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt16(x)

Parameters

Returned value

  • Reinterpreted value x as UInt16. UInt16.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt16(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ UInt16          │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt32​

Performs byte reinterpretation by treating the input value as a value of type UInt32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt32(x)

Parameters

Returned value

  • Reinterpreted value x as UInt32. UInt32.

Example

Query:

SELECT
    toUInt16(257) AS x,
    toTypeName(x),
    reinterpretAsUInt32(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16        │ 257 │ UInt32          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt64​

Performs byte reinterpretation by treating the input value as a value of type UInt64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt64(x)

Parameters

Returned value

  • Reinterpreted value x as UInt64. UInt64.

Example

Query:

SELECT
    toUInt32(257) AS x,
    toTypeName(x),
    reinterpretAsUInt64(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32        │ 257 │ UInt64          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt128​

Performs byte reinterpretation by treating the input value as a value of type UInt128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt128(x)

Parameters

Returned value

  • Reinterpreted value x as UInt128. UInt128.

Example

Query:

SELECT
    toUInt64(257) AS x,
    toTypeName(x),
    reinterpretAsUInt128(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64        │ 257 │ UInt128         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt256​

Performs byte reinterpretation by treating the input value as a value of type UInt256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt256(x)

Parameters

Returned value

  • Reinterpreted value x as UInt256. UInt256.

Example

Query:

SELECT
    toUInt128(257) AS x,
    toTypeName(x),
    reinterpretAsUInt256(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128       │ 257 │ UInt256         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt8​

Performs byte reinterpretation by treating the input value as a value of type Int8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt8(x)

Parameters

Returned value

  • Reinterpreted value x as Int8. Int8.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt8(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ Int8            │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsInt16​

Performs byte reinterpretation by treating the input value as a value of type Int16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt16(x)

Parameters

Returned value

  • Reinterpreted value x as Int16. Int16.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt16(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ Int16           │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsInt32​

Performs byte reinterpretation by treating the input value as a value of type Int32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt32(x)

Parameters

Returned value

  • Reinterpreted value x as Int32. Int32.

Example

Query:

SELECT
    toInt16(257) AS x,
    toTypeName(x),
    reinterpretAsInt32(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16         │ 257 │ Int32           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt64​

Performs byte reinterpretation by treating the input value as a value of type Int64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt64(x)

Parameters

Returned value

  • Reinterpreted value x as Int64. Int64.

Example

Query:

SELECT
    toInt32(257) AS x,
    toTypeName(x),
    reinterpretAsInt64(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32         │ 257 │ Int64           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt128​

Performs byte reinterpretation by treating the input value as a value of type Int128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt128(x)

Parameters

Returned value

  • Reinterpreted value x as Int128. Int128.

Example

Query:

SELECT
    toInt64(257) AS x,
    toTypeName(x),
    reinterpretAsInt128(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64         │ 257 │ Int128          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt256​

Performs byte reinterpretation by treating the input value as a value of type Int256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt256(x)

Parameters

Returned value

  • Reinterpreted value x as Int256. Int256.

Example

Query:

SELECT
    toInt128(257) AS x,
    toTypeName(x),
    reinterpretAsInt256(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int128        │ 257 │ Int256          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsFloat32​

Performs byte reinterpretation by treating the input value as a value of type Float32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsFloat32(x)

Parameters

Returned value

  • Reinterpreted value x as Float32. Float32.

Example

Query:

SELECT reinterpretAsUInt32(toFloat32(0.2)) AS x, reinterpretAsFloat32(x);

Result:

┌──────────x─┬─reinterpretAsFloat32(x)─┐
│ 1045220557 │                     0.2 │
└────────────┴─────────────────────────┘

reinterpretAsFloat64​

Performs byte reinterpretation by treating the input value as a value of type Float64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsFloat64(x)

Parameters

Returned value

  • Reinterpreted value x as Float64. Float64.

Example

Query:

SELECT reinterpretAsUInt64(toFloat64(0.2)) AS x, reinterpretAsFloat64(x);

Result:

┌───────────────────x─┬─reinterpretAsFloat64(x)─┐
│ 4596373779694328218 │                     0.2 │
└─────────────────────┴─────────────────────────┘

reinterpretAsDate​

Accepts a string, fixed string or numeric value and interprets the bytes as a number in host order (little endian). It returns a date from the interpreted number as the number of days since the beginning of the Unix Epoch.

Syntax

reinterpretAsDate(x)

Parameters

Returned value

Implementation details

Note

If the provided string isn't long enough, the function works as if the string is padded with the necessary number of null bytes. If the string is longer than needed, the extra bytes are ignored.

Example

Query:

SELECT reinterpretAsDate(65), reinterpretAsDate('A');

Result:

┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐
│            1970-03-07 │             1970-03-07 │
└───────────────────────┴────────────────────────┘

reinterpretAsDateTime​

These functions accept a string and interpret the bytes placed at the beginning of the string as a number in host order (little endian). Returns a date with time interpreted as the number of seconds since the beginning of the Unix Epoch.

Syntax

reinterpretAsDateTime(x)

Parameters

Returned value

Implementation details

Note

If the provided string isn't long enough, the function works as if the string is padded with the necessary number of null bytes. If the string is longer than needed, the extra bytes are ignored.

Example

Query:

SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A');

Result:

┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐
│       1970-01-01 01:01:05 │        1970-01-01 01:01:05 │
└───────────────────────────┴────────────────────────────┘

reinterpretAsString​

This function accepts a number, date or date with time and returns a string containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a string that is one byte long.

Syntax

reinterpretAsString(x)

Parameters

Returned value

  • String containing bytes representing x. String.

Example

Query:

SELECT
    reinterpretAsString(toDateTime('1970-01-01 01:01:05')),
    reinterpretAsString(toDate('1970-03-07'));

Result:

┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐
│ A                                                      │ A                                         │
└────────────────────────────────────────────────────────┴───────────────────────────────────────────┘

reinterpretAsFixedString​

This function accepts a number, date or date with time and returns a FixedString containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a FixedString that is one byte long.

Syntax

reinterpretAsFixedString(x)

Parameters

Returned value

  • Fixed string containing bytes representing x. FixedString.

Example

Query:

SELECT
    reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')),
    reinterpretAsFixedString(toDate('1970-03-07'));

Result:

┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐
│ A                                                           │ A                                              │
└─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘

reinterpretAsUUID​

Note

In addition to the UUID functions listed here, there is dedicated UUID function documentation.

Accepts a 16 byte string and returns a UUID by interpreting each 8-byte half in little-endian byte order. If the string isn't long enough, the function works as if the string is padded with the necessary number of null bytes to the end. If the string is longer than 16 bytes, the extra bytes at the end are ignored.

Syntax

reinterpretAsUUID(fixed_string)

Arguments

  • fixed_string — Big-endian byte string. FixedString.

Returned value

  • The UUID type value. UUID.

Examples

String to UUID.

Query:

SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')));

Result:

┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│                                  08090a0b-0c0d-0e0f-0001-020304050607 │
└───────────────────────────────────────────────────────────────────────┘

Going back and forth from String to UUID.

Query:

WITH
    generateUUIDv4() AS uuid,
    identity(lower(hex(reverse(reinterpretAsString(uuid))))) AS str,
    reinterpretAsUUID(reverse(unhex(str))) AS uuid2
SELECT uuid = uuid2;

Result:

┌─equals(uuid, uuid2)─┐
│                   1 │
└─────────────────────┘

reinterpret​

Uses the same source in-memory bytes sequence for x value and reinterprets it to destination type.

Syntax

reinterpret(x, type)

Arguments

  • x — Any type.
  • type — Destination type. If it is an array, then the array element type must be a fixed length type.

Returned value

  • Destination type value.

Examples

Query:

SELECT reinterpret(toInt8(-1), 'UInt8') AS int_to_uint,
    reinterpret(toInt8(1), 'Float32') AS int_to_float,
    reinterpret('1', 'UInt32') AS string_to_int;

Result:

┌─int_to_uint─┬─int_to_float─┬─string_to_int─┐
│         255 │        1e-45 │            49 │
└─────────────┴──────────────┴───────────────┘

Query:

SELECT reinterpret(x'3108b4403108d4403108b4403108d440', 'Array(Float32)') AS string_to_array_of_Float32;

Result:

┌─string_to_array_of_Float32─┐
│ [5.626,6.626,5.626,6.626]  │
└────────────────────────────┘

CAST​

Converts an input value to the specified data type. Unlike the reinterpret function, CAST tries to present the same value using the new data type. If the conversion can not be done then an exception is raised. Several syntax variants are supported.

Syntax

CAST(x, T)
CAST(x AS t)
x::t

Arguments

  • x — A value to convert. May be of any type.
  • T — The name of the target data type. String.
  • t — The target data type.

Returned value

  • Converted value.
Note

If the input value does not fit the bounds of the target type, the result overflows. For example, CAST(-1, 'UInt8') returns 255.

Examples

Query:

SELECT
    CAST(toInt8(-1), 'UInt8') AS cast_int_to_uint,
    CAST(1.5 AS Decimal(3,2)) AS cast_float_to_decimal,
    '1'::Int32 AS cast_string_to_int;

Result:

┌─cast_int_to_uint─┬─cast_float_to_decimal─┬─cast_string_to_int─┐
│              255 │                  1.50 │                  1 │
└──────────────────┴───────────────────────┴────────────────────┘

Query:

SELECT
    '2016-06-15 23:00:00' AS timestamp,
    CAST(timestamp AS DateTime) AS datetime,
    CAST(timestamp AS Date) AS date,
    CAST(timestamp, 'String') AS string,
    CAST(timestamp, 'FixedString(22)') AS fixed_string;

Result:

┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────────┘

Conversion to FixedString (N) only works for arguments of type String or FixedString.

Type conversion to Nullable and back is supported.

Example

Query:

SELECT toTypeName(x) FROM t_null;

Result:

┌─toTypeName(x)─┐
│ Int8          │
│ Int8          │
└───────────────┘

Query:

SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null;

Result:

┌─toTypeName(CAST(x, 'Nullable(UInt16)'))─┐
│ Nullable(UInt16)                        │
│ Nullable(UInt16)                        │
└─────────────────────────────────────────┘

See also

accurateCast(x, T)​

Converts x to the T data type.

The difference from cast is that accurateCast does not allow overflow of numeric types during cast if type value x does not fit the bounds of type T. For example, accurateCast(-1, 'UInt8') throws an exception.

Example

Query:

SELECT cast(-1, 'UInt8') AS uint8;

Result:

┌─uint8─┐
│   255 │
└───────┘

Query:

SELECT accurateCast(-1, 'UInt8') AS uint8;

Result:

Code: 70. DB::Exception: Received from localhost:9000. DB::Exception: Value in column Int8 cannot be safely converted into type UInt8: While processing accurateCast(-1, 'UInt8') AS uint8.

accurateCastOrNull(x, T)​

Converts input value x to the specified data type T. Always returns Nullable type and returns NULL if the cast value is not representable in the target type.

Syntax

accurateCastOrNull(x, T)

Arguments

  • x — Input value.
  • T — The name of the returned data type.

Returned value

  • The value, converted to the specified data type T.

Example

Query:

SELECT toTypeName(accurateCastOrNull(5, 'UInt8'));

Result:

┌─toTypeName(accurateCastOrNull(5, 'UInt8'))─┐
│ Nullable(UInt8)                            │
└────────────────────────────────────────────┘

Query:

SELECT
    accurateCastOrNull(-1, 'UInt8') AS uint8,
    accurateCastOrNull(128, 'Int8') AS int8,
    accurateCastOrNull('Test', 'FixedString(2)') AS fixed_string;

Result:

┌─uint8─┬─int8─┬─fixed_string─┐
│  ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ         │
└───────┴──────┴──────────────┘

accurateCastOrDefault(x, T[, default_value])​

Converts input value x to the specified data type T. Returns default type value or default_value if specified if the cast value is not representable in the target type.

Syntax

accurateCastOrDefault(x, T)

Arguments

  • x — Input value.
  • T — The name of the returned data type.
  • default_value — Default value of returned data type.

Returned value

  • The value converted to the specified data type T.

Example

Query:

SELECT toTypeName(accurateCastOrDefault(5, 'UInt8'));

Result:

┌─toTypeName(accurateCastOrDefault(5, 'UInt8'))─┐
│ UInt8                                         │
└───────────────────────────────────────────────┘

Query:

SELECT
    accurateCastOrDefault(-1, 'UInt8') AS uint8,
    accurateCastOrDefault(-1, 'UInt8', 5) AS uint8_default,
    accurateCastOrDefault(128, 'Int8') AS int8,
    accurateCastOrDefault(128, 'Int8', 5) AS int8_default,
    accurateCastOrDefault('Test', 'FixedString(2)') AS fixed_string,
    accurateCastOrDefault('Test', 'FixedString(2)', 'Te') AS fixed_string_default;

Result:

┌─uint8─┬─uint8_default─┬─int8─┬─int8_default─┬─fixed_string─┬─fixed_string_default─┐
│     0 │             5 │    0 │            5 │              │ Te                   │
└───────┴───────────────┴──────┴──────────────┴──────────────┴──────────────────────┘

toInterval​

Creates an Interval data type value from a numeric value and interval unit (eg. 'second' or 'day').

Syntax

toInterval(value, unit)

Arguments

  • value — Length of the interval. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

  • unit — The type of interval to create. String Literal. Possible values:

    • nanosecond
    • microsecond
    • millisecond
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year

    The unit argument is case-insensitive.

Returned value

Example

SELECT toDateTime('2025-01-01 00:00:00') + toInterval(1, 'hour')

┌─toDateTime('2025-01-01 00:00:00') + toInterval(1, 'hour') ─┐
│                                        2025-01-01 01:00:00 │
└────────────────────────────────────────────────────────────┘

toIntervalYear​

Returns an interval of n years of data type IntervalYear.

Syntax

toIntervalYear(n)

Arguments

  • n — Number of years. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalYear(1) AS interval_to_year
SELECT date + interval_to_year AS result

Result:

┌─────result─┐
│ 2025-06-15 │
└────────────┘

toIntervalQuarter​

Returns an interval of n quarters of data type IntervalQuarter.

Syntax

toIntervalQuarter(n)

Arguments

  • n — Number of quarters. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalQuarter(1) AS interval_to_quarter
SELECT date + interval_to_quarter AS result

Result:

┌─────result─┐
│ 2024-09-15 │
└────────────┘

toIntervalMonth​

Returns an interval of n months of data type IntervalMonth.

Syntax

toIntervalMonth(n)

Arguments

  • n — Number of months. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalMonth(1) AS interval_to_month
SELECT date + interval_to_month AS result

Result:

┌─────result─┐
│ 2024-07-15 │
└────────────┘

toIntervalWeek​

Returns an interval of n weeks of data type IntervalWeek.

Syntax

toIntervalWeek(n)

Arguments

  • n — Number of weeks. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalWeek(1) AS interval_to_week
SELECT date + interval_to_week AS result

Result:

┌─────result─┐
│ 2024-06-22 │
└────────────┘

toIntervalDay​

Returns an interval of n days of data type IntervalDay.

Syntax

toIntervalDay(n)

Arguments

  • n — Number of days. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalDay(5) AS interval_to_days
SELECT date + interval_to_days AS result

Result:

┌─────result─┐
│ 2024-06-20 │
└────────────┘

toIntervalHour​

Returns an interval of n hours of data type IntervalHour.

Syntax

toIntervalHour(n)

Arguments

  • n — Number of hours. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalHour(12) AS interval_to_hours
SELECT date + interval_to_hours AS result

Result:

┌──────────────result─┐
│ 2024-06-15 12:00:00 │
└─────────────────────┘

toIntervalMinute​

Returns an interval of n minutes of data type IntervalMinute.

Syntax

toIntervalMinute(n)

Arguments

  • n — Number of minutes. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalMinute(12) AS interval_to_minutes
SELECT date + interval_to_minutes AS result

Result:

┌──────────────result─┐
│ 2024-06-15 00:12:00 │
└─────────────────────┘

toIntervalSecond​

Returns an interval of n seconds of data type IntervalSecond.

Syntax

toIntervalSecond(n)

Arguments

  • n — Number of seconds. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDate('2024-06-15') AS date,
    toIntervalSecond(30) AS interval_to_seconds
SELECT date + interval_to_seconds AS result

Result:

┌──────────────result─┐
│ 2024-06-15 00:00:30 │
└─────────────────────┘

toIntervalMillisecond​

Returns an interval of n milliseconds of data type IntervalMillisecond.

Syntax

toIntervalMillisecond(n)

Arguments

  • n — Number of milliseconds. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDateTime('2024-06-15') AS date,
    toIntervalMillisecond(30) AS interval_to_milliseconds
SELECT date + interval_to_milliseconds AS result

Result:

┌──────────────────result─┐
│ 2024-06-15 00:00:00.030 │
└─────────────────────────┘

toIntervalMicrosecond​

Returns an interval of n microseconds of data type IntervalMicrosecond.

Syntax

toIntervalMicrosecond(n)

Arguments

  • n — Number of microseconds. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDateTime('2024-06-15') AS date,
    toIntervalMicrosecond(30) AS interval_to_microseconds
SELECT date + interval_to_microseconds AS result

Result:

┌─────────────────────result─┐
│ 2024-06-15 00:00:00.000030 │
└────────────────────────────┘

toIntervalNanosecond​

Returns an interval of n nanoseconds of data type IntervalNanosecond.

Syntax

toIntervalNanosecond(n)

Arguments

  • n — Number of nanoseconds. Integer numbers or string representations thereof, and float numbers. (U)Int*/Float*/String.

Returned values

Example

Query:

WITH
    toDateTime('2024-06-15') AS date,
    toIntervalNanosecond(30) AS interval_to_nanoseconds
SELECT date + interval_to_nanoseconds AS result

Result:

┌────────────────────────result─┐
│ 2024-06-15 00:00:00.000000030 │
└───────────────────────────────┘

parseDateTime​

Converts a String to DateTime according to a MySQL format string.

This function is the opposite operation of function formatDateTime.

Syntax

parseDateTime(str[, format[, timezone]])

Arguments

  • str — The String to be parsed
  • format — The format string. Optional. %Y-%m-%d %H:%i:%s if not specified.
  • timezone — Timezone. Optional.

Returned value(s)

Return a DateTime value parsed from the input string according to a MySQL-style format string.

Supported format specifiers

All format specifiers listed in formatDateTime except:

  • %Q: Quarter (1-4)

Example

SELECT parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')

┌─parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│                                       2021-01-04 23:00:00 │
└───────────────────────────────────────────────────────────┘

Alias: TO_TIMESTAMP.

parseDateTimeOrZero​

Same as for parseDateTime except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTimeOrNull​

Same as for parseDateTime except that it returns NULL when it encounters a date format that cannot be processed.

Alias: str_to_date.

parseDateTimeInJodaSyntax​

Similar to parseDateTime, except that the format string is in Joda instead of MySQL syntax.

This function is the opposite operation of function formatDateTimeInJodaSyntax.

Syntax

parseDateTimeInJodaSyntax(str[, format[, timezone]])

Arguments

  • str — The String to be parsed
  • format — The format string. Optional. yyyy-MM-dd HH:mm:ss if not specified.
  • timezone — Timezone. Optional.

Returned value(s)

Return a DateTime value parsed from the input string according to a Joda-style format string.

Supported format specifiers

All format specifiers listed in formatDateTimeInJodaSyntax are supported, except:

  • S: fraction of second
  • z: time zone
  • Z: time zone offset/id

Example

SELECT parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')

┌─parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')─┐
│                                                                     2023-02-24 14:53:31 │
└─────────────────────────────────────────────────────────────────────────────────────────┘

parseDateTimeInJodaSyntaxOrZero​

Same as for parseDateTimeInJodaSyntax except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTimeInJodaSyntaxOrNull​

Same as for parseDateTimeInJodaSyntax except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTime64​

Converts a String to DateTime64 according to a MySQL format string.

Syntax

parseDateTime64(str[, format[, timezone]])

Arguments

  • str — The String to be parsed.
  • format — The format string. Optional. %Y-%m-%d %H:%i:%s.%f if not specified.
  • timezone — Timezone. Optional.

Returned value(s)

Return a DateTime64 value parsed from the input string according to a MySQL-style format string. The precision of the returned value is 6.

parseDateTime64OrZero​

Same as for parseDateTime64 except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTime64OrNull​

Same as for parseDateTime64 except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTime64InJodaSyntax​

Converts a String to DateTime64 according to a Joda format string.

Syntax

parseDateTime64InJodaSyntax(str[, format[, timezone]])

Arguments

  • str — The String to be parsed.
  • format — The format string. Optional. yyyy-MM-dd HH:mm:ss if not specified.
  • timezone — Timezone. Optional.

Returned value(s)

Return a DateTime64 value parsed from the input string according to a Joda-style format string. The precision of the returned value equal to the number of S placeholders in the format string (but at most 6).

parseDateTime64InJodaSyntaxOrZero​

Same as for parseDateTime64InJodaSyntax except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTime64InJodaSyntaxOrNull​

Same as for parseDateTime64InJodaSyntax except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTimeBestEffort​

parseDateTime32BestEffort​

Converts a date and time in the String representation to DateTime data type.

The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse's and some other date and time formats.

Syntax

parseDateTimeBestEffort(time_string [, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String.
  • time_zone — Time zone. The function parses time_string according to the time zone. String.

Supported non-standard formats

  • A string containing 9..10 digit unix timestamp.
  • A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
  • A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
  • A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
  • A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc. For example, 2020-12-12 17:36:00 -5:00.
  • A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. Examples: 24/DEC/18, 24-Dec-18, 01-September-2018. If the year is not specified, it is considered to be equal to the current year. If the resulting DateTime happen to be in the future (even by a second after the current moment), then the current year is substituted by the previous year.

Returned value

  • time_string converted to the DateTime data type.

Examples

Query:

SELECT parseDateTimeBestEffort('23/10/2020 12:12:57')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2020-10-23 12:12:57 │
└─────────────────────────┘

Query:

SELECT parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Asia/Istanbul')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2018-08-18 10:22:16 │
└─────────────────────────┘

Query:

SELECT parseDateTimeBestEffort('1284101485')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2015-07-07 12:04:41 │
└─────────────────────────┘

Query:

SELECT parseDateTimeBestEffort('2018-10-23 10:12:12')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2018-10-23 10:12:12 │
└─────────────────────────┘

Query:

SELECT toYear(now()) AS year, parseDateTimeBestEffort('10 20:19');

Result:

┌─year─┬─parseDateTimeBestEffort('10 20:19')─┐
│ 2023 │                 2023-01-10 20:19:00 │
└──────┴─────────────────────────────────────┘

Query:

WITH
    now() AS ts_now,
    formatDateTime(ts_around, '%b %e %T') AS syslog_arg
SELECT
    ts_now,
    syslog_arg,
    parseDateTimeBestEffort(syslog_arg)
FROM (SELECT arrayJoin([ts_now - 30, ts_now + 30]) AS ts_around);

Result:

┌──────────────ts_now─┬─syslog_arg──────┬─parseDateTimeBestEffort(syslog_arg)─┐
│ 2023-06-30 23:59:30 │ Jun 30 23:59:00 │                 2023-06-30 23:59:00 │
│ 2023-06-30 23:59:30 │ Jul  1 00:00:00 │                 2022-07-01 00:00:00 │
└─────────────────────┴─────────────────┴─────────────────────────────────────┘

See also

parseDateTimeBestEffortUS​

This function behaves like parseDateTimeBestEffort for ISO date formats, e.g. YYYY-MM-DD hh:mm:ss, and other date formats where the month and date components can be unambiguously extracted, e.g. YYYYMMDDhhmmss, YYYY-MM, DD hh, or YYYY-MM-DD hh:mm:ss ±h:mm. If the month and the date components cannot be unambiguously extracted, e.g. MM/DD/YYYY, MM-DD-YYYY, or MM-DD-YY, it prefers the US date format instead of DD/MM/YYYY, DD-MM-YYYY, or DD-MM-YY. As an exception from the latter, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of parseDateTimeBestEffort, e.g. 15/08/2020 is parsed as 2020-08-15.

parseDateTimeBestEffortOrNull​

parseDateTime32BestEffortOrNull​

Same as for parseDateTimeBestEffort except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTimeBestEffortOrZero​

parseDateTime32BestEffortOrZero​

Same as for parseDateTimeBestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.

parseDateTimeBestEffortUSOrNull​

Same as parseDateTimeBestEffortUS function except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTimeBestEffortUSOrZero​

Same as parseDateTimeBestEffortUS function except that it returns zero date (1970-01-01) or zero date with time (1970-01-01 00:00:00) when it encounters a date format that cannot be processed.

parseDateTime64BestEffort​

Same as parseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime data type.

Syntax

parseDateTime64BestEffort(time_string [, precision [, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String.
  • precision — Required precision. 3 — for milliseconds, 6 — for microseconds. Default — 3. Optional. UInt8.
  • time_zone — Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

  • time_string converted to the DateTime data type.

Examples

Query:

SELECT parseDateTime64BestEffort('2021-01-01') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346',6) AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346',3,'Asia/Istanbul') AS a, toTypeName(a) AS t
FORMAT PrettyCompactMonoBlock;

Result:

┌──────────────────────────a─┬─t──────────────────────────────┐
│ 2021-01-01 01:01:00.123000 │ DateTime64(3)                  │
│ 2021-01-01 00:00:00.000000 │ DateTime64(3)                  │
│ 2021-01-01 01:01:00.123460 │ DateTime64(6)                  │
│ 2020-12-31 22:01:00.123000 │ DateTime64(3, 'Asia/Istanbul') │
└────────────────────────────┴────────────────────────────────┘

parseDateTime64BestEffortUS​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity.

parseDateTime64BestEffortOrNull​

Same as for parseDateTime64BestEffort except that it returns NULL when it encounters a date format that cannot be processed.

parseDateTime64BestEffortOrZero​

Same as for parseDateTime64BestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.

parseDateTime64BestEffortUSOrNull​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns NULL when it encounters a date format that cannot be processed.

parseDateTime64BestEffortUSOrZero​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns zero date or zero date time when it encounters a date format that cannot be processed.

toLowCardinality​

Converts input parameter to the LowCardinality version of same data type.

To convert data from the LowCardinality data type use the CAST function. For example, CAST(x as String).

Syntax

toLowCardinality(expr)

Arguments

Returned values

Example

Query:

SELECT toLowCardinality('1');

Result:

┌─toLowCardinality('1')─┐
│ 1                     │
└───────────────────────┘

toUnixTimestamp​

Converts a String, Date, or DateTime to a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) as UInt32.

Syntax

toUnixTimestamp(date, [timezone])

Arguments

Returned value

Returns the Unix timestamp. UInt32

Examples

Usage example

SELECT
'2017-11-05 08:07:47' AS dt_str,
toUnixTimestamp(dt_str) AS from_str,
toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo,
toUnixTimestamp(toDateTime(dt_str)) AS from_datetime,
toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64,
toUnixTimestamp(toDate(dt_str)) AS from_date,
toUnixTimestamp(toDate32(dt_str)) AS from_date32
FORMAT Vertical;

Row 1:
──────
dt_str:          2017-11-05 08:07:47
from_str:        1509869267
from_str_tokyo:  1509836867
from_datetime:   1509869267
from_datetime64: 1509869267
from_date:       1509840000
from_date32:     1509840000

toUnixTimestamp64Second​

Converts a DateTime64 to a Int64 value with fixed second precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Second(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('2009-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Second(dt64);

Result:

┌─toUnixTimestamp64Second(dt64)─┐
│                    1234567891 │
└───────────────────────────────┘

toUnixTimestamp64Milli​

Converts a DateTime64 to a Int64 value with fixed millisecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Milli(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('2009-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Milli(dt64);

Result:

┌─toUnixTimestamp64Milli(dt64)─┐
│                1234567891011 │
└──────────────────────────────┘

toUnixTimestamp64Micro​

Converts a DateTime64 to a Int64 value with fixed microsecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Micro(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('1970-01-15 06:56:07.891011', 6, 'UTC') AS dt64
SELECT toUnixTimestamp64Micro(dt64);

Result:

┌─toUnixTimestamp64Micro(dt64)─┐
│                1234567891011 │
└──────────────────────────────┘

toUnixTimestamp64Nano​

Converts a DateTime64 to a Int64 value with fixed nanosecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Nano(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('1970-01-01 00:20:34.567891011', 9, 'UTC') AS dt64
SELECT toUnixTimestamp64Nano(dt64);

Result:

┌─toUnixTimestamp64Nano(dt64)─┐
│               1234567891011 │
└─────────────────────────────┘

fromUnixTimestamp64Second​

Converts an Int64 to a DateTime64 value with fixed second precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

Note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Second(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 0. DateTime64.

Example

Query:

WITH CAST(1733935988, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Second(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌───────────────────x─┬─toTypeName(x)────────┐
│ 2024-12-11 16:53:08 │ DateTime64(0, 'UTC') │
└─────────────────────┴──────────────────────┘

fromUnixTimestamp64Milli​

Converts an Int64 to a DateTime64 value with fixed millisecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

Note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Milli(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 3. DateTime64.

Example

Query:

WITH CAST(1733935988123, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Milli(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌───────────────────────x─┬─toTypeName(x)────────┐
│ 2024-12-11 16:53:08.123 │ DateTime64(3, 'UTC') │
└─────────────────────────┴──────────────────────┘

fromUnixTimestamp64Micro​

Converts an Int64 to a DateTime64 value with fixed microsecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

Note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Micro(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 6. DateTime64.

Example

Query:

WITH CAST(1733935988123456, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Micro(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌──────────────────────────x─┬─toTypeName(x)────────┐
│ 2024-12-11 16:53:08.123456 │ DateTime64(6, 'UTC') │
└────────────────────────────┴──────────────────────┘

fromUnixTimestamp64Nano​

Converts an Int64 to a DateTime64 value with fixed nanosecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

Note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Nano(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 9. DateTime64.

Example

Query:

WITH CAST(1733935988123456789, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Nano(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌─────────────────────────────x─┬─toTypeName(x)────────┐
│ 2024-12-11 16:53:08.123456789 │ DateTime64(9, 'UTC') │
└───────────────────────────────┴──────────────────────┘

formatRow​

Converts arbitrary expressions into a string via given format.

Syntax

formatRow(format, x, y, ...)

Arguments

Returned value

  • A formatted string. (for text formats it's usually terminated with the new line character).

Example

Query:

SELECT formatRow('CSV', number, 'good')
FROM numbers(3);

Result:

┌─formatRow('CSV', number, 'good')─┐
│ 0,"good"
                         │
│ 1,"good"
                         │
│ 2,"good"
                         │
└──────────────────────────────────┘

Note: If format contains suffix/prefix, it will be written in each row.

Example

Query:

SELECT formatRow('CustomSeparated', number, 'good')
FROM numbers(3)
SETTINGS format_custom_result_before_delimiter='<prefix>\n', format_custom_result_after_delimiter='<suffix>'

Result:

┌─formatRow('CustomSeparated', number, 'good')─┐
│ <prefix>
0    good
<suffix>                   │
│ <prefix>
1    good
<suffix>                   │
│ <prefix>
2    good
<suffix>                   │
└──────────────────────────────────────────────┘

Note: Only row-based formats are supported in this function.

formatRowNoNewline​

Converts arbitrary expressions into a string via given format. Differs from formatRow in that this function trims the last \n if any.

Syntax

formatRowNoNewline(format, x, y, ...)

Arguments

Returned value

  • A formatted string.

Example

Query:

SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3);

Result:

┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good"                                  │
│ 1,"good"                                  │
│ 2,"good"                                  │
└───────────────────────────────────────────┘

CAST​

Introduced in: v1.1

Converts a value to a specified data type. Unlike the reinterpret function, CAST tries to generate the same value in the target type. If that is not possible, an exception is raised.

Syntax

CAST(x, T)
or CAST(x AS T)
or x::T

Arguments

  • x — A value of any type. Any
  • T — The target data type. String

Returned value

Returns the converted value with the target data type. Any

Examples

Basic usage

SELECT CAST(42, 'String')

┌─CAST(42, 'String')─┐
│ 42                 │
└────────────────────┘

Using AS syntax

SELECT CAST('2025-01-01' AS Date)

┌─CAST('2025-01-01', 'Date')─┐
│                 2025-01-01 │
└────────────────────────────┘

Using :: syntax

SELECT '123'::UInt32

┌─CAST('123', 'UInt32')─┐
│                   123 │
└───────────────────────┘

accurateCast​

Introduced in: v1.1

Converts a value to a specified data type. Unlike CAST, accurateCast performs stricter type checking and throws an exception if the conversion would result in a loss of data precision or if the conversion is not possible.

This function is safer than regular CAST as it prevents precision loss and invalid conversions.

Syntax

accurateCast(x, T)

Arguments

  • x — A value to convert. Any
  • T — The target data type name. String

Returned value

Returns the converted value with the target data type. Any

Examples

Successful conversion

SELECT accurateCast(42, 'UInt16')

┌─accurateCast(42, 'UInt16')─┐
│                        42 │
└───────────────────────────┘

String to number

SELECT accurateCast('123.45', 'Float64')

┌─accurateCast('123.45', 'Float64')─┐
│                            123.45 │
└───────────────────────────────────┘

accurateCastOrDefault​

Introduced in: v21.1

Converts a value to a specified data type. Like accurateCast, but returns a default value instead of throwing an exception if the conversion cannot be performed accurately.

If a default value is provided as the second argument, it must be of the target type. If no default value is provided, the default value of the target type is used.

Syntax

accurateCastOrDefault(x, T[, default_value])

Arguments

  • x — A value to convert. Any
  • T — The target data type name. const String
  • default_value — Optional. Default value to return if conversion fails. Any

Returned value

Returns the converted value with the target data type, or the default value if conversion is not possible. Any

Examples

Successful conversion

SELECT accurateCastOrDefault(42, 'String')

┌─accurateCastOrDefault(42, 'String')─┐
│ 42                                  │
└─────────────────────────────────────┘

Failed conversion with explicit default

SELECT accurateCastOrDefault('abc', 'UInt32', 999::UInt32)

┌─accurateCastOrDefault('abc', 'UInt32', 999)─┐
│                                         999 │
└─────────────────────────────────────────────┘

Failed conversion with implicit default

SELECT accurateCastOrDefault('abc', 'UInt32')

┌─accurateCastOrDefault('abc', 'UInt32')─┐
│                                      0 │
└────────────────────────────────────────┘

accurateCastOrNull​

Introduced in: v1.1

Converts a value to a specified data type. Like accurateCast, but returns NULL instead of throwing an exception if the conversion cannot be performed accurately.

This function combines the safety of accurateCast with graceful error handling.

Syntax

accurateCastOrNull(x, T)

Arguments

  • x — A value to convert. Any
  • T — The target data type name. String

Returned value

Returns the converted value with the target data type, or NULL if conversion is not possible. Any

Examples

Successful conversion

SELECT accurateCastOrNull(42, 'String')

┌─accurateCastOrNull(42, 'String')─┐
│ 42                               │
└──────────────────────────────────┘

Failed conversion returns NULL

SELECT accurateCastOrNull('abc', 'UInt32')

┌─accurateCastOrNull('abc', 'UInt32')─┐
│                                ᴺᵁᴸᴸ │
└─────────────────────────────────────┘

formatRow​

Introduced in: v20.7

Converts arbitrary expressions into a string via given format.

Note

If the format contains a suffix/prefix, it will be written in each row. Only row-based formats are supported in this function.

Syntax

formatRow(format, x, y, ...)

Arguments

  • format — Text format. For example, CSV, TSV. String
  • x, y, ... — Expressions. Any

Returned value

A formatted string. (for text formats it's usually terminated with the new line character). String

Examples

Basic usage

SELECT formatRow('CSV', number, 'good')
FROM numbers(3)

┌─formatRow('CSV', number, 'good')─┐
│ 0,"good"
                         │
│ 1,"good"
                         │
│ 2,"good"
                         │
└──────────────────────────────────┘

With custom format

SELECT formatRow('CustomSeparated', number, 'good')
FROM numbers(3)
SETTINGS format_custom_result_before_delimiter='<prefix>\n', format_custom_result_after_delimiter='<suffix>'

┌─formatRow('CustomSeparated', number, 'good')─┐
│ <prefix>
0    good
<suffix>                   │
│ <prefix>
1    good
<suffix>                   │
│ <prefix>
2    good
<suffix>                   │
└──────────────────────────────────────────────┘

formatRowNoNewline​

Introduced in: v20.7

Same as formatRow, but trims the newline character of each row.

Converts arbitrary expressions into a string via given format, but removes any trailing newline characters from the result.

Syntax

formatRowNoNewline(format, x, y, ...)

Arguments

  • format — Text format. For example, CSV, TSV. String
  • x, y, ... — Expressions. Any

Returned value

Returns a formatted string with newlines removed. String

Examples

Basic usage

SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3)

┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good"                                  │
│ 1,"good"                                  │
│ 2,"good"                                  │
└───────────────────────────────────────────┘

fromUnixTimestamp64Micro​

Introduced in: v20.5

Converts a Unix timestamp in microseconds to a DateTime64 value with microsecond precision.

The input value is treated as a Unix timestamp with microsecond precision (number of microseconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Micro(value[, timezone])

Arguments

  • value — Unix timestamp in microseconds. Int64
  • timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with microsecond precision. DateTime64(6)

Examples

Usage example

SELECT fromUnixTimestamp64Micro(1640995200123456)

┌─fromUnixTimestamp64Micro(1640995200123456)─┐
│                 2022-01-01 00:00:00.123456 │
└────────────────────────────────────────────┘

fromUnixTimestamp64Milli​

Introduced in: v20.5

Converts a Unix timestamp in milliseconds to a DateTime64 value with millisecond precision.

The input value is treated as a Unix timestamp with millisecond precision (number of milliseconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Milli(value[, timezone])

Arguments

  • value — Unix timestamp in milliseconds. Int64
  • timezone — Optional. Timezone for the returned value. String

Returned value

A DateTime64 value with millisecond precision. DateTime64(3)

Examples

Usage example

SELECT fromUnixTimestamp64Milli(1640995200123)

┌─fromUnixTimestamp64Milli(1640995200123)─┐
│                 2022-01-01 00:00:00.123 │
└─────────────────────────────────────────┘

fromUnixTimestamp64Nano​

Introduced in: v20.5

Converts a Unix timestamp in nanoseconds to a DateTime64 value with nanosecond precision.

The input value is treated as a Unix timestamp with nanosecond precision (number of nanoseconds since 1970-01-01 00:00:00 UTC).

Note

Please note that the input value is treated as a UTC timestamp, not the timezone of the input value.

Syntax

fromUnixTimestamp64Nano(value[, timezone])

Arguments

  • value — Unix timestamp in nanoseconds. Int64
  • timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with nanosecond precision. DateTime64(9)

Examples

Usage example

SELECT fromUnixTimestamp64Nano(1640995200123456789)

┌─fromUnixTimestamp64Nano(1640995200123456789)─┐
│                2022-01-01 00:00:00.123456789 │
└──────────────────────────────────────────────┘

fromUnixTimestamp64Second​

Introduced in: v24.12

Converts a Unix timestamp in seconds to a DateTime64 value with second precision.

The input value is treated as a Unix timestamp with second precision (number of seconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Second(value[, timezone])

Arguments

  • value — Unix timestamp in seconds. Int64
  • timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with second precision. DateTime64(0)

Examples

Usage example

SELECT fromUnixTimestamp64Second(1640995200)

┌─fromUnixTimestamp64Second(1640995200)─┐
│                   2022-01-01 00:00:00 │
└───────────────────────────────────────┘

parseDateTime​

Introduced in: v23.3

Parses a date and time string according to a MySQL date format string.

This function is the inverse of formatDateTime. It parses a String argument using a format String. Returns a DateTime type.

Syntax

parseDateTime(time_string, format[, timezone])

Aliases: TO_UNIXTIME

Arguments

  • time_string — String to be parsed into DateTime. String
  • format — Format string specifying how to parse time_string. String
  • timezone — Optional. Timezone. String

Returned value

Returns a DateTime parsed from the input string according to the MySQL style format string. DateTime

Examples

Usage example

SELECT parseDateTime('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')

┌─parseDateTime('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│                                       2025-01-04 23:00:00 │
└───────────────────────────────────────────────────────────┘

parseDateTime32BestEffort​

Introduced in: v20.9

Converts a string representation of a date and time to the DateTime data type.

The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse's and some other date and time formats.

Syntax

parseDateTime32BestEffort(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed String

Returned value

Returns time_string as a DateTime. DateTime

Examples

Usage example

SELECT parseDateTime32BestEffort('23/10/2025 12:12:57')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2025-10-23 12:12:57 │
└───────────────────────────┘

With timezone

SELECT parseDateTime32BestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2025-08-18 10:22:16 │
└───────────────────────────┘

Unix timestamp

SELECT parseDateTime32BestEffort('1284101485')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2015-07-07 12:04:41 │
└───────────────────────────┘

parseDateTime32BestEffortOrNull​

Introduced in: v20.9

Same as parseDateTime32BestEffort except that it returns NULL when it encounters a date format that cannot be processed.

Syntax

parseDateTime32BestEffortOrNull(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns a DateTime object parsed from the string, or NULL if the parsing fails. DateTime

Examples

Usage example

SELECT
    parseDateTime32BestEffortOrNull('23/10/2025 12:12:57') AS valid,
    parseDateTime32BestEffortOrNull('invalid date') AS invalid

┌─valid───────────────┬─invalid─┐
│ 2025-10-23 12:12:57 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTime32BestEffortOrZero​

Introduced in: v20.9

Same as parseDateTime32BestEffort except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime32BestEffortOrZero(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns a DateTime object parsed from the string, or zero date (1970-01-01 00:00:00) if the parsing fails. DateTime

Examples

Usage example

SELECT
    parseDateTime32BestEffortOrZero('23/10/2025 12:12:57') AS valid,
    parseDateTime32BestEffortOrZero('invalid date') AS invalid

┌─valid───────────────┬─invalid─────────────┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTime64BestEffort​

Introduced in: v20.1

Same as parseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime64 data type.

Syntax

parseDateTime64BestEffort(time_string[, precision[, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to the DateTime64 data type. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffort('2025-01-01') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',6) AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',3,'Asia/Istanbul') AS a, toTypeName(a) AS t
FORMAT PrettyCompactMonoBlock

┌──────────────────────────a─┬─t──────────────────────────────┐
│ 2025-01-01 01:01:00.123000 │ DateTime64(3)                  │
│ 2025-01-01 00:00:00.000000 │ DateTime64(3)                  │
│ 2025-01-01 01:01:00.123460 │ DateTime64(6)                  │
│ 2025-12-31 22:01:00.123000 │ DateTime64(3, 'Asia/Istanbul') │
└────────────────────────────┴────────────────────────────────┘

parseDateTime64BestEffortOrNull​

Introduced in: v20.1

Same as parseDateTime64BestEffort except that it returns NULL when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortOrNull(time_string[, precision[, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64, or NULL if the input cannot be parsed. DateTime64 or NULL

Examples

Usage example

SELECT parseDateTime64BestEffortOrNull('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrNull('invalid') AS invalid

┌─valid───────────────────┬─invalid─┐
│ 2025-01-01 01:01:00.123 │    ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘

parseDateTime64BestEffortOrZero​

Introduced in: v20.1

Same as parseDateTime64BestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortOrZero(time_string[, precision[, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64, or zero date/datetime (1970-01-01 00:00:00.000) if the input cannot be parsed. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortOrZero('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrZero('invalid') AS invalid

┌─valid───────────────────┬─invalid─────────────────┐
│ 2025-01-01 01:01:00.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

parseDateTime64BestEffortUS​

Introduced in: v22.8

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity.

Syntax

parseDateTime64BestEffortUS(time_string [, precision [, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US date format preference for ambiguous cases. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortUS('02/10/2025 12:30:45.123') AS us_format,
       parseDateTime64BestEffortUS('15/08/2025 10:15:30.456') AS fallback_to_standard

┌─us_format───────────────┬─fallback_to_standard────┐
│ 2025-02-10 12:30:45.123 │ 2025-08-15 10:15:30.456 │
└─────────────────────────┴─────────────────────────┘

parseDateTime64BestEffortUSOrNull​

Introduced in: v22.8

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns NULL when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortUSOrNull(time_string[, precision[, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US format preference, or NULL if the input cannot be parsed. DateTime64 or NULL

Examples

Usage example

SELECT parseDateTime64BestEffortUSOrNull('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrNull('invalid') AS invalid

┌─valid_us────────────────┬─invalid─┐
│ 2025-02-10 12:30:45.123 │    ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘

parseDateTime64BestEffortUSOrZero​

Introduced in: v22.8

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns zero date or zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortUSOrZero(time_string [, precision [, time_zone]])

Arguments

  • time_string — String containing a date or date with time to convert. String
  • precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US format preference, or zero date/datetime (1970-01-01 00:00:00.000) if the input cannot be parsed. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortUSOrZero('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrZero('invalid') AS invalid

┌─valid_us────────────────┬─invalid─────────────────┐
│ 2025-02-10 12:30:45.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

parseDateTimeBestEffort​

Introduced in: v1.1

Converts a date and time in the String representation to DateTime data type. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse's and some other date and time formats.

Supported non-standard formats:

  • A string containing 9..10 digit unix timestamp.
  • A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
  • A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
  • A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
  • A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
  • A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffort(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffort('23/10/2025 12:12:57') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-10-23 12:12:57 │
└─────────────────────────┘

With timezone

SELECT parseDateTimeBestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-08-18 10:22:16 │
└─────────────────────────┘

Unix timestamp

SELECT parseDateTimeBestEffort('1735689600') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-01-01 00:00:00 │
└─────────────────────────┘

parseDateTimeBestEffortOrNull​

Introduced in: v1.1

The same as parseDateTimeBestEffort except that it returns NULL when it encounters a date format that cannot be processed. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse's and some other date and time formats.

Supported non-standard formats:

  • A string containing 9..10 digit unix timestamp.
  • A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
  • A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
  • A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
  • A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
  • A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffortOrNull(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime, or NULL if the input cannot be parsed. DateTime or NULL

Examples

Usage example

SELECT parseDateTimeBestEffortOrNull('23/10/2025 12:12:57') AS valid,
       parseDateTimeBestEffortOrNull('invalid') AS invalid

┌─valid───────────────┬─invalid─┐
│ 2025-10-23 12:12:57 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTimeBestEffortOrZero​

Introduced in: v1.1

Same as parseDateTimeBestEffort except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse's and some other date and time formats.

Supported non-standard formats:

  • A string containing 9..10 digit unix timestamp.
  • A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
  • A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
  • A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
  • A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
  • A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffortOrZero(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime, or zero date/datetime (1970-01-01 or 1970-01-01 00:00:00) if the input cannot be parsed. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortOrZero('23/10/2025 12:12:57') AS valid,
       parseDateTimeBestEffortOrZero('invalid') AS invalid

┌─valid───────────────┬─invalid─────────────┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTimeBestEffortUS​

Introduced in: v1.1

This function behaves like parseDateTimeBestEffort for ISO date formats, e.g. YYYY-MM-DD hh:mm:ss, and other date formats where the month and date components can be unambiguously extracted, e.g. YYYYMMDDhhmmss, YYYY-MM, DD hh, or YYYY-MM-DD hh:mm:ss ±h:mm. If the month and the date components cannot be unambiguously extracted, e.g. MM/DD/YYYY, MM-DD-YYYY, or MM-DD-YY, it prefers the US date format instead of DD/MM/YYYY, DD-MM-YYYY, or DD-MM-YY. As an exception to the previous statement, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of parseDateTimeBestEffort, e.g. 15/08/2020 is parsed as 2020-08-15.

Syntax

parseDateTimeBestEffortUS(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US date format preference for ambiguous cases. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortUS('02/10/2025') AS us_format,
       parseDateTimeBestEffortUS('15/08/2025') AS fallback_to_standard

┌─us_format───────────┬─fallback_to_standard─┐
│ 2025-02-10 00:00:00 │  2025-08-15 00:00:00 │
└─────────────────────┴──────────────────────┘

parseDateTimeBestEffortUSOrNull​

Introduced in: v1.1

Same as parseDateTimeBestEffortUS function except that it returns NULL when it encounters a date format that cannot be processed.

This function behaves like parseDateTimeBestEffort for ISO date formats, but prefers the US date format for ambiguous cases, with NULL return on parsing errors.

Syntax

parseDateTimeBestEffortUSOrNull(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US format preference, or NULL if the input cannot be parsed. DateTime or NULL

Examples

Usage example

SELECT parseDateTimeBestEffortUSOrNull('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrNull('invalid') AS invalid

┌─valid_us────────────┬─invalid─┐
│ 2025-02-10 00:00:00 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTimeBestEffortUSOrZero​

Introduced in: v1.1

Same as parseDateTimeBestEffortUS function except that it returns zero date (1970-01-01) or zero date with time (1970-01-01 00:00:00) when it encounters a date format that cannot be processed.

This function behaves like parseDateTimeBestEffort for ISO date formats, but prefers the US date format for ambiguous cases, with zero return on parsing errors.

Syntax

parseDateTimeBestEffortUSOrZero(time_string[, time_zone])

Arguments

  • time_string — String containing a date and time to convert. String
  • time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US format preference, or zero date/datetime (1970-01-01 or 1970-01-01 00:00:00) if the input cannot be parsed. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortUSOrZero('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrZero('invalid') AS invalid

┌─valid_us────────────┬─invalid─────────────┐
│ 2025-02-10 00:00:00 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTimeOrNull​

Introduced in: v23.3

Same as parseDateTime but returns NULL when it encounters an unparsable date format.

Syntax

parseDateTimeOrNull(time_string, format[, timezone])

Aliases: str_to_date

Arguments

  • time_string — String to be parsed into DateTime. String
  • format — Format string specifying how to parse time_string. String
  • timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or NULL if parsing fails. Nullable(DateTime)

Examples

Usage example

SELECT parseDateTimeOrNull('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')

┌─parseDateTimeOrNull('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│                                            2025-01-04 23:00:00  │
└─────────────────────────────────────────────────────────────────┘

parseDateTimeOrZero​

Introduced in: v23.3

Same as parseDateTime but returns zero date when it encounters an unparsable date format.

Syntax

parseDateTimeOrZero(time_string, format[, timezone])

Arguments

  • time_string — String to be parsed into DateTime. String
  • format — Format string specifying how to parse time_string. String
  • timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or zero DateTime if parsing fails. DateTime

Examples

Usage example

SELECT parseDateTimeOrZero('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')

┌─parseDateTimeOrZero('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│                                             2025-01-04 23:00:00 │
└─────────────────────────────────────────────────────────────────┘

reinterpret​

Introduced in: v1.1

Uses the same source in-memory bytes sequence for the provided value x and reinterprets it to the destination type.

Syntax

reinterpret(x, type)

Arguments

  • x — Any type. Any
  • type — Destination type. If it is an array, then the array element type must be a fixed length type. String

Returned value

Destination type value. Any

Examples

Usage example

SELECT reinterpret(toInt8(-1), 'UInt8') AS int_to_uint,
    reinterpret(toInt8(1), 'Float32') AS int_to_float,
    reinterpret('1', 'UInt32') AS string_to_int

┌─int_to_uint─┬─int_to_float─┬─string_to_int─┐
│         255 │        1e-45 │            49 │
└─────────────┴──────────────┴───────────────┘

Array example

SELECT reinterpret(x'3108b4403108d4403108b4403108d440', 'Array(Float32)') AS string_to_array_of_Float32

┌─string_to_array_of_Float32─┐
│ [5.626,6.626,5.626,6.626]  │
└────────────────────────────┘

reinterpretAsDate​

Introduced in: v1.1

Reinterprets the input value as a Date value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01

Syntax

reinterpretAsDate(x)

Arguments

Returned value

Date. Date

Examples

Usage example

SELECT reinterpretAsDate(65), reinterpretAsDate('A')

┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐
│            1970-03-07 │             1970-03-07 │
└───────────────────────┴────────────────────────┘

reinterpretAsDateTime​

Introduced in: v1.1

Reinterprets the input value as a DateTime value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01

Syntax

reinterpretAsDateTime(x)

Arguments

Returned value

Date and Time. DateTime

Examples

Usage example

SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A')

┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐
│       1970-01-01 01:01:05 │        1970-01-01 01:01:05 │
└───────────────────────────┴────────────────────────────┘

reinterpretAsFixedString​

Introduced in: v1.1

Reinterprets the input value as a fixed string (assuming little endian order). Null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character.

Syntax

reinterpretAsFixedString(x)

Arguments

Returned value

Fixed string containing bytes representing x. FixedString

Examples

Usage example

SELECT
    reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')),
    reinterpretAsFixedString(toDate('1970-03-07'))

┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐
│ A                                                           │ A                                              │
└─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘

reinterpretAsFloat32​

Introduced in: v1.1

Reinterprets the input value as a value of type Float32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsFloat32(x)

Arguments

Returned value

Returns the reinterpreted value x. Float32

Examples

Usage example

SELECT reinterpretAsUInt32(toFloat32(0.2)) AS x, reinterpretAsFloat32(x)

┌──────────x─┬─reinterpretAsFloat32(x)─┐
│ 1045220557 │                     0.2 │
└────────────┴─────────────────────────┘

reinterpretAsFloat64​

Introduced in: v1.1

Reinterprets the input value as a value of type Float64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsFloat64(x)

Arguments

Returned value

Returns the reinterpreted value x. Float64

Examples

Usage example

SELECT reinterpretAsUInt64(toFloat64(0.2)) AS x, reinterpretAsFloat64(x)

┌───────────────────x─┬─reinterpretAsFloat64(x)─┐
│ 4596373779694328218 │                     0.2 │
└─────────────────────┴─────────────────────────┘

reinterpretAsInt128​

Introduced in: v1.1

Reinterprets the input value as a value of type Int128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt128(x)

Arguments

Returned value

Returns the reinterpreted value x. Int128

Examples

Usage example

SELECT
    toInt64(257) AS x,
    toTypeName(x),
    reinterpretAsInt128(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64         │ 257 │ Int128          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt16​

Introduced in: v1.1

Reinterprets the input value as a value of type Int16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt16(x)

Arguments

Returned value

Returns the reinterpreted value x. Int16

Examples

Usage example

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt16(x) AS res,
    toTypeName(res)

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ Int16           │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsInt256​

Introduced in: v1.1

Reinterprets the input value as a value of type Int256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt256(x)

Arguments

Returned value

Returns the reinterpreted value x. Int256

Examples

Usage example

SELECT
    toInt128(257) AS x,
    toTypeName(x),
    reinterpretAsInt256(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int128        │ 257 │ Int256          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt32​

Introduced in: v1.1

Reinterprets the input value as a value of type Int32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt32(x)

Arguments

Returned value

Returns the reinterpreted value x. Int32

Examples

Usage example

SELECT
    toInt16(257) AS x,
    toTypeName(x),
    reinterpretAsInt32(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16         │ 257 │ Int32           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt64​

Introduced in: v1.1

Reinterprets the input value as a value of type Int64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt64(x)

Arguments

Returned value

Returns the reinterpreted value x. Int64

Examples

Usage example

SELECT
    toInt32(257) AS x,
    toTypeName(x),
    reinterpretAsInt64(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32         │ 257 │ Int64           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt8​

Introduced in: v1.1

Reinterprets the input value as a value of type Int8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt8(x)

Arguments

Returned value

Returns the reinterpreted value x. Int8

Examples

Usage example

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt8(x) AS res,
    toTypeName(res)

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ Int8            │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsString​

Introduced in: v1.1

Reinterprets the input value as a string (assuming little endian order). Null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character.

Syntax

reinterpretAsString(x)

Arguments

Returned value

String containing bytes representing x. String

Examples

Usage example

SELECT
    reinterpretAsString(toDateTime('1970-01-01 01:01:05')),
    reinterpretAsString(toDate('1970-03-07'))

┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐
│ A                                                      │ A                                         │
└────────────────────────────────────────────────────────┴───────────────────────────────────────────┘

reinterpretAsUInt128​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt128(x)

Arguments

Returned value

Returns the reinterpreted value x. UInt128

Examples

Usage example

SELECT
    toUInt64(257) AS x,
    toTypeName(x),
    reinterpretAsUInt128(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64        │ 257 │ UInt128         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt16​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt16(x)

Arguments

Returned value

Returns the reinterpreted value x. UInt16

Examples

Usage example

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt16(x) AS res,
    toTypeName(res)

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ UInt16          │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt256​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt256(x)

Arguments

Returned value

Returns the reinterpreted value x. UInt256

Examples

Usage example

SELECT
    toUInt128(257) AS x,
    toTypeName(x),
    reinterpretAsUInt256(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128       │ 257 │ UInt256         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt32​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt32(x)

Arguments

Returned value

Returns the reinterpreted value x. UInt32

Examples

Usage example

SELECT
    toUInt16(257) AS x,
    toTypeName(x),
    reinterpretAsUInt32(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16        │ 257 │ UInt32          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt64​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt64(x)

Arguments

Returned value

Returns the reinterpreted value of x. UInt64

Examples

Usage example

SELECT
    toUInt32(257) AS x,
    toTypeName(x),
    reinterpretAsUInt64(x) AS res,
    toTypeName(res)

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32        │ 257 │ UInt64          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt8​

Introduced in: v1.1

Reinterprets the input value as a value of type UInt8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt8(x)

Arguments

Returned value

Returns the reinterpreted value x. UInt8

Examples

Usage example

SELECT
    toInt8(-1) AS val,
    toTypeName(val),
    reinterpretAsUInt8(val) AS res,
    toTypeName(res);

┌─val─┬─toTypeName(val)─┬─res─┬─toTypeName(res)─┐
│  -1 │ Int8            │ 255 │ UInt8           │
└─────┴─────────────────┴─────┴─────────────────┘

reinterpretAsUUID​

Introduced in: v1.1

Accepts a 16 byte string and returns a UUID by interpreting each 8-byte half in little-endian byte order. If the string isn't long enough, the function works as if the string is padded with the necessary number of null bytes to the end. If the string is longer than 16 bytes, the extra bytes at the end are ignored.

Syntax

reinterpretAsUUID(fixed_string)

Arguments

Returned value

The UUID type value. UUID

Examples

String to UUID

SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))

┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│                                  08090a0b-0c0d-0e0f-0001-020304050607 │
└───────────────────────────────────────────────────────────────────────┘

toBFloat16​

Introduced in: v1.1

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

See also:

Syntax

toBFloat16(expr)

Arguments

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

Returned value

Returns a 16-bit brain-float value. BFloat16

Examples

Usage example

SELECT
toBFloat16(toFloat32(42.7)),
toBFloat16(toFloat32('42.7')),
toBFloat16('42.7')
FORMAT Vertical;

toBFloat16(toFloat32(42.7)): 42.5
toBFloat16(t⋯32('42.7')):    42.5
toBFloat16('42.7'):          42.5

toBFloat16OrNull​

Introduced in: v1.1

Converts a String input value to a value of type BFloat16. If the string does not represent a floating point value, the function returns NULL.

Supported arguments:

  • String representations of numeric values.

Unsupported arguments (return NULL):

  • String representations of binary and hexadecimal values.
  • Numeric values.
Note

The function allows a silent loss of precision while converting from the string representation.

See also:

Syntax

toBFloat16OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Reurns a 16-bit brain-float value, otherwise NULL. BFloat16 or NULL

Examples

Usage example

SELECT toBFloat16OrNull('0x5E'), -- unsupported arguments
       toBFloat16OrNull('12.3'), -- typical use
       toBFloat16OrNull('12.3456789') -- silent loss of precision

\N
12.25
12.3125

toBFloat16OrZero​

Introduced in: v1.1

Converts a String input value to a value of type BFloat16. If the string does not represent a floating point value, the function returns zero.

Supported arguments:

  • String representations of numeric values.

Unsupported arguments (return 0):

  • String representations of binary and hexadecimal values.
  • Numeric values.
Note

The function allows a silent loss of precision while converting from the string representation.

See also:

Syntax

toBFloat16OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a 16-bit brain-float value, otherwise 0. BFloat16

Examples

Usage example

SELECT toBFloat16OrZero('0x5E'), -- unsupported arguments
       toBFloat16OrZero('12.3'), -- typical use
       toBFloat16OrZero('12.3456789') -- silent loss of precision

0
12.25
12.3125

toBool​

Introduced in: v22.2

Converts an input value to a value of type Bool.

Syntax

toBool(expr)

Arguments

  • expr — Expression returning a number or a string. For strings, accepts 'true' or 'false' (case-insensitive). (U)Int* or Float* or String or Expression

Returned value

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

Examples

Usage example

SELECT
    toBool(toUInt8(1)),
    toBool(toInt8(-1)),
    toBool(toFloat32(1.01)),
    toBool('true'),
    toBool('false'),
    toBool('FALSE')
FORMAT Vertical

toBool(toUInt8(1)):      true
toBool(toInt8(-1)):      true
toBool(toFloat32(1.01)): true
toBool('true'):          true
toBool('false'):         false
toBool('FALSE'):         false

toDate​

Introduced in: v1.1

Converts an input value to type Date. Supports conversion from String, FixedString, DateTime, or numeric types.

Syntax

toDate(x)

Arguments

Returned value

Returns the converted input value. Date

Examples

String to Date conversion

SELECT toDate('2025-04-15')

2025-04-15

DateTime to Date conversion

SELECT toDate(toDateTime('2025-04-15 10:30:00'))

2025-04-15

Integer to Date conversion

SELECT toDate(20297)

2025-07-28

toDate32​

Introduced in: v21.9

Converts the argument to the Date32 data type. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument is of type Date, it's bounds are taken into account.

Syntax

toDate32(expr)

Arguments

Returned value

Returns a calendar date. Date32

Examples

Within range

SELECT toDate32('2025-01-01') AS value, toTypeName(value)
FORMAT Vertical

Row 1:
──────
value:           2025-01-01
toTypeName(value): Date32

Outside range

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

Row 1:
──────
value:           1900-01-01
toTypeName(value): Date32

toDate32OrDefault​

Introduced in: v21.11

Converts the argument to the Date32 data type. If the value is outside the range, toDate32OrDefault returns the lower border value supported by Date32. If the argument has Date type, it's borders are taken into account. Returns default value if an invalid argument is received.

Syntax

toDate32OrDefault(expr[, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Date32

Returned value

Value of type Date32 if successful, otherwise returns the default value if passed or 1900-01-01 if not. Date32

Examples

Successful conversion

SELECT toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))

1930-01-01

Failed conversion

SELECT toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))

2020-01-01

toDate32OrNull​

Introduced in: v21.9

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

Syntax

toDate32OrNull(x)

Arguments

  • x — A string representation of a date. String

Returned value

Returns a Date32 value if successful, otherwise NULL. Date32 or NULL

Examples

Usage example

SELECT toDate32OrNull('2025-01-01'), toDate32OrNull('invalid')

┌─toDate32OrNull('2025-01-01')─┬─toDate32OrNull('invalid')─┐
│                   2025-01-01 │                      ᴺᵁᴸᴸ │
└──────────────────────────────┴───────────────────────────┘

toDate32OrZero​

Introduced in: v21.9

Converts an input value to a value of type Date32 but returns the lower boundary of Date32 if an invalid argument is received. The same as toDate32 but returns lower boundary of Date32 if an invalid argument is received.

See also:

Syntax

toDate32OrZero(x)

Arguments

  • x — A string representation of a date. String

Returned value

Returns a Date32 value if successful, otherwise the lower boundary of Date32 (1900-01-01). Date32

Examples

Usage example

SELECT toDate32OrZero('2025-01-01'), toDate32OrZero('')

┌─toDate32OrZero('2025-01-01')─┬─toDate32OrZero('')─┐
│                   2025-01-01 │         1900-01-01 │
└──────────────────────────────┴────────────────────┘

toDateOrDefault​

Introduced in: v21.11

Like toDate but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of Date.

Syntax

toDateOrDefault(expr[, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Date

Returned value

Value of type Date if successful, otherwise returns the default value if passed or 1970-01-01 if not. Date

Examples

Successful conversion

SELECT toDateOrDefault('2022-12-30')

2022-12-30

Failed conversion

SELECT toDateOrDefault('', CAST('2023-01-01', 'Date'))

2023-01-01

toDateOrNull​

Introduced in: v1.1

Converts an input value to a value of type Date but returns NULL if an invalid argument is received. The same as toDate but returns NULL if an invalid argument is received.

Syntax

toDateOrNull(x)

Arguments

  • x — A string representation of a date. String

Returned value

Returns a Date value if successful, otherwise NULL. Date or NULL

Examples

Usage example

SELECT toDateOrNull('2025-12-30'), toDateOrNull('invalid')

┌─toDateOrNull('2025-12-30')─┬─toDateOrNull('invalid')─┐
│                 2025-12-30 │                   ᴺᵁᴸᴸ │
└────────────────────────────┴────────────────────────┘

toDateOrZero​

Introduced in: v1.1

Converts an input value to a value of type Date but returns the lower boundary of Date if an invalid argument is received. The same as toDate but returns lower boundary of Date if an invalid argument is received.

See also:

Syntax

toDateOrZero(x)

Arguments

  • x — A string representation of a date. String

Returned value

Returns a Date value if successful, otherwise the lower boundary of Date (1970-01-01). Date

Examples

Usage example

SELECT toDateOrZero('2025-12-30'), toDateOrZero('')

┌─toDateOrZero('2025-12-30')─┬─toDateOrZero('')─┐
│                 2025-12-30 │       1970-01-01 │
└────────────────────────────┴──────────────────┘

toDateTime​

Introduced in: v1.1

Converts an input value to type DateTime.

Note

If expr is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp). If expr is a String, it may be interpreted as a Unix timestamp or as a string representation of date / date with time. Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string '1999' may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.

Syntax

toDateTime(expr[, time_zone])

Arguments

Returned value

Returns a date time. DateTime

Examples

Usage example

SELECT toDateTime('2025-01-01 00:00:00'), toDateTime(1735689600, 'UTC')
FORMAT Vertical

Row 1:
──────
toDateTime('2025-01-01 00:00:00'): 2025-01-01 00:00:00
toDateTime(1735689600, 'UTC'):     2025-01-01 00:00:00

toDateTime32​

Introduced in: v20.9

Converts an input value to type DateTime. Supports conversion from String, FixedString, Date, Date32, DateTime, or numeric types ((U)Int*, Float*, Decimal). DateTime32 provides extended range compared to DateTime, supporting dates from 1900-01-01 to 2299-12-31.

Syntax

toDateTime32(x[, timezone])

Arguments

Returned value

Returns the converted input value. DateTime

Examples

The value is within the range

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

┌───────────────────value─┬─toTypeName(toDateTime64('20255-01-01 00:00:00.000', 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3)                                          │
└─────────────────────────┴────────────────────────────────────────────────────────┘

As a decimal with precision

SELECT toDateTime64(1735689600.000, 3) AS value, toTypeName(value);
-- without the decimal point the value is still treated as Unix Timestamp in seconds
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(toDateTime64(1735689600.000, 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3)                            │
└─────────────────────────┴──────────────────────────────────────────┘
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘

With a timezone

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

┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul')                                      │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘

toDateTime64​

Introduced in: v20.1

Converts an input value to a value of type DateTime64.

Syntax

toDateTime64(expr, scale[, timezone])

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • scale — Tick size (precision): 10^(-scale) seconds. UInt8
  • timezone — Optional. Time zone for the specified DateTime64 object. String

Returned value

Returns a calendar date and time of day, with sub-second precision. DateTime64

Examples

The value is within the range

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

┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00.000', 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3)                                          │
└─────────────────────────┴────────────────────────────────────────────────────────┘

As decimal with precision

SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);
-- Without the decimal point the value is still treated as Unix Timestamp in seconds
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘

With timezone

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

┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul')                                      │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘

toDateTime64OrDefault​

Introduced in: v21.11

Like toDateTime64, this function 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 — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • scale — Tick size (precision): 10^-precision seconds. UInt8
  • timezone — Optional. Time zone. String
  • default — Optional. The default value to return if parsing is unsuccessful. DateTime64

Returned value

Value of type DateTime64 if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00.000 if not. DateTime64

Examples

Successful conversion

SELECT toDateTime64OrDefault('1976-10-18 00:00:00.30', 3)

1976-10-18 00:00:00.300

Failed conversion

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

2000-12-31 23:00:00.000

toDateTime64OrNull​

Introduced in: v20.1

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

Syntax

toDateTime64OrNull(x)

Arguments

  • x — A string representation of a date with time and subsecond precision. String

Returned value

Returns a DateTime64 value if successful, otherwise NULL. DateTime64 or NULL

Examples

Usage example

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

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

toDateTime64OrZero​

Introduced in: v20.1

Converts an input value to a value of type DateTime64 but returns the lower boundary of DateTime64 if an invalid argument is received. The same as toDateTime64 but returns lower boundary of DateTime64 if an invalid argument is received.

See also:

Syntax

toDateTime64OrZero(x)

Arguments

  • x — A string representation of a date with time and subsecond precision. String

Returned value

Returns a DateTime64 value if successful, otherwise the lower boundary of DateTime64 (1970-01-01 00:00:00.000). 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​

Introduced in: v21.11

Like toDateTime but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of DateTime.

Syntax

toDateTimeOrDefault(expr[, timezone, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • timezone — Optional. Time zone. String
  • default — Optional. The default value to return if parsing is unsuccessful. 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

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​

Introduced in: v1.1

Converts an input value to a value of type DateTime but returns NULL if an invalid argument is received. The same as toDateTime but returns NULL if an invalid argument is received.

Syntax

toDateTimeOrNull(x)

Arguments

  • x — A string representation of a date with time. String

Returned value

Returns a DateTime value if successful, otherwise NULL. DateTime or 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​

Introduced in: v1.1

Converts an input value to a value of type DateTime but returns the lower boundary of DateTime if an invalid argument is received. The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received.

Syntax

toDateTimeOrZero(x)

Arguments

  • x — A string representation of a date with time. String

Returned value

Returns a DateTime value if successful, otherwise the lower boundary of DateTime (1970-01-01 00:00:00). 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​

Introduced in: v18.12

Converts an input value to a value of type Decimal(38, S) 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
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a value of type Decimal(38, S) Decimal128(S)

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​

Introduced in: v21.11

Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns the default value in case of an error.

Syntax

toDecimal128OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8
  • default — Optional. The default value to return if parsing to type Decimal128(S) is unsuccessful. Decimal128(S)

Returned value

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

Examples

Successful conversion

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

0.023809523809523808

Failed conversion

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

-1

toDecimal128OrNull​

Introduced in: v20.1

Converts an input value to a value of type Decimal(38, S) but returns NULL in case of an error. Like toDecimal128 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
  • Values that exceed the bounds of Decimal128:(-1*10^(38 - S), 1*10^(38 - S)).

See also:

Syntax

toDecimal128OrNull(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a Decimal(38, S) value if successful, otherwise NULL. Decimal128(S) or NULL

Examples

Usage example

SELECT toDecimal128OrNull('42.7', 2), toDecimal128OrNull('invalid', 2)

┌─toDecimal128OrNull('42.7', 2)─┬─toDecimal128OrNull('invalid', 2)─┐
│                         42.70 │                             ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal128OrZero​

Introduced in: v20.1

Converts an input value to a value of type Decimal(38, S) but returns 0 in case of an error. Like toDecimal128 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
Note

If the input value exceeds the bounds of Decimal128:(-1*10^(38 - S), 1*10^(38 - S)), the function returns 0.

Syntax

toDecimal128OrZero(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • S — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a Decimal(38, S) value if successful, otherwise 0. Decimal128(S)

Examples

Basic usage

SELECT toDecimal128OrZero('42.7', 2), toDecimal128OrZero('invalid', 2)

┌─toDecimal128OrZero('42.7', 2)─┬─toDecimal128OrZero('invalid', 2)─┐
│                         42.70 │                             0.00 │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal256​

Introduced in: v20.8

Converts an input value to a value of type Decimal(76, S) 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 toDecimal256('0xc0fe', 1);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal256:(-1*10^(76 - S), 1*10^(76 - 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: toDecimal256(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: toDecimal256('1.15', 2) = 1.15

Syntax

toDecimal256(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a value of type Decimal(76, S). Decimal256(S)

Examples

Usage example

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

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

toDecimal256OrDefault​

Introduced in: v21.11

Like toDecimal256, this function converts an input value to a value of type Decimal(76, S) but returns the default value in case of an error.

Syntax

toDecimal256OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8
  • default — Optional. The default value to return if parsing to type Decimal256(S) is unsuccessful. Decimal256(S)

Returned value

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

Examples

Successful conversion

SELECT toDecimal256OrDefault(toString(1/42), 76)

0.023809523809523808

Failed conversion

SELECT toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)'))

-1

toDecimal256OrNull​

Introduced in: v20.8

Converts an input value to a value of type Decimal(76, S) but returns NULL in case of an error. Like toDecimal256 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
  • Values that exceed the bounds of Decimal256: (-1 * 10^(76 - S), 1 * 10^(76 - S)).

See also:

Syntax

toDecimal256OrNull(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a Decimal(76, S) value if successful, otherwise NULL. Decimal256(S) or NULL

Examples

Usage example

SELECT toDecimal256OrNull('42.7', 2), toDecimal256OrNull('invalid', 2)

┌─toDecimal256OrNull('42.7', 2)─┬─toDecimal256OrNull('invalid', 2)─┐
│                         42.70 │                             ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal256OrZero​

Introduced in: v20.8

Converts an input value to a value of type Decimal(76, S) but returns 0 in case of an error. Like toDecimal256 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
Note

If the input value exceeds the bounds of Decimal256:(-1*10^(76 - S), 1*10^(76 - S)), the function returns 0.

See also:

Syntax

toDecimal256OrZero(expr, S)

Arguments

  • expr — Expression returning a number or a string representation of a number. Expression
  • S — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8

Returned value

Returns a Decimal(76, S) value if successful, otherwise 0. Decimal256(S)

Examples

Usage example

SELECT toDecimal256OrZero('42.7', 2), toDecimal256OrZero('invalid', 2)

┌─toDecimal256OrZero('42.7', 2)─┬─toDecimal256OrZero('invalid', 2)─┐
│                         42.70 │                             0.00 │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal32​

Introduced in: v18.12

Converts an input value to a value of type Decimal(9, S) 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 toDecimal32('0xc0fe', 1);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal32:(-1*10^(9 - S), 1*10^(9 - 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: toDecimal32(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: toDecimal32('1.15', 2) = 1.15

Syntax

toDecimal32(expr, S)

Arguments

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

Returned value

Returns a value of type Decimal(9, S) Decimal32(S)

Examples

Usage 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
FORMAT Vertical

Row 1:
──────
a:      2
type_a: Decimal(9, 1)
b:      4.2
type_b: Decimal(9, 2)
c:      4.2
type_c: Decimal(9, 3)

toDecimal32OrDefault​

Introduced in: v21.11

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.

Syntax

toDecimal32OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String
  • S — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8
  • default — Optional. The default value to return if parsing to type Decimal32(S) is unsuccessful. Decimal32(S)

Returned value

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

Examples

Successful conversion

SELECT toDecimal32OrDefault(toString(0.0001), 5)

0.0001

Failed conversion

SELECT toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)'))

-1

toDecimal32OrNull​

Introduced in: v20.1

Converts an input value to a value of type Decimal(9, S) but returns NULL in case of an error. Like toDecimal32 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
  • Values that exceed the bounds of Decimal32:(-1*10^(9 - S), 1*10^(9 - S)).

See also:

Syntax

toDecimal32OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(9, S) value if successful, otherwise NULL. Decimal32(S) or NULL

Examples

Usage example

SELECT toDecimal32OrNull('42.7', 2), toDecimal32OrNull('invalid', 2)

┌─toDecimal32OrNull('42.7', 2)─┬─toDecimal32OrNull('invalid', 2)─┐
│                        42.70 │                            ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal32OrZero​

Introduced in: v20.1

Converts an input value to a value of type Decimal(9, S) but returns 0 in case of an error. Like toDecimal32 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
Note

If the input value exceeds the bounds of Decimal32:(-1*10^(9 - S), 1*10^(9 - S)), the function returns 0.

Syntax

toDecimal32OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(9, S) value if successful, otherwise 0. Decimal32(S)

Examples

Usage example

SELECT toDecimal32OrZero('42.7', 2), toDecimal32OrZero('invalid', 2)

┌─toDecimal32OrZero('42.7', 2)─┬─toDecimal32OrZero('invalid', 2)─┐
│                        42.70 │                            0.00 │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal64​

Introduced in: v18.12

Converts an input value to a value of type Decimal(18, S) 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 toDecimal64('0xc0fe', 1);.
Note

An overflow can occur if the value of expr exceeds the bounds of Decimal64:(-1*10^(18 - S), 1*10^(18 - 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: toDecimal64(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: toDecimal64('1.15', 2) = 1.15

Syntax

toDecimal64(expr, S)

Arguments

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

Returned value

Returns a decimal value. Decimal(18, S)

Examples

Usage 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
FORMAT Vertical

Row 1:
──────
a:      2.0
type_a: Decimal(18, 1)
b:      4.20
type_b: Decimal(18, 2)
c:      4.200
type_c: Decimal(18, 3)

toDecimal64OrDefault​

Introduced in: v21.11

Like toDecimal64, this function converts an input value to a value of type Decimal(18, S) but returns the default value in case of an error.

Syntax

toDecimal64OrDefault(expr, S[, default])

Arguments

  • expr — A String representation of a number. String
  • S — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8
  • default — Optional. The default value to return if parsing to type Decimal64(S) is unsuccessful. Decimal64(S)

Returned value

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

Examples

Successful conversion

SELECT toDecimal64OrDefault(toString(0.0001), 18)

0.0001

Failed conversion

SELECT toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)'))

-1

toDecimal64OrNull​

Introduced in: v20.1

Converts an input value to a value of type Decimal(18, S) but returns NULL in case of an error. Like toDecimal64 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
  • Values that exceed the bounds of Decimal64:(-1*10^(18 - S), 1*10^(18 - S)).

See also:

Syntax

toDecimal64OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(18, S) value if successful, otherwise NULL. Decimal64(S) or NULL

Examples

Usage example

SELECT toDecimal64OrNull('42.7', 2), toDecimal64OrNull('invalid', 2)

┌─toDecimal64OrNull('42.7', 2)─┬─toDecimal64OrNull('invalid', 2)─┐
│                        42.70 │                            ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal64OrZero​

Introduced in: v20.1

Converts an input value to a value of type Decimal(18, S) but returns 0 in case of an error. Like toDecimal64 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

  • Values or string representations of Float* values NaN and Inf (case-insensitive).
  • String representations of binary and hexadecimal values.
Note

If the input value exceeds the bounds of Decimal64:(-1*10^(18 - S), 1*10^(18 - S)), the function returns 0.

See also:

Syntax

toDecimal64OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(18, S) value if successful, otherwise 0. Decimal64(S)

Examples

Usage example

SELECT toDecimal64OrZero('42.7', 2), toDecimal64OrZero('invalid', 2)

┌─toDecimal64OrZero('42.7', 2)─┬─toDecimal64OrZero('invalid', 2)─┐
│                        42.70 │                            0.00 │
└──────────────────────────────┴─────────────────────────────────┘

toDecimalString​

Introduced in: v

Returns string representation of a number. First argument is the number of any numeric type, second argument is the desired number of digits in fractional part. Returns String.

Syntax

Arguments

  • None.

Returned value

Examples

toDecimalString

SELECT toDecimalString(2.1456,2)

toFixedString​

Introduced in: v1.1

Converts a String argument to a FixedString(N) type (a string of fixed length N).

If the string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.

Syntax

toFixedString(s, N)

Arguments

  • s — String to convert. String
  • N — Length of the resulting FixedString. const UInt*

Returned value

Returns a FixedString of length N. FixedString(N)

Examples

Usage example

SELECT toFixedString('foo', 8) AS s;

┌─s─────────────┐
│ foo\0\0\0\0\0 │
└───────────────┘

toFloat32​

Introduced in: v1.1

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

Supported arguments:

  • Values of type (U)Int*.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float*, including NaN and Inf.
  • String representations of Float*, including NaN and Inf (case-insensitive).

Unsupported arguments:

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

See also:

Syntax

toFloat32(expr)

Arguments

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

Returned value

Returns a 32-bit floating point value. Float32

Examples

Usage example

SELECT
    toFloat32(42.7),
    toFloat32('42.7'),
    toFloat32('NaN')
FORMAT Vertical

Row 1:
──────
toFloat32(42.7):   42.7
toFloat32('42.7'): 42.7
toFloat32('NaN'):  nan

toFloat32OrDefault​

Introduced in: v21.11

Like toFloat32, this function converts an input value to a value of type Float32 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

toFloat32OrDefault(expr[, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Float32

Returned value

Returns a value of type Float32 if successful, otherwise returns the default value if passed or 0 if not. Float32

Examples

Successful conversion

SELECT toFloat32OrDefault('8', CAST('0', 'Float32'))

8

Failed conversion

SELECT toFloat32OrDefault('abc', CAST('0', 'Float32'))

0

toFloat32OrNull​

Introduced in: v1.1

Converts an input value to a value of type Float32 but returns NULL in case of an error. Like toFloat32 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

  • Values of type (U)Int*.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float*, including NaN and Inf.
  • String representations of Float*, including NaN and Inf (case-insensitive).

Unsupported arguments (return NULL):

  • String representations of binary and hexadecimal values, e.g. SELECT toFloat32OrNull('0xc0fe');.
  • Invalid string formats.

See also:

Syntax

toFloat32OrNull(x)

Arguments

  • x — A string representation of a number. String

Returned value

Returns a 32-bit Float value if successful, otherwise NULL. Float32 or NULL

Examples

Usage example

SELECT
    toFloat32OrNull('42.7'),
    toFloat32OrNull('NaN'),
    toFloat32OrNull('abc')
FORMAT Vertical

Row 1:
──────
toFloat32OrNull('42.7'): 42.7
toFloat32OrNull('NaN'):  nan
toFloat32OrNull('abc'):  \N

toFloat32OrZero​

Introduced in: v1.1

Converts an input value to a value of type Float32 but returns 0 in case of an error. Like toFloat32 but returns 0 instead of throwing an exception on conversion errors.

See also:

Syntax

toFloat32OrZero(x)

Arguments

  • x — A string representation of a number. String

Returned value

Returns a 32-bit Float value if successful, otherwise 0. Float32

Examples

Usage example

SELECT
    toFloat32OrZero('42.7'),
    toFloat32OrZero('abc')
FORMAT Vertical

Row 1:
──────
toFloat32OrZero('42.7'): 42.7
toFloat32OrZero('abc'):  0

toFloat64​

Introduced in: v1.1

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

Supported arguments:

  • Values of type (U)Int*.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float*, including NaN and Inf.
  • String representations of type Float*, including NaN and Inf (case-insensitive).

Unsupported arguments:

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

See also:

Syntax

toFloat64(expr)

Arguments

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

Returned value

Returns a 64-bit floating point value. Float64

Examples

Usage example

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

Row 1:
──────
toFloat64(42.7):   42.7
toFloat64('42.7'): 42.7
toFloat64('NaN'):  nan

toFloat64OrDefault​

Introduced in: v21.11

Like toFloat64, this function converts an input value to a value of type Float64 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

toFloat64OrDefault(expr[, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Float64

Returned value

Returns a value of type Float64 if successful, otherwise returns the default value if passed or 0 if not. Float64

Examples

Successful conversion

SELECT toFloat64OrDefault('8', CAST('0', 'Float64'))

8

Failed conversion

SELECT toFloat64OrDefault('abc', CAST('0', 'Float64'))

0

toFloat64OrNull​

Introduced in: v1.1

Converts an input value to a value of type Float64 but returns NULL in case of an error. Like toFloat64 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

  • Values of type (U)Int*.
  • String representations of (U)Int8/16/32/128/256.
  • Values of type Float*, including NaN and Inf.
  • String representations of type Float*, including NaN and Inf (case-insensitive).

Unsupported arguments (return NULL):

  • String representations of binary and hexadecimal values, e.g. SELECT toFloat64OrNull('0xc0fe');.
  • Invalid string formats.

See also:

Syntax

toFloat64OrNull(x)

Arguments

  • x — A string representation of a number. String

Returned value

Returns a 64-bit Float value if successful, otherwise NULL. Float64 or NULL

Examples

Usage example

SELECT
    toFloat64OrNull('42.7'),
    toFloat64OrNull('NaN'),
    toFloat64OrNull('abc')
FORMAT Vertical

Row 1:
──────
toFloat64OrNull('42.7'): 42.7
toFloat64OrNull('NaN'):  nan
toFloat64OrNull('abc'):  \N

toFloat64OrZero​

Introduced in: v1.1

Converts an input value to a value of type Float64 but returns 0 in case of an error. Like toFloat64 but returns 0 instead of throwing an exception on conversion errors.

See also:

Syntax

toFloat64OrZero(x)

Arguments

  • x — A string representation of a number. String

Returned value

Returns a 64-bit Float value if successful, otherwise 0. Float64

Examples

Usage example

SELECT
    toFloat64OrZero('42.7'),
    toFloat64OrZero('abc')
FORMAT Vertical

Row 1:
──────
toFloat64OrZero('42.7'): 42.7
toFloat64OrZero('abc'):  0

toInt128​

Introduced in: v1.1

Converts an input value to a value of type Int128. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt128('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int128, the result over or under flows. This is not considered an error.

See also:

Syntax

toInt128(expr)

Arguments

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

Returned value

Returns a 128-bit integer value. Int128

Examples

Usage example

SELECT
    toInt128(-128),
    toInt128(-128.8),
    toInt128('-128')
FORMAT Vertical

Row 1:
──────
toInt128(-128):   -128
toInt128(-128.8): -128
toInt128('-128'): -128

toInt128OrDefault​

Introduced in: v21.11

Like toInt128, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int128

Returned value

Returns a value of type Int128 if successful, otherwise returns the default value if passed, or 0 if not. Int128

Examples

Successful conversion

SELECT toInt128OrDefault('-128', CAST('-1', 'Int128'))

-128

Failed conversion

SELECT toInt128OrDefault('abc', CAST('-1', 'Int128'))

-1

toInt128OrNull​

Introduced in: v20.8

Like toInt128, this function converts an input value to a value of type Int128 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt128OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt128OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int128, otherwise NULL if the conversion is unsuccessful. Int128 or NULL

Examples

Usage example

SELECT
    toInt128OrNull('-128'),
    toInt128OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt128OrNull('-128'): -128
toInt128OrNull('abc'):  \N

toInt128OrZero​

Introduced in: v20.8

Converts an input value to type Int128 but returns 0 in case of an error. Like toInt128 but returns 0 instead of throwing an exception.

See also:

Syntax

toInt128OrZero(x)

Arguments

Returned value

Returns the converted input value, otherwise 0 if conversion fails. Int128

Examples

Usage example

SELECT toInt128OrZero('123')

123

Failed conversion returns zero

SELECT toInt128OrZero('abc')

0

toInt16​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt16('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toInt16(32768) == -32768;.

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toInt16(expr)

Arguments

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

Returned value

Returns a 16-bit integer value. Int16

Examples

Usage example

SELECT
    toInt16(-16),
    toInt16(-16.16),
    toInt16('-16')
FORMAT Vertical

Row 1:
──────
toInt16(-16):    -16
toInt16(-16.16): -16
toInt16('-16'):  -16

toInt16OrDefault​

Introduced in: v21.11

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 — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int16

Returned value

Returns a value of type Int16 if successful, otherwise returns the default value if passed, or 0 if not. Int16

Examples

Successful conversion

SELECT toInt16OrDefault('-16', CAST('-1', 'Int16'))

-16

Failed conversion

SELECT toInt16OrDefault('abc', CAST('-1', 'Int16'))

-1

toInt16OrNull​

Introduced in: v1.1

Like toInt16, this function converts an input value to a value of type Int16 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt16OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt16OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int16, otherwise NULL if the conversion is unsuccessful. Int16 or NULL

Examples

Usage example

SELECT
    toInt16OrNull('-16'),
    toInt16OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt16OrNull('-16'): -16
toInt16OrNull('abc'): \N

toInt16OrZero​

Introduced in: v1.1

Like toInt16, this function converts an input value to a value of type Int16 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt16OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt16OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int16, otherwise 0 if the conversion is unsuccessful. Int16

Examples

Usage example

SELECT
    toInt16OrZero('16'),
    toInt16OrZero('abc')
FORMAT Vertical

Row 1:
──────
toInt16OrZero('16'): 16
toInt16OrZero('abc'): 0

toInt256​

Introduced in: v1.1

Converts an input value to a value of type Int256. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt256('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int256, the result over or under flows. This is not considered an error.

See also:

Syntax

toInt256(expr)

Arguments

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

Returned value

Returns a 256-bit integer value. Int256

Examples

Usage example

SELECT
    toInt256(-256),
    toInt256(-256.256),
    toInt256('-256')
FORMAT Vertical

Row 1:
──────
toInt256(-256):     -256
toInt256(-256.256): -256
toInt256('-256'):   -256

toInt256OrDefault​

Introduced in: v21.11

Like toInt256, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int256

Returned value

Returns a value of type Int256 if successful, otherwise returns the default value if passed, or 0 if not. Int256

Examples

Successful conversion

SELECT toInt256OrDefault('-256', CAST('-1', 'Int256'))

-256

Failed conversion

SELECT toInt256OrDefault('abc', CAST('-1', 'Int256'))

-1

toInt256OrNull​

Introduced in: v20.8

Like toInt256, this function converts an input value to a value of type Int256 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt256OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt256OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int256, otherwise NULL if the conversion is unsuccessful. Int256 or NULL

Examples

Usage example

SELECT
    toInt256OrNull('-256'),
    toInt256OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt256OrNull('-256'): -256
toInt256OrNull('abc'):  \N

toInt256OrZero​

Introduced in: v20.8

Converts an input value to type Int256 but returns 0 in case of an error. Like toInt256 but returns 0 instead of throwing an exception.

See also:

Syntax

toInt256OrZero(x)

Arguments

Returned value

Returns the converted input value, otherwise 0 if conversion fails. Int256

Examples

Usage example

SELECT toInt256OrZero('123')

123

Failed conversion returns zero

SELECT toInt256OrZero('abc')

0

toInt32​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt32('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int32, the result over or under flows. This is not considered an error. For example: SELECT toInt32(2147483648) == -2147483648;

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toInt32(expr)

Arguments

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

Returned value

Returns a 32-bit integer value. Int32

Examples

Usage example

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

Row 1:
──────
toInt32(-32):    -32
toInt32(-32.32): -32
toInt32('-32'):  -32

toInt32OrDefault​

Introduced in: v21.11

Like toInt32, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int32

Returned value

Returns a value of type Int32 if successful, otherwise returns the default value if passed or 0 if not. Int32

Examples

Successful conversion

SELECT toInt32OrDefault('-32', CAST('-1', 'Int32'))

-32

Failed conversion

SELECT toInt32OrDefault('abc', CAST('-1', 'Int32'))

-1

toInt32OrNull​

Introduced in: v1.1

Like toInt32, this function converts an input value to a value of type Int32 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt32OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt32OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int32, otherwise NULL if the conversion is unsuccessful. Int32 or NULL

Examples

Usage example

SELECT
    toInt32OrNull('-32'),
    toInt32OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt32OrNull('-32'): -32
toInt32OrNull('abc'): \N

toInt32OrZero​

Introduced in: v1.1

Like toInt32, this function converts an input value to a value of type Int32 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt32OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt32OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int32, otherwise 0 if the conversion is unsuccessful. Int32

Examples

Usage example

SELECT
    toInt32OrZero('32'),
    toInt32OrZero('abc')
FORMAT Vertical

Row 1:
──────
toInt32OrZero('32'): 32
toInt32OrZero('abc'): 0

toInt64​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt64('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int64, the result over or under flows. This is not considered an error. For example: SELECT toInt64(9223372036854775808) == -9223372036854775808;

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toInt64(expr)

Arguments

  • expr — Expression returning a number or a string representation of a number. Supported: values or string representations of type (U)Int*, values of type Float*. Unsupported: string representations of Float* values including NaN and Inf, string representations of binary and hexadecimal values. Expression

Returned value

Returns a 64-bit integer value. Int64

Examples

Usage example

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

Row 1:
──────
toInt64(-64):    -64
toInt64(-64.64): -64
toInt64('-64'):  -64

toInt64OrDefault​

Introduced in: v21.11

Like toInt64, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int64

Returned value

Returns a value of type Int64 if successful, otherwise returns the default value if passed, or 0 if not. Int64

Examples

Successful conversion

SELECT toInt64OrDefault('-64', CAST('-1', 'Int64'))

-64

Failed conversion

SELECT toInt64OrDefault('abc', CAST('-1', 'Int64'))

-1

toInt64OrNull​

Introduced in: v1.1

Like toInt64, this function converts an input value to a value of type Int64 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt64OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt64OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int64, otherwise NULL if the conversion is unsuccessful. Int64 or NULL

Examples

Usage example

SELECT
    toInt64OrNull('-64'),
    toInt64OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt64OrNull('-64'): -64
toInt64OrNull('abc'): \N

toInt64OrZero​

Introduced in: v1.1

Converts an input value to type Int64 but returns 0 in case of an error. Like toInt64 but returns 0 instead of throwing an exception.

See also:

Syntax

toInt64OrZero(x)

Arguments

Returned value

Returns the converted input value, otherwise 0 if conversion fails. Int64

Examples

Usage example

SELECT toInt64OrZero('123')

123

Failed conversion returns zero

SELECT toInt64OrZero('abc')

0

toInt8​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt8('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toInt8(128) == -128;.

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toInt8(expr)

Arguments

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

Returned value

Returns an 8-bit integer value. Int8

Examples

Usage example

SELECT
    toInt8(-8),
    toInt8(-8.8),
    toInt8('-8')
FORMAT Vertical

Row 1:
──────
toInt8(-8):   -8
toInt8(-8.8): -8
toInt8('-8'): -8

toInt8OrDefault​

Introduced in: v21.11

Like toInt8, this function converts an input value to a value of type Int8 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

toInt8OrDefault(expr[, default])

Arguments

  • expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. Int8

Returned value

Returns a value of type Int8 if successful, otherwise returns the default value if passed, or 0 if not. Int8

Examples

Successful conversion

SELECT toInt8OrDefault('-8', CAST('-1', 'Int8'))

-8

Failed conversion

SELECT toInt8OrDefault('abc', CAST('-1', 'Int8'))

-1

toInt8OrNull​

Introduced in: v1.1

Like toInt8, this function converts an input value to a value of type Int8 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt8OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt8OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int8, otherwise NULL if the conversion is unsuccessful. Int8 or NULL

Examples

Usage example

SELECT
    toInt8OrNull('-8'),
    toInt8OrNull('abc')
FORMAT Vertical

Row 1:
──────
toInt8OrNull('-8'):  -8
toInt8OrNull('abc'): \N

toInt8OrZero​

Introduced in: v1.1

Like toInt8, this function converts an input value to a value of type Int8 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toInt8OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toInt8OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type Int8, otherwise 0 if the conversion is unsuccessful. Int8

Examples

Usage example

SELECT
    toInt8OrZero('8'),
    toInt8OrZero('abc')
FORMAT Vertical

Row 1:
──────
toInt8OrZero('8'): 8
toInt8OrZero('abc'): 0

toInterval​

Introduced in: v

Creates an interval from a value and a unit.

Syntax

Arguments

  • None.

Returned value

Examples

toIntervalDay​

Introduced in: v1.1

Returns an interval of n days of data type IntervalDay.

Syntax

toIntervalDay(n)

Arguments

  • n — Number of days. Integer numbers or string representations thereof, and float numbers. (U)Int* or Float* or String

Returned value

Returns an interval of n days. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalDay(5) AS interval_to_days
SELECT date + interval_to_days AS result

┌─────result─┐
│ 2025-06-20 │
└────────────┘

toIntervalHour​

Introduced in: v1.1

Returns an interval of n hours of data type IntervalHour.

Syntax

toIntervalHour(n)

Arguments

  • n — Number of hours. Integer numbers or string representations thereof, and float numbers. Int* or UInt* or Float* or String

Returned value

Returns an interval of n hours. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalHour(12) AS interval_to_hours
SELECT date + interval_to_hours AS result

┌──────────────result─┐
│ 2025-06-15 12:00:00 │
└─────────────────────┘

toIntervalMicrosecond​

Introduced in: v22.6

Returns an interval of n microseconds of data type IntervalMicrosecond.

Syntax

toIntervalMicrosecond(n)

Arguments

Returned value

Returns an interval of n microseconds. Interval

Examples

Usage example

WITH
    toDateTime('2025-06-15') AS date,
    toIntervalMicrosecond(30) AS interval_to_microseconds
SELECT date + interval_to_microseconds AS result

┌─────────────────────result─┐
│ 2025-06-15 00:00:00.000030 │
└────────────────────────────┘

toIntervalMillisecond​

Introduced in: v22.6

Returns an interval of n milliseconds of data type IntervalMillisecond.

Syntax

toIntervalMillisecond(n)

Arguments

Returned value

Returns an interval of n milliseconds. Interval

Examples

Usage example

WITH
    toDateTime('2025-06-15') AS date,
    toIntervalMillisecond(30) AS interval_to_milliseconds
SELECT date + interval_to_milliseconds AS result

┌──────────────────result─┐
│ 2025-06-15 00:00:00.030 │
└─────────────────────────┘

toIntervalMinute​

Introduced in: v1.1

Returns an interval of n minutes of data type IntervalMinute.

Syntax

toIntervalMinute(n)

Arguments

  • n — Number of minutes. Integer numbers or string representations thereof, and float numbers. (U)Int* or Float* or String

Returned value

Returns an interval of n minutes. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalMinute(12) AS interval_to_minutes
SELECT date + interval_to_minutes AS result

┌──────────────result─┐
│ 2025-06-15 00:12:00 │
└─────────────────────┘

toIntervalMonth​

Introduced in: v1.1

Returns an interval of n months of data type IntervalMonth.

Syntax

toIntervalMonth(n)

Arguments

Returned value

Returns an interval of n months. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalMonth(1) AS interval_to_month
SELECT date + interval_to_month AS result

┌─────result─┐
│ 2025-07-15 │
└────────────┘

toIntervalNanosecond​

Introduced in: v22.6

Returns an interval of n nanoseconds of data type IntervalNanosecond.

Syntax

toIntervalNanosecond(n)

Arguments

Returned value

Returns an interval of n nanoseconds. Interval

Examples

Usage example

WITH
    toDateTime('2025-06-15') AS date,
    toIntervalNanosecond(30) AS interval_to_nanoseconds
SELECT date + interval_to_nanoseconds AS result

┌────────────────────────result─┐
│ 2025-06-15 00:00:00.000000030 │
└───────────────────────────────┘

toIntervalQuarter​

Introduced in: v1.1

Returns an interval of n quarters of data type IntervalQuarter.

Syntax

toIntervalQuarter(n)

Arguments

Returned value

Returns an interval of n quarters. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalQuarter(1) AS interval_to_quarter
SELECT date + interval_to_quarter AS result

┌─────result─┐
│ 2025-09-15 │
└────────────┘

toIntervalSecond​

Introduced in: v1.1

Returns an interval of n seconds of data type IntervalSecond.

Syntax

toIntervalSecond(n)

Arguments

  • n — Number of seconds. Integer numbers or string representations thereof, and float numbers. (U)Int* or Float* or String

Returned value

Returns an interval of n seconds. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalSecond(30) AS interval_to_seconds
SELECT date + interval_to_seconds AS result

┌──────────────result─┐
│ 2025-06-15 00:00:30 │
└─────────────────────┘

toIntervalWeek​

Introduced in: v1.1

Returns an interval of n weeks of data type IntervalWeek.

Syntax

toIntervalWeek(n)

Arguments

Returned value

Returns an interval of n weeks. Interval

Examples

Usage example

WITH
    toDate('2025-06-15') AS date,
    toIntervalWeek(1) AS interval_to_week
SELECT date + interval_to_week AS result

┌─────result─┐
│ 2025-06-22 │
└────────────┘

toIntervalYear​

Introduced in: v1.1

Returns an interval of n years of data type IntervalYear.

Syntax

toIntervalYear(n)

Arguments

Returned value

Returns an interval of n years. Interval

Examples

Usage example

WITH
    toDate('2024-06-15') AS date,
    toIntervalYear(1) AS interval_to_year
SELECT date + interval_to_year AS result

┌─────result─┐
│ 2025-06-15 │
└────────────┘

toLowCardinality​

Introduced in: v18.12

Converts the input argument to the LowCardinality version of same data type.

Tip

To convert from the LowCardinality data type to a regular data type, use the CAST function. For example: CAST(x AS String).

Syntax

toLowCardinality(expr)

Arguments

Returned value

Returns the input value converted to the LowCardinality data type. LowCardinality

Examples

Usage example

SELECT toLowCardinality('1')

┌─toLowCardinality('1')─┐
│ 1                     │
└───────────────────────┘

toString​

Introduced in: v1.1

Converts values to their string representation. For DateTime arguments, the function can take a second String argument containing the name of the time zone.

Syntax

toString(value[, timezone])

Arguments

  • value — Value to convert to string. Any
  • timezone — Optional. Timezone name for DateTime conversion. String

Returned value

Returns a string representation of the input value. String

Examples

Usage example

SELECT
    now() AS ts,
    time_zone,
    toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam  │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra    │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan  │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens     │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast    │ 2023-09-08 20:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘

toStringCutToZero​

Introduced in: v1.1

Accepts a String or FixedString argument and returns a String that contains a copy of the original string truncated at the first null byte.

Null bytes (\0) are considered as string terminators. This function is useful for processing C-style strings or binary data where null bytes mark the end of meaningful content.

Syntax

toStringCutToZero(s)

Arguments

Returned value

Returns a String containing the characters before the first null byte. String

Examples

Usage example

SELECT
    toStringCutToZero('hello'),
    toStringCutToZero('hello\0world')

┌─toStringCutToZero('hello')─┬─toStringCutToZero('hello\\0world')─┐
│ hello                      │ hello                             │
└────────────────────────────┴───────────────────────────────────┘

toTime​

Introduced in: v1.1

Converts an input value to type Time. Supports conversion from String, FixedString, DateTime, or numeric types representing seconds since midnight.

Syntax

toTime(x)

Arguments

Returned value

Returns the converted value. Time

Examples

String to Time conversion

SELECT toTime('14:30:25')

14:30:25

DateTime to Time conversion

SELECT toTime(toDateTime('2025-04-15 14:30:25'))

14:30:25

Integer to Time conversion

SELECT toTime(52225)

14:30:25

toTime64​

Introduced in: v25.6

Converts an input value to type Time64. Supports conversion from String, FixedString, DateTime64, or numeric types representing microseconds since midnight. Provides microsecond precision for time values.

Syntax

toTime64(x)

Arguments

Returned value

Returns the converted input value with microsecond precision. Time64(6)

Examples

String to Time64 conversion

SELECT toTime64('14:30:25.123456')

14:30:25.123456

DateTime64 to Time64 conversion

SELECT toTime64(toDateTime64('2025-04-15 14:30:25.123456', 6))

14:30:25.123456

Integer to Time64 conversion

SELECT toTime64(52225123456)

14:30:25.123456

toTime64OrNull​

Introduced in: v25.6

Converts an input value to a value of type Time64 but returns NULL in case of an error. Like toTime64 but returns NULL instead of throwing an exception on conversion errors.

See also:

Syntax

toTime64OrNull(x)

Arguments

  • x — A string representation of a time with subsecond precision. String

Returned value

Returns a Time64 value if successful, otherwise NULL. Time64 or NULL

Examples

Usage example

SELECT toTime64OrNull('12:30:45.123'), toTime64OrNull('invalid')

┌─toTime64OrNull('12:30:45.123')─┬─toTime64OrNull('invalid')─┐
│                   12:30:45.123 │                      ᴺᵁᴸᴸ │
└────────────────────────────────┴───────────────────────────┘

toTime64OrZero​

Introduced in: v25.6

Converts an input value to a value of type Time64 but returns 00:00:00.000 in case of an error. Like toTime64 but returns 00:00:00.000 instead of throwing an exception on conversion errors.

Syntax

toTime64OrZero(x)

Arguments

  • x — A string representation of a time with subsecond precision. String

Returned value

Returns a Time64 value if successful, otherwise 00:00:00.000. Time64

Examples

Usage example

SELECT toTime64OrZero('12:30:45.123'), toTime64OrZero('invalid')

┌─toTime64OrZero('12:30:45.123')─┬─toTime64OrZero('invalid')─┐
│                   12:30:45.123 │             00:00:00.000 │
└────────────────────────────────┴──────────────────────────┘

toTimeOrNull​

Introduced in: v1.1

Converts an input value to a value of type Time but returns NULL in case of an error. Like toTime but returns NULL instead of throwing an exception on conversion errors.

See also:

Syntax

toTimeOrNull(x)

Arguments

  • x — A string representation of a time. String

Returned value

Returns a Time value if successful, otherwise NULL. Time or NULL

Examples

Usage example

SELECT toTimeOrNull('12:30:45'), toTimeOrNull('invalid')

┌─toTimeOrNull('12:30:45')─┬─toTimeOrNull('invalid')─┐
│                 12:30:45 │                    ᴺᵁᴸᴸ │
└──────────────────────────┴─────────────────────────┘

toTimeOrZero​

Introduced in: v1.1

Converts an input value to a value of type Time but returns 00:00:00 in case of an error. Like toTime but returns 00:00:00 instead of throwing an exception on conversion errors.

Syntax

toTimeOrZero(x)

Arguments

  • x — A string representation of a time. String

Returned value

Returns a Time value if successful, otherwise 00:00:00. Time

Examples

Usage example

SELECT toTimeOrZero('12:30:45'), toTimeOrZero('invalid')

┌─toTimeOrZero('12:30:45')─┬─toTimeOrZero('invalid')─┐
│                 12:30:45 │                00:00:00 │
└──────────────────────────┴─────────────────────────┘

toUInt128​

Introduced in: v1.1

Converts an input value to a value of type UInt128. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt128('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt128, the result over or under flows. This is not considered an error.

See also:

Syntax

toUInt128(expr)

Arguments

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

Returned value

Returns a 128-bit unsigned integer value. UInt128

Examples

Usage example

SELECT
    toUInt128(128),
    toUInt128(128.8),
    toUInt128('128')
FORMAT Vertical

Row 1:
──────
toUInt128(128):   128
toUInt128(128.8): 128
toUInt128('128'): 128

toUInt128OrDefault​

Introduced in: v21.11

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 — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt128

Returned value

Returns a value of type UInt128 if successful, otherwise returns the default value if passed, or 0 if not. UInt128

Examples

Successful conversion

SELECT toUInt128OrDefault('128', CAST('0', 'UInt128'))

128

Failed conversion

SELECT toUInt128OrDefault('abc', CAST('0', 'UInt128'))

0

toUInt128OrNull​

Introduced in: v21.6

Like toUInt128, this function converts an input value to a value of type UInt128 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt128OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt128, otherwise NULL if the conversion is unsuccessful. UInt128 or NULL

Examples

Usage example

SELECT
    toUInt128OrNull('128'),
    toUInt128OrNull('abc')
FORMAT Vertical

Row 1:
──────
toUInt128OrNull('128'): 128
toUInt128OrNull('abc'): \N

toUInt128OrZero​

Introduced in: v1.1

Like toUInt128, this function converts an input value to a value of type UInt128 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt128OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt128, otherwise 0 if the conversion is unsuccessful. UInt128

Examples

Usage example

SELECT
    toUInt128OrZero('128'),
    toUInt128OrZero('abc')
FORMAT Vertical

Row 1:
──────
toUInt128OrZero('128'): 128
toUInt128OrZero('abc'): 0

toUInt16​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt16('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt16(65536) == 0;.

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toUInt16(expr)

Arguments

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

Returned value

Returns a 16-bit unsigned integer value. UInt16

Examples

Usage example

SELECT
    toUInt16(16),
    toUInt16(16.16),
    toUInt16('16')
FORMAT Vertical

Row 1:
──────
toUInt16(16):    16
toUInt16(16.16): 16
toUInt16('16'):  16

toUInt16OrDefault​

Introduced in: v21.11

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 — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt16

Returned value

Returns a value of type UInt16 if successful, otherwise returns the default value if passed, or 0 if not. UInt16

Examples

Successful conversion

SELECT toUInt16OrDefault('16', CAST('0', 'UInt16'))

16

Failed conversion

SELECT toUInt16OrDefault('abc', CAST('0', 'UInt16'))

0

toUInt16OrNull​

Introduced in: v1.1

Like toUInt16, this function converts an input value to a value of type UInt16 but returns NULL in case of an error.

Supported arguments:

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

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt16OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt16, otherwise NULL if the conversion is unsuccessful. UInt16 or NULL

Examples

Usage example

SELECT
    toUInt16OrNull('16'),
    toUInt16OrNull('abc')
FORMAT Vertical

Row 1:
──────
toUInt16OrNull('16'):  16
toUInt16OrNull('abc'): \N

toUInt16OrZero​

Introduced in: v1.1

Like toUInt16, this function converts an input value to a value of type UInt16 but returns 0 in case of an error.

Supported arguments:

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

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt16OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt16, otherwise 0 if the conversion is unsuccessful. UInt16

Examples

Usage example

SELECT
    toUInt16OrZero('16'),
    toUInt16OrZero('abc')
FORMAT Vertical

Row 1:
──────
toUInt16OrZero('16'):  16
toUInt16OrZero('abc'): 0

toUInt256​

Introduced in: v1.1

Converts an input value to a value of type UInt256. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt256('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt256, the result over or under flows. This is not considered an error.

See also:

Syntax

toUInt256(expr)

Arguments

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

Returned value

Returns a 256-bit unsigned integer value. UInt256

Examples

Usage example

SELECT
    toUInt256(256),
    toUInt256(256.256),
    toUInt256('256')
FORMAT Vertical

Row 1:
──────
toUInt256(256):     256
toUInt256(256.256): 256
toUInt256('256'):   256

toUInt256OrDefault​

Introduced in: v21.11

Like toUInt256, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt256

Returned value

Returns a value of type UInt256 if successful, otherwise returns the default value if passed, or 0 if not. UInt256

Examples

Successful conversion

SELECT toUInt256OrDefault('-256', CAST('0', 'UInt256'))

0

Failed conversion

SELECT toUInt256OrDefault('abc', CAST('0', 'UInt256'))

0

toUInt256OrNull​

Introduced in: v20.8

Like toUInt256, this function converts an input value to a value of type UInt256 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt256OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt256, otherwise NULL if the conversion is unsuccessful. UInt256 or NULL

Examples

Usage example

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

Row 1:
──────
toUInt256OrNull('256'): 256
toUInt256OrNull('abc'): \N

toUInt256OrZero​

Introduced in: v20.8

Like toUInt256, this function converts an input value to a value of type UInt256 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt256OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt256, otherwise 0 if the conversion is unsuccessful. UInt256

Examples

Usage example

SELECT
    toUInt256OrZero('256'),
    toUInt256OrZero('abc')
FORMAT Vertical

Row 1:
──────
toUInt256OrZero('256'): 256
toUInt256OrZero('abc'): 0

toUInt32​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt32('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt32, the result over or under flows. This is not considered an error. For example: SELECT toUInt32(4294967296) == 0;

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toUInt32(expr)

Arguments

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

Returned value

Returns a 32-bit unsigned integer value. UInt32

Examples

Usage example

SELECT
    toUInt32(32),
    toUInt32(32.32),
    toUInt32('32')
FORMAT Vertical

Row 1:
──────
toUInt32(32):    32
toUInt32(32.32): 32
toUInt32('32'):  32

toUInt32OrDefault​

Introduced in: v21.11

Like toUInt32, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt32

Returned value

Returns a value of type UInt32 if successful, otherwise returns the default value if passed, or 0 if not. UInt32

Examples

Successful conversion

SELECT toUInt32OrDefault('32', CAST('0', 'UInt32'))

32

Failed conversion

SELECT toUInt32OrDefault('abc', CAST('0', 'UInt32'))

0

toUInt32OrNull​

Introduced in: v1.1

Like toUInt32, this function converts an input value to a value of type UInt32 but returns NULL in case of an error.

Supported arguments:

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

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt32OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt32, otherwise NULL if the conversion is unsuccessful. UInt32 or NULL

Examples

Usage example

SELECT
    toUInt32OrNull('32'),
    toUInt32OrNull('abc')
FORMAT Vertical

Row 1:
──────
toUInt32OrNull('32'):  32
toUInt32OrNull('abc'): \N

toUInt32OrZero​

Introduced in: v1.1

Like toUInt32, this function converts an input value to a value of type UInt32 but returns 0 in case of an error.

Supported arguments:

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

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt32OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt32, otherwise 0 if the conversion is unsuccessful. UInt32

Examples

Usage example

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

Row 1:
──────
toUInt32OrZero('32'):  32
toUInt32OrZero('abc'): 0

toUInt64​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported types:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt64('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt64, the result over or under flows. This is not considered an error. For example: SELECT toUInt64(18446744073709551616) == 0;

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toUInt64(expr)

Arguments

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

Returned value

Returns a 64-bit unsigned integer value. UInt64

Examples

Usage example

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

Row 1:
──────
toUInt64(64):    64
toUInt64(64.64): 64
toUInt64('64'):  64

toUInt64OrDefault​

Introduced in: v21.11

Like toUInt64, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt64

Returned value

Returns a value of type UInt64 if successful, otherwise returns the default value if passed, or 0 if not. UInt64

Examples

Successful conversion

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

64

Failed conversion

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

0

toUInt64OrNull​

Introduced in: v1.1

Like toUInt64, this function converts an input value to a value of type UInt64 but returns NULL in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return NULL):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt64OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt64, otherwise NULL if the conversion is unsuccessful. UInt64 or NULL

Examples

Usage example

SELECT
    toUInt64OrNull('64'),
    toUInt64OrNull('abc')
FORMAT Vertical

Row 1:
──────
toUInt64OrNull('64'):  64
toUInt64OrNull('abc'): \N

toUInt64OrZero​

Introduced in: v1.1

Like toUInt64, this function converts an input value to a value of type UInt64 but returns 0 in case of an error.

Supported arguments:

  • String representations of (U)Int*.

Unsupported arguments (return 0):

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt64OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt64, otherwise 0 if the conversion is unsuccessful. UInt64

Examples

Usage example

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

Row 1:
──────
toUInt64OrZero('64'):  64
toUInt64OrZero('abc'): 0

toUInt8​

Introduced in: v1.1

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

Supported arguments:

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

Unsupported arguments:

  • String representations of Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt8('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt8(256) == 0;.

Note

The function uses rounding towards zero, meaning it truncates fractional digits of numbers.

See also:

Syntax

toUInt8(expr)

Arguments

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

Returned value

Returns an 8-bit unsigned integer value. UInt8

Examples

Usage example

SELECT
    toUInt8(8),
    toUInt8(8.8),
    toUInt8('8')
FORMAT Vertical

Row 1:
──────
toUInt8(8):   8
toUInt8(8.8): 8
toUInt8('8'): 8

toUInt8OrDefault​

Introduced in: v21.11

Like toUInt8, this function 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 returning a number or a string representation of a number. String or (U)Int* or Float*
  • default — Optional. The default value to return if parsing is unsuccessful. UInt8

Returned value

Returns a value of type UInt8 if successful, otherwise returns the default value if passed, or 0 if not. UInt8

Examples

Successful conversion

SELECT toUInt8OrDefault('8', CAST('0', 'UInt8'))

8

Failed conversion

SELECT toUInt8OrDefault('abc', CAST('0', 'UInt8'))

0

toUInt8OrNull​

Introduced in: v1.1

Like toUInt8, this function converts an input value to a value of type UInt8 but returns NULL in case of an error.

Supported arguments:

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

Unsupported arguments (return NULL):

  • String representations of ordinary Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrNull('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt8OrNull(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt8, otherwise NULL if the conversion is unsuccessful. UInt8 or NULL

Examples

Usage example

SELECT
    toUInt8OrNull('42'),
    toUInt8OrNull('abc')
FORMAT Vertical

Row 1:
──────
toUInt8OrNull('42'):  42
toUInt8OrNull('abc'): \N

toUInt8OrZero​

Introduced in: v1.1

Like toUInt8, this function converts an input value to a value of type UInt8 but returns 0 in case of an error.

Supported arguments:

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

Unsupported arguments (return 0):

  • String representations of ordinary Float* values, including NaN and Inf.
  • String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrZero('0xc0fe');.
Note

If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.

See also:

Syntax

toUInt8OrZero(x)

Arguments

  • x — A String representation of a number. String

Returned value

Returns a value of type UInt8, otherwise 0 if the conversion is unsuccessful. UInt8

Examples

Usage example

SELECT
    toUInt8OrZero('-8'),
    toUInt8OrZero('abc')
FORMAT Vertical

Row 1:
──────
toUInt8OrZero('-8'):  0
toUInt8OrZero('abc'): 0

toUUID​

Introduced in: v1.1

Converts a String value to a UUID value.

Syntax

toUUID(string)

Arguments

Returned value

Returns a UUID from the string representation of the UUID. UUID

Examples

Usage example

SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid

┌─────────────────────────────────uuid─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │
└──────────────────────────────────────┘

toUUIDOrZero​

Introduced in: v20.12

Converts an input value to a value of type UUID but returns zero UUID in case of an error. Like toUUID but returns zero UUID (00000000-0000-0000-0000-000000000000) instead of throwing an exception on conversion errors.

Supported arguments:

  • String representations of UUID in standard format (8-4-4-4-12 hexadecimal digits).
  • String representations of UUID without hyphens (32 hexadecimal digits).

Unsupported arguments (return zero UUID):

  • Invalid string formats.
  • Non-string types.

Syntax

toUUIDOrZero(x)

Arguments

  • x — A string representation of a UUID. String

Returned value

Returns a UUID value if successful, otherwise zero UUID (00000000-0000-0000-0000-000000000000). UUID

Examples

Usage example

SELECT
    toUUIDOrZero('550e8400-e29b-41d4-a716-446655440000') AS valid_uuid,
    toUUIDOrZero('invalid-uuid') AS invalid_uuid

┌─valid_uuid───────────────────────────┬─invalid_uuid─────────────────────────┐
│ 550e8400-e29b-41d4-a716-446655440000 │ 00000000-0000-0000-0000-000000000000 │
└──────────────────────────────────────┴──────────────────────────────────────┘

toUnixTimestamp64Micro​

Introduced in: v20.5

Converts a DateTime64 to a Int64 value with fixed microsecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is relative to UTC, not to the timezone of the input value.

Syntax

toUnixTimestamp64Micro(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64

Returned value

Returns a Unix timestamp in microseconds. Int64

Examples

Usage example

WITH toDateTime64('2025-02-13 23:31:31.011123', 6, 'UTC') AS dt64
SELECT toUnixTimestamp64Micro(dt64);

┌─toUnixTimestamp64Micro(dt64)─┐
│               1739489491011123 │
└────────────────────────────────┘

toUnixTimestamp64Milli​

Introduced in: v20.5

Converts a DateTime64 to a Int64 value with fixed millisecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is relative to UTC, not to the timezone of the input value.

Syntax

toUnixTimestamp64Milli(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64

Returned value

Returns a Unix timestamp in milliseconds. Int64

Examples

Usage example

WITH toDateTime64('2025-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Milli(dt64);

┌─toUnixTimestamp64Milli(dt64)─┐
│                1739489491011 │
└──────────────────────────────┘

toUnixTimestamp64Nano​

Introduced in: v20.5

Converts a DateTime64 to a Int64 value with fixed nanosecond precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is relative to UTC, not to the timezone of the input value.

Syntax

toUnixTimestamp64Nano(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64

Returned value

Returns a Unix timestamp in nanoseconds. Int64

Examples

Usage example

WITH toDateTime64('2025-02-13 23:31:31.011123456', 9, 'UTC') AS dt64
SELECT toUnixTimestamp64Nano(dt64);

┌─toUnixTimestamp64Nano(dt64)────┐
│            1739489491011123456 │
└────────────────────────────────┘

toUnixTimestamp64Second​

Introduced in: v24.12

Converts a DateTime64 to a Int64 value with fixed second precision. The input value is scaled up or down appropriately depending on its precision.

Note

The output value is relative to UTC, not to the timezone of the input value.

Syntax

toUnixTimestamp64Second(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64

Returned value

Returns a Unix timestamp in seconds. Int64

Examples

Usage example

WITH toDateTime64('2025-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Second(dt64);

┌─toUnixTimestamp64Second(dt64)─┐
│                    1739489491 │
└───────────────────────────────┘