--- title: Type Conversion Functions reference meta: description: Functions for converting values between different data types. headingMaxLevels: 2 --- # Type conversion functions The following functions are available for type conversion. ## Common issues with data conversion `to` functions and cast behave differently in some cases, for example in case of LowCardinality: cast removes LowCardinality trait `to` functions don't. The same with Nullable, this behaviour isn't compatible with SQL standard, and it can be changed using cast_keep_nullable setting. {% callout type="info" %} 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. {% /callout %} Example: ```sql 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) │ └──────────────────┴─────────────────────┴──────────────────┘ ``` ## toBool Converts an input value to a boolean type. This function will throw an error if the conversion is not possible. ### Syntax ```sql toBool(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string values: `'true'` or `'false'` (case-insensitive). ### Returns A boolean value (`true` or `false`). [Bool](../data-types/boolean). ### Example ```sql SELECT toBool(toUInt8(1)), toBool(toInt8(-1)), toBool(toFloat32(1.01)), toBool('true'), toBool('false'), toBool('FALSE') ``` Result: ```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 an 8-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt8(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int8, overflow or underflow of the result occurs. This isn't considered an error. For example: `SELECT toInt8(128) == -128;`. {% /callout %} ### Returns An 8-bit signed integer. [Int8](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt8(-8), toInt8(-8.8), toInt8('-8') ``` Result: ```result Row 1: ────── toInt8(-8): -8 toInt8(-8.8): -8 toInt8('-8'): -8 ``` ## toInt8OrZero Converts an input value to an 8-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt8OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit signed integer if successful, otherwise `0`. [Int8](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt8OrZero('-8'), toInt8OrZero('abc') ``` Result: ```result Row 1: ────── toInt8OrZero('-8'): -8 toInt8OrZero('abc'): 0 ``` ## toInt8OrNull Converts an input value to an 8-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt8OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit signed integer if successful, otherwise `NULL`. [Int8](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt8OrNull('-8'), toInt8OrNull('abc') ``` Result: ```result Row 1: ────── toInt8OrNull('-8'): -8 toInt8OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt8OrDefault Converts an input value to an 8-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt8OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int8` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int8](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt8OrDefault('-8', CAST('-1', 'Int8')), toInt8OrDefault('abc', CAST('-1', 'Int8')) ``` Result: ```result Row 1: ────── toInt8OrDefault('-8', CAST('-1', 'Int8')): -8 toInt8OrDefault('abc', CAST('-1', 'Int8')): -1 ``` ## toInt16 Converts an input value to a 16-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt16(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int16, overflow or underflow of the result occurs. This isn't considered an error. For example: `SELECT toInt16(32768) == -32768;`. {% /callout %} ### Returns A 16-bit signed integer. [Int16](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt16(-16), toInt16(-16.16), toInt16('-16') ``` Result: ```result Row 1: ────── toInt16(-16): -16 toInt16(-16.16): -16 toInt16('-16'): -16 ``` ## toInt16OrZero Converts an input value to a 16-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt16OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int16, overflow or underflow of the result occurs. This isn't considered as an error. {% /callout %} ### Returns A 16-bit signed integer if successful, otherwise `0`. [Int16](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt16OrZero('-16'), toInt16OrZero('abc') ``` Result: ```result Row 1: ────── toInt16OrZero('-16'): -16 toInt16OrZero('abc'): 0 ``` ## toInt16OrNull Converts an input value to a 16-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt16OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int16, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 16-bit signed integer if successful, otherwise `NULL`. [Int16](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt16OrNull('-16'), toInt16OrNull('abc') ``` Result: ```result Row 1: ────── toInt16OrNull('-16'): -16 toInt16OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt16OrDefault Converts an input value to a 16-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt16OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int16` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int16, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 16-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int16](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt16OrDefault('-16', CAST('-1', 'Int16')), toInt16OrDefault('abc', CAST('-1', 'Int16')) ``` Result: ```result Row 1: ────── toInt16OrDefault('-16', CAST('-1', 'Int16')): -16 toInt16OrDefault('abc', CAST('-1', 'Int16')): -1 ``` ## toInt32 Converts an input value to a 32-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt32(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int32, the result over or under flows. This isn't considered an error. For example: `SELECT toInt32(2147483648) == -2147483648;` {% /callout %} ### Returns A 32-bit signed integer. [Int32](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt32(-32), toInt32(-32.32), toInt32('-32') ``` Result: ```result Row 1: ────── toInt32(-32): -32 toInt32(-32.32): -32 toInt32('-32'): -32 ``` ## toInt32OrZero Converts an input value to a 32-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt32OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit signed integer if successful, otherwise `0`. [Int32](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt32OrZero('-32'), toInt32OrZero('abc') ``` Result: ```result Row 1: ────── toInt32OrZero('-32'): -32 toInt32OrZero('abc'): 0 ``` ## toInt32OrNull Converts an input value to a 32-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt32OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit signed integer if successful, otherwise `NULL`. [Int32](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt32OrNull('-32'), toInt32OrNull('abc') ``` Result: ```result Row 1: ────── toInt32OrNull('-32'): -32 toInt32OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt32OrDefault Converts an input value to a 32-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt32OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int32` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int32](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt32OrDefault('-32', CAST('-1', 'Int32')), toInt32OrDefault('abc', CAST('-1', 'Int32')) ``` Result: ```result Row 1: ────── toInt32OrDefault('-32', CAST('-1', 'Int32')): -32 toInt32OrDefault('abc', CAST('-1', 'Int32')): -1 ``` ## toInt64 Converts an input value to a 64-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt64(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int64, the result over or under flows. This isn't considered an error. For example: `SELECT toInt64(9223372036854775808) == -9223372036854775808;` {% /callout %} ### Returns A 64-bit signed integer. [Int64](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt64(-64), toInt64(-64.64), toInt64('-64') ``` Result: ```result Row 1: ────── toInt64(-64): -64 toInt64(-64.64): -64 toInt64('-64'): -64 ``` ## toInt64OrZero Converts an input value to a 64-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt64OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit signed integer if successful, otherwise `0`. [Int64](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt64OrZero('-64'), toInt64OrZero('abc') ``` Result: ```result Row 1: ────── toInt64OrZero('-64'): -64 toInt64OrZero('abc'): 0 ``` ## toInt64OrNull Converts an input value to a 64-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt64OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit signed integer if successful, otherwise `NULL`. [Int64](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt64OrNull('-64'), toInt64OrNull('abc') ``` Result: ```result Row 1: ────── toInt64OrNull('-64'): -64 toInt64OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt64OrDefault Converts an input value to a 64-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt64OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int64` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int64](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt64OrDefault('-64', CAST('-1', 'Int64')), toInt64OrDefault('abc', CAST('-1', 'Int64')) ``` Result: ```result Row 1: ────── toInt64OrDefault('-64', CAST('-1', 'Int64')): -64 toInt64OrDefault('abc', CAST('-1', 'Int64')): -1 ``` ## toInt128 Converts an input value to a 128-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt128(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int128, the result over or under flows. This isn't considered an error. {% /callout %} ### Returns A 128-bit signed integer. [Int128](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt128(-128), toInt128(-128.8), toInt128('-128') ``` Result: ```result Row 1: ────── toInt128(-128): -128 toInt128(-128.8): -128 toInt128('-128'): -128 ``` ## toInt128OrZero Converts an input value to a 128-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt128OrZero(expr) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit signed integer if successful, otherwise `0`. [Int128](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt128OrZero('-128'), toInt128OrZero('abc') ``` Result: ```result Row 1: ────── toInt128OrZero('-128'): -128 toInt128OrZero('abc'): 0 ``` ## toInt128OrNull Converts an input value to a 128-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt128OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit signed integer if successful, otherwise `NULL`. [Int128](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt128OrNull('-128'), toInt128OrNull('abc') ``` Result: ```result Row 1: ────── toInt128OrNull('-128'): -128 toInt128OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt128OrDefault Converts an input value to a 128-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt128OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int128` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int128](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt128OrDefault('-128', CAST('-1', 'Int128')), toInt128OrDefault('abc', CAST('-1', 'Int128')) ``` Result: ```result Row 1: ────── toInt128OrDefault('-128', CAST('-1', 'Int128')): -128 toInt128OrDefault('abc', CAST('-1', 'Int128')): -1 ``` ## toInt256 Converts an input value to a 256-bit signed integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toInt256(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int256, the result over or under flows. This isn't considered an error. {% /callout %} ### Returns A 256-bit signed integer. [Int256](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt256(-256), toInt256(-256.256), toInt256('-256') ``` Result: ```result Row 1: ────── toInt256(-256): -256 toInt256(-256.256): -256 toInt256('-256'): -256 ``` ## toInt256OrZero Converts an input value to a 256-bit signed integer, returning `0` if the conversion fails. ### Syntax ```sql toInt256OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit signed integer if successful, otherwise `0`. [Int256](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt256OrZero('-256'), toInt256OrZero('abc') ``` Result: ```result Row 1: ────── toInt256OrZero('-256'): -256 toInt256OrZero('abc'): 0 ``` ## toInt256OrNull Converts an input value to a 256-bit signed integer, returning `NULL` if the conversion fails. ### Syntax ```sql toInt256OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of Int256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit signed integer if successful, otherwise `NULL`. [Int256](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toInt256OrNull('-256'), toInt256OrNull('abc') ``` Result: ```result Row 1: ────── toInt256OrNull('-256'): -256 toInt256OrNull('abc'): ᴺᵁᴸᴸ ``` ## toInt256OrDefault Converts an input value to a 256-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toInt256OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `Int256` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of Int256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit signed integer if successful, otherwise the `default` value (if provided) or `0`. [Int256](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toInt256OrDefault('-256', CAST('-1', 'Int256')), toInt256OrDefault('abc', CAST('-1', 'Int256')) ``` Result: ```result Row 1: ────── toInt256OrDefault('-256', CAST('-1', 'Int256')): -256 toInt256OrDefault('abc', CAST('-1', 'Int256')): -1 ``` ## toUInt8 Converts an input value to an 8-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt8(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt8, overflow or underflow of the result occurs. This isn't considered an error. For example: `SELECT toUInt8(256) == 0;`. {% /callout %} ### Returns An 8-bit unsigned integer. [UInt8](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt8(8), toUInt8(8.8), toUInt8('8') ``` Result: ```result Row 1: ────── toUInt8(8): 8 toUInt8(8.8): 8 toUInt8('8'): 8 ``` ## toUInt8OrZero Converts an input value to an 8-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt8OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit unsigned integer if successful, otherwise `0`. [UInt8](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt8OrZero('-8'), toUInt8OrZero('abc') ``` Result: ```result Row 1: ────── toUInt8OrZero('-8'): 0 toUInt8OrZero('abc'): 0 ``` ## toUInt8OrNull Converts an input value to an 8-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt8OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit unsigned integer if successful, otherwise `NULL`. [UInt8](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt8OrNull('8'), toUInt8OrNull('abc') ``` Result: ```result Row 1: ────── toUInt8OrNull('8'): 8 toUInt8OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt8OrDefault Converts an input value to an 8-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt8OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt8` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt8, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns An 8-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt8](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt8OrDefault('8', CAST('0', 'UInt8')), toUInt8OrDefault('abc', CAST('0', 'UInt8')) ``` Result: ```result Row 1: ────── toUInt8OrDefault('8', CAST('0', 'UInt8')): 8 toUInt8OrDefault('abc', CAST('0', 'UInt8')): 0 ``` ## toUInt16 Converts an input value to a 16-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt16(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt16, overflow or underflow of the result occurs. This isn't considered an error. For example: `SELECT toUInt16(65536) == 0;`. {% /callout %} ### Returns A 16-bit unsigned integer. [UInt16](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt16(16), toUInt16(16.16), toUInt16('16') ``` Result: ```result Row 1: ────── toUInt16(16): 16 toUInt16(16.16): 16 toUInt16('16'): 16 ``` ## toUInt16OrZero Converts an input value to a 16-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt16OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt16, overflow or underflow of the result occurs. This isn't considered as an error. {% /callout %} ### Returns A 16-bit unsigned integer if successful, otherwise `0`. [UInt16](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt16OrZero('16'), toUInt16OrZero('abc') ``` Result: ```result Row 1: ────── toUInt16OrZero('16'): 16 toUInt16OrZero('abc'): 0 ``` ## toUInt16OrNull Converts an input value to a 16-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt16OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt16, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 16-bit unsigned integer if successful, otherwise `NULL`. [UInt16](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt16OrNull('16'), toUInt16OrNull('abc') ``` Result: ```result Row 1: ────── toUInt16OrNull('16'): 16 toUInt16OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt16OrDefault Converts an input value to a 16-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt16OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt16` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt16, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 16-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt16](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt16OrDefault('16', CAST('0', 'UInt16')), toUInt16OrDefault('abc', CAST('0', 'UInt16')) ``` Result: ```result Row 1: ────── toUInt16OrDefault('16', CAST('0', 'UInt16')): 16 toUInt16OrDefault('abc', CAST('0', 'UInt16')): 0 ``` ## toUInt32 Converts an input value to a 32-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt32(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt32, the result over or under flows. This isn't considered an error. For example: `SELECT toUInt32(4294967296) == 0;` {% /callout %} ### Returns A 32-bit unsigned integer. [UInt32](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt32(32), toUInt32(32.32), toUInt32('32') ``` Result: ```result Row 1: ────── toUInt32(32): 32 toUInt32(32.32): 32 toUInt32('32'): 32 ``` ## toUInt32OrZero Converts an input value to a 32-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt32OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit unsigned integer if successful, otherwise `0`. [UInt32](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt32OrZero('32'), toUInt32OrZero('abc') ``` Result: ```result Row 1: ────── toUInt32OrZero('32'): 32 toUInt32OrZero('abc'): 0 ``` ## toUInt32OrNull Converts an input value to a 32-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt32OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit unsigned integer if successful, otherwise `NULL`. [UInt32](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt32OrNull('32'), toUInt32OrNull('abc') ``` Result: ```result Row 1: ────── toUInt32OrNull('32'): 32 toUInt32OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt32OrDefault Converts an input value to a 32-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt32OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt32` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt32, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 32-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt32](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt32OrDefault('32', CAST('0', 'UInt32')), toUInt32OrDefault('abc', CAST('0', 'UInt32')) ``` Result: ```result Row 1: ────── toUInt32OrDefault('32', CAST('0', 'UInt32')): 32 toUInt32OrDefault('abc', CAST('0', 'UInt32')): 0 ``` ## toUInt64 Converts an input value to a 64-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt64(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt64, the result over or under flows. This isn't considered an error. For example: `SELECT toUInt64(18446744073709551616) == 0;` {% /callout %} ### Returns A 64-bit unsigned integer. [UInt64](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt64(64), toUInt64(64.64), toUInt64('64') ``` Result: ```result Row 1: ────── toUInt64(64): 64 toUInt64(64.64): 64 toUInt64('64'): 64 ``` ## toUInt64OrZero Converts an input value to a 64-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt64OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit unsigned integer if successful, otherwise `0`. [UInt64](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt64OrZero('64'), toUInt64OrZero('abc') ``` Result: ```result Row 1: ────── toUInt64OrZero('64'): 64 toUInt64OrZero('abc'): 0 ``` ## toUInt64OrNull Converts an input value to a 64-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt64OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit unsigned integer if successful, otherwise `NULL`. [UInt64](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt64OrNull('64'), toUInt64OrNull('abc') ``` Result: ```result Row 1: ────── toUInt64OrNull('64'): 64 toUInt64OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt64OrDefault Converts an input value to a 64-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt64OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt64` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt64, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 64-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt64](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt64OrDefault('64', CAST('0', 'UInt64')), toUInt64OrDefault('abc', CAST('0', 'UInt64')) ``` Result: ```result Row 1: ────── toUInt64OrDefault('64', CAST('0', 'UInt64')): 64 toUInt64OrDefault('abc', CAST('0', 'UInt64')): 0 ``` ## toUInt128 Converts an input value to a 128-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt128(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt128, the result over or under flows. This isn't considered an error. {% /callout %} ### Returns A 128-bit unsigned integer. [UInt128](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt128(128), toUInt128(128.8), toUInt128('128') ``` Result: ```result Row 1: ────── toUInt128(128): 128 toUInt128(128.8): 128 toUInt128('128'): 128 ``` ## toUInt128OrZero Converts an input value to a 128-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt128OrZero(expr) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit unsigned integer if successful, otherwise `0`. [UInt128](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt128OrZero('128'), toUInt128OrZero('abc') ``` Result: ```result Row 1: ────── toUInt128OrZero('128'): 128 toUInt128OrZero('abc'): 0 ``` ## toUInt128OrNull Converts an input value to a 128-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt128OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit unsigned integer if successful, otherwise `NULL`. [UInt128](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt128OrNull('128'), toUInt128OrNull('abc') ``` Result: ```result Row 1: ────── toUInt128OrNull('128'): 128 toUInt128OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt128OrDefault Converts an input value to a 128-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt128OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt128` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt128, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 128-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt128](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt128OrDefault('128', CAST('0', 'UInt128')), toUInt128OrDefault('abc', CAST('0', 'UInt128')) ``` Result: ```result Row 1: ────── toUInt128OrDefault('128', CAST('0', 'UInt128')): 128 toUInt128OrDefault('abc', CAST('0', 'UInt128')): 0 ``` ## toUInt256 Converts an input value to a 256-bit unsigned integer. This function will throw an error if the conversion is not possible. ### Syntax ```sql toUInt256(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations: Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt256, the result over or under flows. This isn't considered an error. {% /callout %} ### Returns A 256-bit unsigned integer. [UInt256](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt256(256), toUInt256(256.256), toUInt256('256') ``` Result: ```result Row 1: ────── toUInt256(256): 256 toUInt256(256.256): 256 toUInt256('256'): 256 ``` ## toUInt256OrZero Converts an input value to a 256-bit unsigned integer, returning `0` if the conversion fails. ### Syntax ```sql toUInt256OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit unsigned integer if successful, otherwise `0`. [UInt256](../data-types/int-uint). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt256OrZero('256'), toUInt256OrZero('abc') ``` Result: ```result Row 1: ────── toUInt256OrZero('256'): 256 toUInt256OrZero('abc'): 0 ``` ## toUInt256OrNull Converts an input value to a 256-bit unsigned integer, returning `NULL` if the conversion fails. ### Syntax ```sql toUInt256OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers. - Unsupported string representations (return `NULL`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. {% callout type="info" %} If the input value can't be represented within the bounds of UInt256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit unsigned integer if successful, otherwise `NULL`. [UInt256](../data-types/int-uint) / [Nullable](../data-types/nullable). {% callout type="info" %} The function uses rounding towards zero, meaning it truncates fractional digits of numbers. {% /callout %} ### Example ```sql SELECT toUInt256OrNull('256'), toUInt256OrNull('abc') ``` Result: ```result Row 1: ────── toUInt256OrNull('256'): 256 toUInt256OrNull('abc'): ᴺᵁᴸᴸ ``` ## toUInt256OrDefault Converts an input value to a 256-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toUInt256OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers. - Unsupported string representations (return `default` or `0`): Floating-point numbers (e.g., `'1.23'`), `NaN`, `Inf`, binary, or hexadecimal values. - `default` (optional): The `UInt256` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} If the input value can't be represented within the bounds of UInt256, overflow or underflow of the result occurs. This isn't considered an error. {% /callout %} ### Returns A 256-bit unsigned integer if successful, otherwise the `default` value (if provided) or `0`. [UInt256](../data-types/int-uint). {% callout type="info" %} - 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. {% /callout %} ### Example ```sql SELECT toUInt256OrDefault('-256', CAST('0', 'UInt256')), toUInt256OrDefault('abc', CAST('0', 'UInt256')) ``` Result: ```result Row 1: ────── toUInt256OrDefault('-256', CAST('0', 'UInt256')): 0 toUInt256OrDefault('abc', CAST('0', 'UInt256')): 0 ``` ## toFloat32 Converts an input value to a 32-bit floating-point number. This function will throw an error if the conversion is not possible. ### Syntax ```sql toFloat32(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf` (case-insensitive). - Unsupported string representations: Binary or hexadecimal values. ### Returns A 32-bit floating-point number. [Float32](../data-types/float). ### Example ```sql SELECT toFloat32(42.7), toFloat32('42.7'), toFloat32('NaN') ``` Result: ```result Row 1: ────── toFloat32(42.7): 42.7 toFloat32('42.7'): 42.7 toFloat32('NaN'): nan ``` ## toFloat32OrZero Converts an input value to a 32-bit floating-point number, returning `0` if the conversion fails. ### Syntax ```sql toFloat32OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf`. - Unsupported string representations (return `0`): Binary or hexadecimal values. ### Returns A 32-bit floating-point number if successful, otherwise `0`. [Float32](../data-types/float). ### Example ```sql SELECT toFloat32OrZero('42.7'), toFloat32OrZero('abc') ``` Result: ```result Row 1: ────── toFloat32OrZero('42.7'): 42.7 toFloat32OrZero('abc'): 0 ``` ## toFloat32OrNull Converts an input value to a 32-bit floating-point number, returning `NULL` if the conversion fails. ### Syntax ```sql toFloat32OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf`. - Unsupported string representations (return `NULL`): Binary or hexadecimal values. ### Returns A 32-bit floating-point number if successful, otherwise `NULL`. [Float32](../data-types/float) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toFloat32OrNull('42.7'), toFloat32OrNull('abc') ``` Result: ```result Row 1: ────── toFloat32OrNull('42.7'): 42.7 toFloat32OrNull('abc'): ᴺᵁᴸᴸ ``` ## toFloat32OrDefault Converts an input value to a 32-bit floating-point number, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toFloat32OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf` (case-insensitive). - Unsupported string representations (return `default` or `0`): Binary or hexadecimal values. - `default` (optional): The `Float32` value to return if the conversion of `expr` is unsuccessful. ### Returns A 32-bit floating-point number if successful, otherwise the `default` value (if provided) or `0`. [Float32](../data-types/float). ### Example ```sql SELECT toFloat32OrDefault('8', CAST('0', 'Float32')), toFloat32OrDefault('abc', CAST('0', 'Float32')) ``` Result: ```result Row 1: ────── toFloat32OrDefault('8', CAST('0', 'Float32')): 8 toFloat32OrDefault('abc', CAST('0', 'Float32')): 0 ``` ## toFloat64 Converts an input value to a 64-bit floating-point number. This function will throw an error if the conversion is not possible. ### Syntax ```sql toFloat64(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf` (case-insensitive). - Unsupported string representations: Binary or hexadecimal values. ### Returns A 64-bit floating-point number. [Float64](../data-types/float). ### Example ```sql SELECT toFloat64(42.7), toFloat64('42.7'), toFloat64('NaN') ``` Result: ```result Row 1: ────── toFloat64(42.7): 42.7 toFloat64('42.7'): 42.7 toFloat64('NaN'): nan ``` ## toFloat64OrZero Converts an input value to a 64-bit floating-point number, returning `0` if the conversion fails. ### Syntax ```sql toFloat64OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf`. - Unsupported string representations (return `0`): Binary or hexadecimal values. ### Returns A 64-bit floating-point number if successful, otherwise `0`. [Float64](../data-types/float). ### Example ```sql SELECT toFloat64OrZero('42.7'), toFloat64OrZero('abc') ``` Result: ```result Row 1: ────── toFloat64OrZero('42.7'): 42.7 toFloat64OrZero('abc'): 0 ``` ## toFloat64OrNull Converts an input value to a 64-bit floating-point number, returning `NULL` if the conversion fails. ### Syntax ```sql toFloat64OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf`. - Unsupported string representations (return `NULL`): Binary or hexadecimal values. ### Returns A 64-bit floating-point number if successful, otherwise `NULL`. [Float64](../data-types/float) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toFloat64OrNull('42.7'), toFloat64OrNull('abc') ``` Result: ```result Row 1: ────── toFloat64OrNull('42.7'): 42.7 toFloat64OrNull('abc'): ᴺᵁᴸᴸ ``` ## toFloat64OrDefault Converts an input value to a 64-bit floating-point number, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toFloat64OrDefault(expr[, default]) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf` (case-insensitive). - Unsupported string representations (return `default` or `0`): Binary or hexadecimal values. - `default` (optional): The `Float64` value to return if the conversion of `expr` is unsuccessful. ### Returns A 64-bit floating-point number if successful, otherwise the `default` value (if provided) or `0`. [Float64](../data-types/float). ### Example ```sql SELECT toFloat64OrDefault('8', CAST('0', 'Float64')), toFloat64OrDefault('abc', CAST('0', 'Float64')) ``` Result: ```result Row 1: ────── toFloat64OrDefault('8', CAST('0', 'Float64')): 8 toFloat64OrDefault('abc', CAST('0', 'Float64')): 0 ``` ## toBFloat16 Converts an input value to a 16-bit brain floating-point number. This function will throw an error if the conversion is not possible. ### Syntax ```sql toBFloat16(expr) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers, `NaN`, `Inf` (case-insensitive). ### Returns A 16-bit brain floating-point value. [BFloat16](../data-types/float#bfloat16). ### Example ```sql SELECT toBFloat16(toFloat32(42.7)) ``` Result: ```result ┌─toBFloat16(toFloat32(42.7))─┐ │ 42.5 │ └────────────────────────────┘ ``` ## toBFloat16OrZero Converts a string input value to a 16-bit brain floating-point number, returning `0` if the conversion fails. ### Syntax ```sql toBFloat16OrZero(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Numeric values (integers, floating-point numbers, `NaN`, `Inf`). - Unsupported string representations (return `0`): Binary or hexadecimal values. ### Returns A 16-bit brain floating-point value if successful, otherwise `0`. [BFloat16](../data-types/float#bfloat16). ### Example ```sql SELECT toBFloat16OrZero('0x5E'), toBFloat16OrZero('12.3'), toBFloat16OrZero('12.3456789') ``` Result: ```result ┌─toBFloat16OrZero('0x5E')─┬─toBFloat16OrZero('12.3')─┬─toBFloat16OrZero('12.3456789')─┐ │ 0 │ 12.25 │ 12.3125 │ └──────────────────────────┴──────────────────────────┴────────────────────────────────┘ ``` ## toBFloat16OrNull Converts a string input value to a 16-bit brain floating-point number, returning `NULL` if the conversion fails. ### Syntax ```sql toBFloat16OrNull(x) ``` ### Arguments - `x`: A string representation of a number. - Supported string representations: Numeric values (integers, floating-point numbers, `NaN`, `Inf`). - Unsupported string representations (return `NULL`): Binary or hexadecimal values. ### Returns A 16-bit brain floating-point value if successful, otherwise `NULL`. [BFloat16](../data-types/float#bfloat16) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toBFloat16OrNull('0x5E'), toBFloat16OrNull('12.3'), toBFloat16OrNull('12.3456789') ``` Result: ```result ┌─toBFloat16OrNull('0x5E')─┬─toBFloat16OrNull('12.3')─┬─toBFloat16OrNull('12.3456789')─┐ │ ᴺᵁᴸᴸ │ 12.25 │ 12.3125 │ └──────────────────────────┴──────────────────────────┴────────────────────────────────┘ ``` ## toDate Converts an input value to a `Date` data type. ### Syntax ```sql toDate(expr[, time_zone]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [Int](../data-types/int-uint), [Date](../data-types/date), or [DateTime](../data-types/datetime) / [DateTime64](../data-types/datetime64). - If `expr` is a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64), it extracts only the date component. - If `expr` is a [String](../data-types/string), it parses the string as a date or datetime and extracts the date component. - If `expr` is a number greater than or equal to 65536, it's interpreted as a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) and converted to a date. - If `expr` is a number less than 65536, it's interpreted as the number of days since 1970-01-01. - `time_zone` (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. [String](../data-types/string). ### Returns A date value. [Date](../data-types/date). ### Example ```sql SELECT toDate('2022-12-30 01:02:03') AS date_from_datetime_string, toDate(1672404714, 'Pacific/Apia') AS date_from_unix_timestamp_with_timezone, toDate(12345) AS date_from_days_since_epoch ``` Result: ```result ┌─date_from_datetime_string─┬─date_from_unix_timestamp_with_timezone─┬─date_from_days_since_epoch─┐ │ 2022-12-30 │ 2022-12-31 │ 2003-10-20 │ └───────────────────────────┴────────────────────────────────────────┴────────────────────────────┘ ``` ## toDateOrZero Converts a string to a `Date` data type, returning the minimum `Date` value (`1970-01-01`) if the conversion fails. ### Syntax ```sql toDateOrZero(str) ``` ### Arguments - `str`: The string to convert to a date. [String](../data-types/string). ### Returns A date value if successful, otherwise `1970-01-01`. [Date](../data-types/date). ### Example ```sql SELECT toDateOrZero('2022-12-30'), toDateOrZero('invalid_date') ``` Result: ```result ┌─toDateOrZero('2022-12-30')─┬─toDateOrZero('invalid_date')─┐ │ 2022-12-30 │ 1970-01-01 │ └────────────────────────────┴──────────────────────────────┘ ``` ## toDateOrNull Converts a string to a `Date` data type, returning `NULL` if the conversion fails. ### Syntax ```sql toDateOrNull(str) ``` ### Arguments - `str`: The string to convert to a date. [String](../data-types/string). ### Returns A date value if successful, otherwise `NULL`. [Date](../data-types/date) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toDateOrNull('2022-12-30'), toDateOrNull('invalid_date') ``` Result: ```result ┌─toDateOrNull('2022-12-30')─┬─toDateOrNull('invalid_date')─┐ │ 2022-12-30 │ ᴺᵁᴸᴸ │ └────────────────────────────┴──────────────────────────────┘ ``` ## toDateOrDefault Converts an input value to a `Date` data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum `Date` value (`1970-01-01`). ### Syntax ```sql toDateOrDefault(expr[, default_value]) ``` ### Arguments - `expr`: The value to convert to a date. Can be a [String](../data-types/string), [Int](../data-types/int-uint), [Date](../data-types/date), or [DateTime](../data-types/datetime) / [DateTime64](../data-types/datetime64). - `default_value` (optional): The `Date` value to return if the conversion of `expr` is unsuccessful. ### Returns A date value if successful, otherwise the `default_value` (if provided) or `1970-01-01`. [Date](../data-types/date). ### Example ```sql SELECT toDateOrDefault('2022-12-30'), toDateOrDefault('invalid_date', CAST('2023-01-01', 'Date')) ``` Result: ```result ┌─toDateOrDefault('2022-12-30')─┬─toDateOrDefault('invalid_date', CAST('2023-01-01', 'Date'))─┐ │ 2022-12-30 │ 2023-01-01 │ └───────────────────────────────┴───────────────────────────────────────────────────────────┘ ``` ## toDateTime Converts an input value to a `DateTime` data type. ### Syntax ```sql toDateTime(expr[, time_zone]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [Int](../data-types/int-uint), [Date](../data-types/date), or [DateTime](../data-types/datetime). - If `expr` is a number, it's interpreted as a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC). - If `expr` is a string, it's parsed as a datetime string. Parsing of short numeric strings (up to 4 digits) is disabled to avoid ambiguity with Unix timestamps. - `time_zone` (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. [String](../data-types/string). ### Returns A datetime value. [DateTime](../data-types/datetime). ### Example ```sql SELECT toDateTime('2022-12-30 13:44:17'), toDateTime(1685457500, 'UTC') ``` Result: ```result ┌─toDateTime('2022-12-30 13:44:17')─┬─toDateTime(1685457500, 'UTC')─┐ │ 2022-12-30 13:44:17 │ 2023-05-30 14:38:20 │ └───────────────────────────────────┴───────────────────────────────┘ ``` ## toDateTimeOrZero Converts a string to a `DateTime` data type, returning the minimum `DateTime` value (`1970-01-01 00:00:00`) if the conversion fails. ### Syntax ```sql toDateTimeOrZero(str) ``` ### Arguments - `str`: The string to convert to a datetime. [String](../data-types/string). ### Returns A datetime value if successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('invalid_datetime') ``` Result: ```result ┌─toDateTimeOrZero('2022-12-30 13:44:17')─┬─toDateTimeOrZero('invalid_datetime')─┐ │ 2022-12-30 13:44:17 │ 1970-01-01 00:00:00 │ └─────────────────────────────────────────┴───────────────────────────────────────┘ ``` ## toDateTimeOrNull Converts a string to a `DateTime` data type, returning `NULL` if the conversion fails. ### Syntax ```sql toDateTimeOrNull(str) ``` ### Arguments - `str`: The string to convert to a datetime. [String](../data-types/string). ### Returns A datetime value if successful, otherwise `NULL`. [DateTime](../data-types/datetime) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('invalid_datetime') ``` Result: ```result ┌─toDateTimeOrNull('2022-12-30 13:44:17')─┬─toDateTimeOrNull('invalid_datetime')─┐ │ 2022-12-30 13:44:17 │ ᴺᵁᴸᴸ │ └─────────────────────────────────────────┴───────────────────────────────────────┘ ``` ## toDateTimeOrDefault Converts an input value to a `DateTime` data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum `DateTime` value (`1970-01-01 00:00:00`). ### Syntax ```sql toDateTimeOrDefault(expr[, time_zone [, default_value]]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [Int](../data-types/int-uint), [Date](../data-types/date), or [DateTime](../data-types/datetime). - `time_zone` (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. [String](../data-types/string). - `default_value` (optional): The `DateTime` value to return if the conversion of `expr` is unsuccessful. ### Returns A datetime value if successful, otherwise the `default_value` (if provided) or `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT toDateTimeOrDefault('2022-12-30 13:44:17'), toDateTimeOrDefault('invalid_datetime', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')')) ``` Result: ```result ┌─toDateTimeOrDefault('2022-12-30 13:44:17')─┬─toDateTimeOrDefault('invalid_datetime', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))─┐ │ 2022-12-30 13:44:17 │ 2023-01-01 00:00:00 │ └────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘ ``` ## toDate32 Converts an input value to a `Date32` data type. This function will throw an error if the conversion is not possible. ### Syntax ```sql toDate32(expr) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), or [Date](../data-types/date). - If `expr` is a [Date](../data-types/date), its range is considered. - If `expr` is a [String](../data-types/string), it parses the string as a date. ### Returns A calendar date. [Date32](../data-types/date32). ### Example ```sql SELECT toDate32('1955-01-01') AS valid_date, toDate32('1899-01-01') AS out_of_range_date, toDate32(toDate('1899-01-01')) AS date_from_date_type_out_of_range ``` Result: ```result ┌─valid_date─┬─out_of_range_date─┬─date_from_date_type_out_of_range─┐ │ 1955-01-01 │ 1900-01-01 │ 1970-01-01 │ └────────────┴───────────────────┴──────────────────────────────────┘ ``` ## toDate32OrZero Converts an input value to a `Date32` data type, returning the minimum `Date32` value (`1900-01-01`) if the conversion fails. ### Syntax ```sql toDate32OrZero(expr) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), or [Date](../data-types/date). ### Returns A calendar date if successful, otherwise `1900-01-01`. [Date32](../data-types/date32). ### Example ```sql SELECT toDate32OrZero('1899-01-01'), toDate32OrZero('invalid_date') ``` Result: ```result ┌─toDate32OrZero('1899-01-01')─┬─toDate32OrZero('invalid_date')─┐ │ 1900-01-01 │ 1900-01-01 │ └──────────────────────────────┴────────────────────────────────┘ ``` ## toDate32OrNull Converts an input value to a `Date32` data type, returning `NULL` if the conversion fails. ### Syntax ```sql toDate32OrNull(expr) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), or [Date](../data-types/date). ### Returns A calendar date if successful, otherwise `NULL`. [Date32](../data-types/date32) / [Nullable](../data-types/nullable). ### Example ```sql SELECT toDate32OrNull('1955-01-01'), toDate32OrNull('invalid_date') ``` Result: ```result ┌─toDate32OrNull('1955-01-01')─┬─toDate32OrNull('invalid_date')─┐ │ 1955-01-01 │ ᴺᵁᴸᴸ │ └──────────────────────────────┴────────────────────────────────┘ ``` ## toDate32OrDefault Converts an input value to a `Date32` data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum `Date32` value (`1900-01-01`). ### Syntax ```sql toDate32OrDefault(expr[, default_value]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), or [Date](../data-types/date). - `default_value` (optional): The `Date32` value to return if the conversion of `expr` is unsuccessful. ### Returns A calendar date if successful, otherwise the `default_value` (if provided) or `1900-01-01`. [Date32](../data-types/date32). ### Example ```sql SELECT toDate32OrDefault('1930-01-01', toDate32('2020-01-01')), toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01')) ``` Result: ```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 `DateTime64` data type with specified precision. This function will throw an error if the conversion is not possible. ### Syntax ```sql toDateTime64(expr, scale, [timezone]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), [Float](../data-types/float), or [DateTime](../data-types/datetime). - `scale`: An integer from 0 to 9 representing the number of decimal places for sub-second precision. [UInt8](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for interpreting the datetime. [String](../data-types/string). ### Returns A calendar date and time with sub-second precision. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT toDateTime64('1955-01-01 00:00:00.000', 3) AS datetime_from_string, toDateTime64(1546300800.000, 3) AS datetime_from_float_unix_timestamp, toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS datetime_with_timezone ``` Result: ```result ┌─datetime_from_string─┬─datetime_from_float_unix_timestamp─┬─datetime_with_timezone─┐ │ 1955-01-01 00:00:00.000 │ 2019-01-01 00:00:00.000 │ 2019-01-01 00:00:00.000 │ └───────────────────────┴──────────────────────────────────┴────────────────────────┘ ``` ## toDateTime64OrZero Converts an input value to a `DateTime64` data type, returning the minimum `DateTime64` value (`1970-01-01 01:00:00.000`) if the conversion fails. ### Syntax ```sql toDateTime64OrZero(expr, scale, [timezone]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), [Float](../data-types/float), or [DateTime](../data-types/datetime). - `scale`: An integer from 0 to 9 representing the number of decimal places for sub-second precision. [UInt8](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for interpreting the datetime. [String](../data-types/string). ### Returns A calendar date and time with sub-second precision if successful, otherwise `1970-01-01 01:00:00.000`. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT toDateTime64OrZero('2008-10-12 00:00:00 00:30:30', 3) AS invalid_arg ``` Result: ```result ┌─────────────invalid_arg─┐ │ 1970-01-01 01:00:00.000 │ └─────────────────────────┘ ``` ## toDateTime64OrNull Converts an input value to a `DateTime64` data type, returning `NULL` if the conversion fails. ### Syntax ```sql toDateTime64OrNull(expr, scale, [timezone]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), [Float](../data-types/float), or [DateTime](../data-types/datetime). - `scale`: An integer from 0 to 9 representing the number of decimal places for sub-second precision. [UInt8](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for interpreting the datetime. [String](../data-types/string). ### Returns A calendar date and time with sub-second precision if successful, otherwise `NULL`. [DateTime64](../data-types/datetime64) / [Nullable](../data-types/nullable). ### Example ```sql 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: ```result ┌───────────────valid_arg─┬─invalid_arg─┐ │ 1976-10-18 00:00:00.300 │ ᴺᵁᴸᴸ │ └─────────────────────────┴─────────────┘ ``` ## toDateTime64OrDefault Converts an input value to a `DateTime64` data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum `DateTime64` value (`1970-01-01 01:00:00.000`). ### Syntax ```sql toDateTime64OrDefault(expr, scale, [timezone, default_value]) ``` ### Arguments - `expr`: The value to convert. Can be a [String](../data-types/string), [UInt32](../data-types/int-uint), [Float](../data-types/float), or [DateTime](../data-types/datetime). - `scale`: An integer from 0 to 9 representing the number of decimal places for sub-second precision. [UInt8](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for interpreting the datetime. [String](../data-types/string). - `default_value` (optional): The `DateTime64` value to return if the conversion of `expr` is unsuccessful. ### Returns A calendar date and time with sub-second precision if successful, otherwise the `default_value` (if provided) or `1970-01-01 01:00:00.000`. [DateTime64](../data-types/datetime64). ### Example ```sql 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: ```result ┌─────────────invalid_arg─┬─invalid_arg_with_default─┐ │ 1970-01-01 01:00:00.000 │ 2000-12-31 23:00:00.000 │ └─────────────────────────┴──────────────────────────┘ ``` ## toDecimal32 Converts an input value to a `Decimal32` data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows. ### Syntax ```sql toDecimal32(expr, S) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations: `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 9 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal32` value with the specified scale. [Decimal32(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal32(2, 1) AS a, toDecimal32(4.2, 2) AS b, toDecimal32('4.2', 3) AS c ``` Result: ```result ┌─a─┬─b───┬─c───┐ │ 2 │ 4.2 │ 4.2 │ └───┴─────┴─────┘ ``` ## toDecimal32OrZero Converts an input value to a `Decimal32` data type with a specified scale, returning `0` if the conversion fails. ### Syntax ```sql toDecimal32OrZero(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 9 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal32` value if successful, otherwise `0` with `S` decimal places. [Decimal32(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal32OrZero(toString(-1.111), 5) AS a, toDecimal32OrZero(toString('Inf'), 5) as b ``` Result: ```result ┌───────a─┬─b─┐ │ -1.11100 │ 0 │ └─────────┴───┘ ``` ## toDecimal32OrNull Converts an input value to a `Decimal32` data type with a specified scale, returning `NULL` if the conversion fails. ### Syntax ```sql toDecimal32OrNull(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `NULL`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 9 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal32` value if successful, otherwise `NULL`. [Nullable](../data-types/nullable)([Decimal32(S)](../data-types/decimal)). ### Example ```sql SELECT toDecimal32OrNull(toString(-1.111), 5) AS a, toDecimal32OrNull(toString('Inf'), 5) as b ``` Result: ```result ┌───────a─┬─b─┐ │ -1.11100 │ ᴺᵁᴸᴸ │ └─────────┴───┘ ``` ## toDecimal32OrDefault Converts an input value to a `Decimal32` data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toDecimal32OrDefault(expr, S[, default]) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `default` or `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 9 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). - `default` (optional): The `Decimal32(S)` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal32` value if successful, otherwise the `default` value (if provided) or `0`. [Decimal32(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal32OrDefault(toString(0.0001), 5) AS a, toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)')) AS b ``` Result: ```result ┌────────a─┬─b─┐ │ 0.00010 │ -1 │ └─────────┴───┘ ``` ## toDecimal64 Converts an input value to a `Decimal64` data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows. ### Syntax ```sql toDecimal64(expr, S) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations: `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 18 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal64` value with the specified scale. [Decimal64(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal64(2, 1) AS a, toDecimal64(4.2, 2) AS b, toDecimal64('4.2', 3) AS c ``` Result: ```result ┌─a─┬─b───┬─c───┐ │ 2 │ 4.2 │ 4.2 │ └───┴─────┴─────┘ ``` ## toDecimal64OrZero Converts an input value to a `Decimal64` data type with a specified scale, returning `0` if the conversion fails. ### Syntax ```sql toDecimal64OrZero(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 18 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal64` value if successful, otherwise `0` with `S` decimal places. [Decimal64(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal64OrZero(toString(0.0001), 18) AS a, toDecimal64OrZero(toString('Inf'), 18) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.000100000000000000 │ 0 │ └────────────┴───┘ ``` ## toDecimal64OrNull Converts an input value to a `Decimal64` data type with a specified scale, returning `NULL` if the conversion fails. ### Syntax ```sql toDecimal64OrNull(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `NULL`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 18 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal64` value if successful, otherwise `NULL`. [Nullable](../data-types/nullable)([Decimal64(S)](../data-types/decimal)). ### Example ```sql SELECT toDecimal64OrNull(toString(0.0001), 18) AS a, toDecimal64OrNull(toString('Inf'), 18) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.000100000000000000 │ ᴺᵁᴸᴸ │ └────────────┴───┘ ``` ## toDecimal64OrDefault Converts an input value to a `Decimal64` data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toDecimal64OrDefault(expr, S[, default]) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `default` or `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 18 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). - `default` (optional): The `Decimal64(S)` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal64` value if successful, otherwise the `default` value (if provided) or `0`. [Decimal64(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal64OrDefault(toString(0.0001), 18) AS a, toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)')) AS b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.000100000000000000 │ -1 │ └────────────┴───┘ ``` ## toDecimal128 Converts an input value to a `Decimal128` data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows. ### Syntax ```sql toDecimal128(expr, S) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations: `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 38 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal128` value with the specified scale. [Decimal128(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal128(99, 1) AS a, toDecimal128(99.67, 2) AS b, toDecimal128('99.67', 3) AS c ``` Result: ```result ┌─a─┬─b─────┬─c─────┐ │ 99 │ 99.67 │ 99.67 │ └───┴───────┴───────┘ ``` ## toDecimal128OrZero Converts an input value to a `Decimal128` data type with a specified scale, returning `0` if the conversion fails. ### Syntax ```sql toDecimal128OrZero(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 38 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal128` value if successful, otherwise `0` with `S` decimal places. [Decimal128(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal128OrZero(toString(0.0001), 38) AS a, toDecimal128OrZero(toString('Inf'), 38) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.0001000000000000000000000000000000000000 │ 0 │ └────────────┴───┘ ``` ## toDecimal128OrNull Converts an input value to a `Decimal128` data type with a specified scale, returning `NULL` if the conversion fails. ### Syntax ```sql toDecimal128OrNull(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `NULL`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 38 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal128` value if successful, otherwise `NULL`. [Nullable](../data-types/nullable)([Decimal128(S)](../data-types/decimal)). ### Example ```sql SELECT toDecimal128OrNull(toString(1/42), 38) AS a, toDecimal128OrNull(toString('Inf'), 38) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.02380952380952380800000000000000000000 │ ᴺᵁᴸᴸ │ └────────────┴───┘ ``` ## toDecimal128OrDefault Converts an input value to a `Decimal128` data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toDecimal128OrDefault(expr, S[, default]) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `default` or `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 38 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). - `default` (optional): The `Decimal128(S)` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal128` value if successful, otherwise the `default` value (if provided) or `0`. [Decimal128(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal128OrDefault(toString(1/42), 18) AS a, toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)')) AS b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.023809523809523808 │ -1 │ └────────────┴───┘ ``` ## toDecimal256 Converts an input value to a `Decimal256` data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows. ### Syntax ```sql toDecimal256(expr, S) ``` ### Arguments - `expr`: An expression that evaluates to a number or a string representation of a number. - Supported numeric types: `Int8`, `UInt8`, `Int16`, `UInt16`, `Int32`, `UInt32`, `Int64`, `UInt64`, `Int128`, `UInt128`, `Int256`, `UInt256`, `Float32`, `Float64`. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations: `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 76 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal256` value with the specified scale. [Decimal256(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal256(99, 1) AS a, toDecimal256(99.67, 2) AS b, toDecimal256('99.67', 3) AS c ``` Result: ```result ┌─a─┬─b─────┬─c─────┐ │ 99 │ 99.67 │ 99.67 │ └───┴───────┴───────┘ ``` ## toDecimal256OrZero Converts an input value to a `Decimal256` data type with a specified scale, returning `0` if the conversion fails. ### Syntax ```sql toDecimal256OrZero(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 76 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal256` value if successful, otherwise `0` with `S` decimal places. [Decimal256(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal256OrZero(toString(0.0001), 76) AS a, toDecimal256OrZero(toString('Inf'), 76) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.0001000000000000000000000000000000000000000000000000000000000000000000000000 │ 0 │ └────────────┴───┘ ``` ## toDecimal256OrNull Converts an input value to a `Decimal256` data type with a specified scale, returning `NULL` if the conversion fails. ### Syntax ```sql toDecimal256OrNull(expr, S) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `NULL`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 76 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). {% callout type="info" %} 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. {% /callout %} ### Returns A `Decimal256` value if successful, otherwise `NULL`. [Nullable](../data-types/nullable)([Decimal256(S)](../data-types/decimal)). ### Example ```sql SELECT toDecimal256OrNull(toString(1/42), 76) AS a, toDecimal256OrNull(toString('Inf'), 76) as b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.02380952380952380800000000000000000000000000000000000000000000000000000000 │ ᴺᵁᴸᴸ │ └────────────┴───┘ ``` ## toDecimal256OrDefault Converts an input value to a `Decimal256` data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns `0`. ### Syntax ```sql toDecimal256OrDefault(expr, S[, default]) ``` ### Arguments - `expr`: A string representation of a number. - Supported string representations: Integers, floating-point numbers. - Unsupported string representations (return `default` or `0`): `NaN`, `Inf`, binary, or hexadecimal values. - `S`: An integer from 0 to 76 representing the number of decimal places for the fractional part. [UInt8](../data-types/int-uint). - `default` (optional): The `Decimal256(S)` value to return if the conversion of `expr` is unsuccessful. {% callout type="info" %} 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. {% /callout %} {% callout type="warning" %} 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` {% /callout %} ### Returns A `Decimal256` value if successful, otherwise the `default` value (if provided) or `0`. [Decimal256(S)](../data-types/decimal). ### Example ```sql SELECT toDecimal256OrDefault(toString(1/42), 76) AS a, toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)')) AS b ``` Result: ```result ┌──────────a─┬─b─┐ │ 0.02380952380952380800000000000000000000000000000000000000000000000000000000 │ -1 │ └────────────┴───┘ ``` ## toString Converts a value of any supported data type to its string representation. ### Syntax ```sql toString(expr[, time_zone]) ``` ### Arguments - `expr`: The value to convert. Can be a number, string, date, or datetime. - `time_zone` (optional): For `DateTime` arguments, a string specifying the timezone for formatting the output. [String](../data-types/string). ### Returns A string representation of the input value. [String](../data-types/string). ### Example ```sql SELECT toString(123) AS num_to_string, toString(toDate('2023-01-15')) AS date_to_string, toString(toDateTime('2023-01-15 10:30:00'), 'Europe/Berlin') AS datetime_to_string_with_tz ``` Result: ```result ┌─num_to_string─┬─date_to_string─┬─datetime_to_string_with_tz─┐ │ 123 │ 2023-01-15 │ 2023-01-15 11:30:00 │ └───────────────┴────────────────┴────────────────────────────┘ ``` ## toFixedString Converts a string to a `FixedString(N)` data type, padding with null bytes or throwing an error if the length is mismatched. ### Syntax ```sql toFixedString(s, N) ``` ### Arguments - `s`: The string to convert. [String](../data-types/string). - `N`: The desired fixed length of the string. [UInt8](../data-types/int-uint). ### Returns A fixed-length string. [FixedString](../data-types/fixedstring). ### Example ```sql SELECT toFixedString('foo', 8) AS s ``` Result: ```result ┌─s─────────────┐ │ foo\0\0\0\0\0 │ └───────────────┘ ``` ## toStringCutToZero Truncates a string at the first null byte encountered. ### Syntax ```sql toStringCutToZero(s) ``` ### Arguments - `s`: The string to process. Can be a [String](../data-types/string) or [FixedString](../data-types/fixedstring). ### Returns A string truncated at the first null byte. [String](../data-types/string). ### Example ```sql SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut ``` Result: ```result ┌─s──────────┬─s_cut─┐ │ foo\0bar\0 │ foo │ └────────────┴───────┘ ``` ## toDecimalString Converts a numeric value to its string representation with a specified number of fractional digits. ### Syntax ```sql toDecimalString(number, scale) ``` ### Arguments - `number`: The numeric value to convert. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [Decimal](../data-types/decimal). - `scale`: The number of fractional digits to include in the output string. [UInt8](../data-types/int-uint). - Maximum scale for `Decimal` and integer types is 77. - Maximum scale for `Float` types is 60. ### Returns A string representation of the number with the specified fractional digits. [String](../data-types/string). ### Example ```sql SELECT toDecimalString(CAST('64.32', 'Float64'), 5) ``` Result: ```result ┌─toDecimalString(CAST('64.32', 'Float64'), 5)─┐ │ 64.32000 │ └──────────────────────────────────────────────┘ ``` ## reinterpretAsUInt8 Performs a byte-level reinterpretation of an input value as an 8-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt8(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as an 8-bit unsigned integer. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toInt8(257) AS x, reinterpretAsUInt8(x) AS res ``` Result: ```result ┌─x─┬─res─┐ │ 1 │ 1 │ └───┴─────┘ ``` ## reinterpretAsUInt16 Performs a byte-level reinterpretation of an input value as a 16-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt16(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 16-bit unsigned integer. [UInt16](../data-types/int-uint). ### Example ```sql SELECT toUInt8(257) AS x, reinterpretAsUInt16(x) AS res ``` Result: ```result ┌─x─┬─res─┐ │ 1 │ 1 │ └───┴─────┘ ``` ## reinterpretAsUInt32 Performs a byte-level reinterpretation of an input value as a 32-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt32(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 32-bit unsigned integer. [UInt32](../data-types/int-uint). ### Example ```sql SELECT toUInt16(257) AS x, reinterpretAsUInt32(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsUInt64 Performs a byte-level reinterpretation of an input value as a 64-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt64(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 64-bit unsigned integer. [UInt64](../data-types/int-uint). ### Example ```sql SELECT toUInt32(257) AS x, reinterpretAsUInt64(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsUInt128 Performs a byte-level reinterpretation of an input value as a 128-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt128(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 128-bit unsigned integer. [UInt128](../data-types/int-uint). ### Example ```sql SELECT toUInt64(257) AS x, reinterpretAsUInt128(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsUInt256 Performs a byte-level reinterpretation of an input value as a 256-bit unsigned integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsUInt256(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 256-bit unsigned integer. [UInt256](../data-types/int-uint). ### Example ```sql SELECT toUInt128(257) AS x, reinterpretAsUInt256(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsInt8 Performs a byte-level reinterpretation of an input value as an 8-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt8(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as an 8-bit signed integer. [Int8](../data-types/int-uint). ### Example ```sql SELECT toUInt8(257) AS x, reinterpretAsInt8(x) AS res ``` Result: ```result ┌─x─┬─res─┐ │ 1 │ 1 │ └───┴─────┘ ``` ## reinterpretAsInt16 Performs a byte-level reinterpretation of an input value as a 16-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt16(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 16-bit signed integer. [Int16](../data-types/int-uint). ### Example ```sql SELECT toInt8(257) AS x, reinterpretAsInt16(x) AS res ``` Result: ```result ┌─x─┬─res─┐ │ 1 │ 1 │ └───┴─────┘ ``` ## reinterpretAsInt32 Performs a byte-level reinterpretation of an input value as a 32-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt32(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 32-bit signed integer. [Int32](../data-types/int-uint). ### Example ```sql SELECT toInt16(257) AS x, reinterpretAsInt32(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsInt64 Performs a byte-level reinterpretation of an input value as a 64-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt64(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 64-bit signed integer. [Int64](../data-types/int-uint). ### Example ```sql SELECT toInt32(257) AS x, reinterpretAsInt64(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsInt128 Performs a byte-level reinterpretation of an input value as a 128-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt128(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 128-bit signed integer. [Int128](../data-types/int-uint). ### Example ```sql SELECT toInt64(257) AS x, reinterpretAsInt128(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsInt256 Performs a byte-level reinterpretation of an input value as a 256-bit signed integer. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsInt256(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 256-bit signed integer. [Int256](../data-types/int-uint). ### Example ```sql SELECT toInt128(257) AS x, reinterpretAsInt256(x) AS res ``` Result: ```result ┌───x─┬─res─┐ │ 257 │ 257 │ └─────┴─────┘ ``` ## reinterpretAsFloat32 Performs a byte-level reinterpretation of an input value as a 32-bit floating-point number. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsFloat32(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 32-bit floating-point number. [Float32](../data-types/float). ### Example ```sql SELECT reinterpretAsUInt32(toFloat32(0.2)) as x, reinterpretAsFloat32(x) ``` Result: ```result ┌──────────x─┬─reinterpretAsFloat32(x)─┐ │ 1045220557 │ 0.2 │ └────────────┴─────────────────────────┘ ``` ## reinterpretAsFloat64 Performs a byte-level reinterpretation of an input value as a 64-bit floating-point number. This function does not attempt to preserve the original value's meaning. ### Syntax ```sql reinterpretAsFloat64(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns The byte-reinterpreted value as a 64-bit floating-point number. [Float64](../data-types/float). ### Example ```sql SELECT reinterpretAsUInt64(toFloat64(0.2)) as x, reinterpretAsFloat64(x) ``` Result: ```result ┌───────────────────x─┬─reinterpretAsFloat64(x)─┐ │ 4596373779694328218 │ 0.2 │ └─────────────────────┴─────────────────────────┘ ``` ## reinterpretAsDate Interprets the bytes of an input value as a number (little-endian) and converts it to a `Date` representing days since the Unix Epoch. ### Syntax ```sql reinterpretAsDate(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns A date value. [Date](../data-types/date). {% callout type="info" %} 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. {% /callout %} ### Example ```sql SELECT reinterpretAsDate(65), reinterpretAsDate('A') ``` Result: ```result ┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐ │ 1970-03-07 │ 1970-03-07 │ └───────────────────────┴────────────────────────┘ ``` ## reinterpretAsDateTime Interprets the bytes of an input value as a number (little-endian) and converts it to a `DateTime` representing seconds since the Unix Epoch. ### Syntax ```sql reinterpretAsDateTime(x) ``` ### Arguments - `x`: The value to reinterpret. Can be any numeric type, [Date](../data-types/date), [DateTime](../data-types/datetime), [UUID](../data-types/uuid), [String](../data-types/string), or [FixedString](../data-types/fixedstring). ### Returns A datetime value. [DateTime](../data-types/datetime). {% callout type="info" %} 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. {% /callout %} ### Example ```sql SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A') ``` Result: ```result ┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐ │ 1970-01-01 01:01:05 │ 1970-01-01 01:01:05 │ └───────────────────────────┴────────────────────────────┘ ``` ## reinterpretAsString Converts a numeric, date, or datetime value into a string by representing its underlying bytes in little-endian order. Null bytes are removed from the end of the resulting string. ### Syntax ```sql reinterpretAsString(x) ``` ### Arguments - `x`: The value to reinterpret as a string. Can be any numeric type, [Date](../data-types/date), or [DateTime](../data-types/datetime). ### Returns A string containing the byte representation of the input value. [String](../data-types/string). ### Example ```sql SELECT reinterpretAsString(toDateTime('1970-01-01 01:01:05')), reinterpretAsString(toDate('1970-03-07')) ``` Result: ```result ┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐ │ A │ A │ └────────────────────────────────────────────────────────┴───────────────────────────────────────────┘ ``` ## reinterpretAsFixedString Converts a numeric, date, or datetime value into a fixed-length string by representing its underlying bytes in little-endian order. Null bytes are removed from the end of the resulting string. ### Syntax ```sql reinterpretAsFixedString(x) ``` ### Arguments - `x`: The value to reinterpret as a fixed string. Can be any numeric type, [Date](../data-types/date), or [DateTime](../data-types/datetime). ### Returns A fixed-length string containing the byte representation of the input value. [FixedString](../data-types/fixedstring). ### Example ```sql SELECT reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')), reinterpretAsFixedString(toDate('1970-03-07')) ``` Result: ```result ┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐ │ A │ A │ └─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘ ``` ## reinterpretAsUUID Interprets a 16-byte string as a UUID, treating each 8-byte half in little-endian byte order. ### Syntax ```sql reinterpretAsUUID(fixed_string) ``` ### Arguments - `fixed_string`: A 16-byte string to convert to a UUID. [FixedString](../data-types/fixedstring). ### Returns A UUID value. [UUID](../data-types/uuid). {% callout type="info" %} In addition to the UUID functions listed here, there is dedicated [UUID function documentation](../functions/uuid-functions). {% /callout %} ### Example ```sql SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f'))) ``` Result: ```result ┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐ │ 08090a0b-0c0d-0e0f-0001-020304050607 │ └───────────────────────────────────────────────────────────────────────┘ ``` ## reinterpret Reinterprets the in-memory byte sequence of a value to a different data type without attempting to preserve its original logical value. ### Syntax ```sql reinterpret(x, type) ``` ### Arguments - `x`: The value to reinterpret. Can be of any data type. - `type`: A string specifying the target data type. [String](../data-types/string). ### Returns The value reinterpreted as the specified target type. ### Example ```sql 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: ```result ┌─int_to_uint─┬─int_to_float─┬─string_to_int─┐ │ 255 │ 1e-45 │ 49 │ └─────────────┴──────────────┴───────────────┘ ``` ## CAST Converts an input value to a specified data type, attempting to preserve the value's meaning. If the conversion is not possible, an exception is raised. ### Syntax ```sql CAST(x, T) CAST(x AS t) x::t ``` ### Arguments - `x`: The value to convert. Can be of any data type. - `T`: A string specifying the target data type. [String](../data-types/string). - `t`: The target data type. ### Returns The converted value in the target data type. {% callout type="info" %} If the input value doesn't fit the bounds of the target type, the result overflows. For example, `CAST(-1, 'UInt8')` returns `255`. {% /callout %} ### Example ```sql 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: ```result ┌─cast_int_to_uint─┬─cast_float_to_decimal─┬─cast_string_to_int─┐ │ 255 │ 1.50 │ 1 │ └──────────────────┴───────────────────────┴────────────────────┘ ``` ## accurateCast(x, T) Converts a value `x` to the data type `T`, strictly enforcing type bounds. Unlike `CAST`, this function throws an exception if the value cannot be safely represented in the target type (e.g., due to overflow or underflow). ### Syntax ```sql accurateCast(x, T) ``` ### Arguments - `x`: The input value to convert. - `T`: A string specifying the target data type. ### Returns The converted value in the target data type. ### Example ```sql SELECT accurateCast(-1, 'UInt8') as uint8 ``` Result: ```result Code: 70. DB::Exception: Received from localhost:9000. DB::Exception: Value in column Int8 can't be safely converted into type UInt8: While processing accurateCast(-1, 'UInt8') AS uint8. ``` ## accurateCastOrNull(x, T) Converts a value `x` to the data type `T`, returning `NULL` if the value cannot be safely represented in the target type (e.g., due to overflow or underflow). The return type is always [Nullable](../data-types/nullable). ### Syntax ```sql accurateCastOrNull(x, T) ``` ### Arguments - `x`: The input value to convert. - `T`: A string specifying the target data type. ### Returns The converted value in the target data type if successful, otherwise `NULL`. [Nullable](../data-types/nullable) of type `T`. ### Example ```sql SELECT accurateCastOrNull(-1, 'UInt8') as uint8, accurateCastOrNull(128, 'Int8') as int8, accurateCastOrNull('Test', 'FixedString(2)') as fixed_string ``` Result: ```result ┌─uint8─┬─int8─┬─fixed_string─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ └───────┴──────┴──────────────┘ ``` ## accurateCastOrDefault(x, T[, default_value]) Converts a value `x` to the data type `T`, returning a specified default value if the value cannot be safely represented in the target type (e.g., due to overflow or underflow). If no default is provided, it returns the default value for type `T`. ### Syntax ```sql accurateCastOrDefault(x, T[, default_value]) ``` ### Arguments - `x`: The input value to convert. - `T`: A string specifying the target data type. - `default_value` (optional): The value to return if the conversion is unsuccessful. This value must be compatible with type `T`. ### Returns The converted value in the target data type if successful, otherwise the `default_value` (if provided) or the default value for type `T`. ### Example ```sql 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: ```result ┌─uint8─┬─uint8_default─┬─int8─┬─int8_default─┬─fixed_string─┬─fixed_string_default─┐ │ 0 │ 5 │ 0 │ 5 │ │ Te │ └───────┴───────────────┴──────┴──────────────┴──────────────┴──────────────────────┘ ``` ## toIntervalYear Creates an interval representing a specified number of years. ### Syntax ```sql toIntervalYear(n) ``` ### Arguments - `n`: The number of years. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` years. [IntervalYear](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalYear(1) AS interval_to_year SELECT date + interval_to_year AS result ``` Result: ```result ┌─────result─┐ │ 2025-06-15 │ └────────────┘ ``` ## toIntervalQuarter Creates an interval representing a specified number of quarters. ### Syntax ```sql toIntervalQuarter(n) ``` ### Arguments - `n`: The number of quarters. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` quarters. [IntervalQuarter](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalQuarter(1) AS interval_to_quarter SELECT date + interval_to_quarter AS result ``` Result: ```result ┌─────result─┐ │ 2024-09-15 │ └────────────┘ ``` ## toIntervalMonth Creates an interval representing a specified number of months. ### Syntax ```sql toIntervalMonth(n) ``` ### Arguments - `n`: The number of months. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` months. [IntervalMonth](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalMonth(1) AS interval_to_month SELECT date + interval_to_month AS result ``` Result: ```result ┌─────result─┐ │ 2024-07-15 │ └────────────┘ ``` ## toIntervalWeek Creates an interval representing a specified number of weeks. ### Syntax ```sql toIntervalWeek(n) ``` ### Arguments - `n`: The number of weeks. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` weeks. [IntervalWeek](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalWeek(1) AS interval_to_week SELECT date + interval_to_week AS result ``` Result: ```result ┌─────result─┐ │ 2024-06-22 │ └────────────┘ ``` ## toIntervalDay Creates an interval representing a specified number of days. ### Syntax ```sql toIntervalDay(n) ``` ### Arguments - `n`: The number of days. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` days. [IntervalDay](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalDay(5) AS interval_to_days SELECT date + interval_to_days AS result ``` Result: ```result ┌─────result─┐ │ 2024-06-20 │ └────────────┘ ``` ## toIntervalHour Creates an interval representing a specified number of hours. ### Syntax ```sql toIntervalHour(n) ``` ### Arguments - `n`: The number of hours. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` hours. [IntervalHour](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalHour(12) AS interval_to_hours SELECT date + interval_to_hours AS result ``` Result: ```result ┌──────────────result─┐ │ 2024-06-15 12:00:00 │ └─────────────────────┘ ``` ## toIntervalMinute Creates an interval representing a specified number of minutes. ### Syntax ```sql toIntervalMinute(n) ``` ### Arguments - `n`: The number of minutes. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` minutes. [IntervalMinute](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalMinute(12) AS interval_to_minutes SELECT date + interval_to_minutes AS result ``` Result: ```result ┌──────────────result─┐ │ 2024-06-15 00:12:00 │ └─────────────────────┘ ``` ## toIntervalSecond Creates an interval representing a specified number of seconds. ### Syntax ```sql toIntervalSecond(n) ``` ### Arguments - `n`: The number of seconds. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` seconds. [IntervalSecond](../data-types/interval). ### Example ```sql WITH toDate('2024-06-15') AS date, toIntervalSecond(30) AS interval_to_seconds SELECT date + interval_to_seconds AS result ``` Result: ```result ┌──────────────result─┐ │ 2024-06-15 00:00:30 │ └─────────────────────┘ ``` ## toIntervalMillisecond Creates an interval representing a specified number of milliseconds. ### Syntax ```sql toIntervalMillisecond(n) ``` ### Arguments - `n`: The number of milliseconds. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` milliseconds. [IntervalMillisecond](../data-types/interval). ### Example ```sql WITH toDateTime('2024-06-15') AS date, toIntervalMillisecond(30) AS interval_to_milliseconds SELECT date + interval_to_milliseconds AS result ``` Result: ```result ┌──────────────────result─┐ │ 2024-06-15 00:00:00.030 │ └─────────────────────────┘ ``` ## toIntervalMicrosecond Creates an interval representing a specified number of microseconds. ### Syntax ```sql toIntervalMicrosecond(n) ``` ### Arguments - `n`: The number of microseconds. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` microseconds. [IntervalMicrosecond](../data-types/interval). ### Example ```sql WITH toDateTime('2024-06-15') AS date, toIntervalMicrosecond(30) AS interval_to_microseconds SELECT date + interval_to_microseconds AS result ``` Result: ```result ┌─────────────────────result─┐ │ 2024-06-15 00:00:00.000030 │ └────────────────────────────┘ ``` ## toIntervalNanosecond Creates an interval representing a specified number of nanoseconds. ### Syntax ```sql toIntervalNanosecond(n) ``` ### Arguments - `n`: The number of nanoseconds. Can be an [Int](../data-types/int-uint), [UInt](../data-types/int-uint), [Float](../data-types/float), or [String](../data-types/string). ### Returns An interval of `n` nanoseconds. [IntervalNanosecond](../data-types/interval). ### Example ```sql WITH toDateTime('2024-06-15') AS date, toIntervalNanosecond(30) AS interval_to_nanoseconds SELECT date + interval_to_nanoseconds AS result ``` Result: ```result ┌────────────────────────result─┐ │ 2024-06-15 00:00:00.000000030 │ └───────────────────────────────┘ ``` ## parseDateTime Converts a string to a `DateTime` value using a MySQL-style format string. This is the inverse of `formatDateTime`. ### Syntax ```sql parseDateTime(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value parsed from the input string. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s') ``` Result: ```result ┌─parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐ │ 2021-01-04 23:00:00 │ └───────────────────────────────────────────────────────────┘ ``` ## parseDateTimeOrZero Converts a string to a `DateTime` value using a MySQL-style format string, returning the zero date (`1970-01-01 00:00:00`) if parsing fails. ### Syntax ```sql parseDateTimeOrZero(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeOrZero('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s'), parseDateTimeOrZero('invalid_date') ``` Result: ```result ┌─parseDateTimeOrZero('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┬─parseDateTimeOrZero('invalid_date')─┐ │ 2021-01-04 23:00:00 │ 1970-01-01 00:00:00 │ └───────────────────────────────────────────────────────────┴───────────────────────────────────────┘ ``` ## parseDateTimeOrNull Converts a string to a `DateTime` value using a MySQL-style format string, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTimeOrNull(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime](../data-types/datetime)). ### Example ```sql SELECT parseDateTimeOrNull('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s'), parseDateTimeOrNull('invalid_date') ``` Result: ```result ┌─parseDateTimeOrNull('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┬─parseDateTimeOrNull('invalid_date')─┐ │ 2021-01-04 23:00:00 │ ᴺᵁᴸᴸ │ └───────────────────────────────────────────────────────────┴───────────────────────────────────────┘ ``` ## parseDateTimeInJodaSyntax Converts a string to a `DateTime` value using a Joda-style format string. This is the inverse of `formatDateTimeInJodaSyntax`. ### Syntax ```sql parseDateTimeInJodaSyntax(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value parsed from the input string. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')─┐ │ 2023-02-24 14:53:31 │ └─────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTimeInJodaSyntaxOrZero Converts a string to a `DateTime` value using a Joda-style format string, returning the zero date (`1970-01-01 00:00:00`) if parsing fails. ### Syntax ```sql parseDateTimeInJodaSyntaxOrZero(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeInJodaSyntaxOrZero('invalid_date', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTimeInJodaSyntaxOrZero('invalid_date', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')─┐ │ 1970-01-01 00:00:00 │ └─────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTimeInJodaSyntaxOrNull Converts a string to a `DateTime` value using a Joda-style format string, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTimeInJodaSyntaxOrNull(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime](../data-types/datetime)). ### Example ```sql SELECT parseDateTimeInJodaSyntaxOrNull('invalid_date', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTimeInJodaSyntaxOrNull('invalid_date', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')─┐ │ ᴺᵁᴸᴸ │ └─────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64 Converts a string to a `DateTime64` value using a MySQL-style format string. ### Syntax ```sql parseDateTime64(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s.%f'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value parsed from the input string with a precision of 6. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64('2021-01-04 23:00:00.123456', '%Y-%m-%d %H:%i:%s.%f') ``` Result: ```result ┌─parseDateTime64('2021-01-04 23:00:00.123456', '%Y-%m-%d %H:%i:%s.%f')─┐ │ 2021-01-04 23:00:00.123456 │ └──────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64OrZero Converts a string to a `DateTime64` value using a MySQL-style format string, returning the zero date (`1970-01-01 00:00:00.000000`) if parsing fails. ### Syntax ```sql parseDateTime64OrZero(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s.%f'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `1970-01-01 00:00:00.000000`. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64OrZero('invalid_date', '%Y-%m-%d %H:%i:%s.%f') ``` Result: ```result ┌─parseDateTime64OrZero('invalid_date', '%Y-%m-%d %H:%i:%s.%f')─┐ │ 1970-01-01 00:00:00.000000 │ └──────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64OrNull Converts a string to a `DateTime64` value using a MySQL-style format string, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTime64OrNull(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The MySQL-style format string. Defaults to `'%Y-%m-%d %H:%i:%s.%f'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime64](../data-types/datetime64)). ### Example ```sql SELECT parseDateTime64OrNull('invalid_date', '%Y-%m-%d %H:%i:%s.%f') ``` Result: ```result ┌─parseDateTime64OrNull('invalid_date', '%Y-%m-%d %H:%i:%s.%f')─┐ │ ᴺᵁᴸᴸ │ └────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64InJodaSyntax Converts a string to a `DateTime64` value using a Joda-style format string. ### Syntax ```sql parseDateTime64InJodaSyntax(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value parsed from the input string. The precision is determined by the number of `'S'` placeholders in the format string (up to 6). [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64InJodaSyntax('2023-02-24 14:53:31.123', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTime64InJodaSyntax('2023-02-24 14:53:31.123', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk')─┐ │ 2023-02-24 14:53:31.123 │ └───────────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64InJodaSyntaxOrZero Converts a string to a `DateTime64` value using a Joda-style format string, returning the zero date (`1970-01-01 00:00:00.000000`) if parsing fails. ### Syntax ```sql parseDateTime64InJodaSyntaxOrZero(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `1970-01-01 00:00:00.000000`. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64InJodaSyntaxOrZero('invalid_date', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTime64InJodaSyntaxOrZero('invalid_date', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk')─┐ │ 1970-01-01 00:00:00.000 │ └───────────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTime64InJodaSyntaxOrNull Converts a string to a `DateTime64` value using a Joda-style format string, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTime64InJodaSyntaxOrNull(str[, format[, timezone]]) ``` ### Arguments - `str`: The string to parse. [String](../data-types/string). - `format` (optional): The Joda-style format string. Defaults to `'yyyy-MM-dd HH:mm:ss'`. - `timezone` (optional): The timezone to use for parsing. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime64](../data-types/datetime64)). ### Example ```sql SELECT parseDateTime64InJodaSyntaxOrNull('invalid_date', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk') ``` Result: ```result ┌─parseDateTime64InJodaSyntaxOrNull('invalid_date', 'yyyy-MM-dd HH:mm:ss.SSS', 'Europe/Minsk')─┐ │ ᴺᵁᴸᴸ │ └───────────────────────────────────────────────────────────────────────────────────────────────┘ ``` ## parseDateTimeBestEffort Converts a string representation of a date and time to a `DateTime` data type, attempting to parse various common formats. ### Syntax ```sql parseDateTimeBestEffort(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value converted from the input string. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeBestEffort('23/10/2020 12:12:57') AS from_ddmmyyyy_hhmmss, parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Asia/Istanbul') AS from_rfc_with_tz, parseDateTimeBestEffort('1284101485') AS from_unix_timestamp_string ``` Result: ```result ┌─from_ddmmyyyy_hhmmss─┬─from_rfc_with_tz─┬─from_unix_timestamp_string─┐ │ 2020-10-23 12:12:57 │ 2018-08-18 10:22:16 │ 2015-07-07 12:04:41 │ └──────────────────────┴───────────────────┴────────────────────────────┘ ``` ## parseDateTime32BestEffort Alias for `parseDateTimeBestEffort`. ### Syntax ```sql parseDateTime32BestEffort(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value converted from the input string. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTime32BestEffort('2018-10-23 10:12:12') AS result ``` Result: ```result ┌─result─┐ │ 2018-10-23 10:12:12 │ └────────┘ ``` ## parseDateTimeBestEffortUS Converts a string representation of a date and time to a `DateTime` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists. ### Syntax ```sql parseDateTimeBestEffortUS(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value converted from the input string. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeBestEffortUS('01/02/2023 10:00:00') AS us_format_date ``` Result: ```result ┌─us_format_date─┐ │ 2023-01-02 10:00:00 │ └────────────────┘ ``` ## parseDateTimeBestEffortOrNull Converts a string representation of a date and time to a `DateTime` data type, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTimeBestEffortOrNull(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime](../data-types/datetime)). ### Example ```sql SELECT parseDateTimeBestEffortOrNull('invalid_date_string') AS result ``` Result: ```result ┌─result─┐ │ ᴺᵁᴸᴸ │ └────────┘ ``` ## parseDateTime32BestEffortOrNull Alias for `parseDateTimeBestEffortOrNull`. ### Syntax ```sql parseDateTime32BestEffortOrNull(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime](../data-types/datetime)). ### Example ```sql SELECT parseDateTime32BestEffortOrNull('another_bad_date') AS result ``` Result: ```result ┌─result─┐ │ ᴺᵁᴸᴸ │ └────────┘ ``` ## parseDateTimeBestEffortOrZero Converts a string representation of a date and time to a `DateTime` data type, returning the zero date (`1970-01-01 00:00:00`) if parsing fails. ### Syntax ```sql parseDateTimeBestEffortOrZero(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeBestEffortOrZero('invalid_date_string') AS result ``` Result: ```result ┌─result─┐ │ 1970-01-01 00:00:00 │ └────────┘ ``` ## parseDateTime32BestEffortOrZero Alias for `parseDateTimeBestEffortOrZero`. ### Syntax ```sql parseDateTime32BestEffortOrZero(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTime32BestEffortOrZero('another_bad_date') AS result ``` Result: ```result ┌─result─┐ │ 1970-01-01 00:00:00 │ └────────┘ ``` ## parseDateTimeBestEffortUSOrNull Converts a string representation of a date and time to a `DateTime` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists, and returning `NULL` if parsing fails. ### Syntax ```sql parseDateTimeBestEffortUSOrNull(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime](../data-types/datetime)). ### Example ```sql SELECT parseDateTimeBestEffortUSOrNull('invalid_date_string') AS result ``` Result: ```result ┌─result─┐ │ ᴺᵁᴸᴸ │ └────────┘ ``` ## parseDateTimeBestEffortUSOrZero Converts a string representation of a date and time to a `DateTime` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists, and returning the zero date (`1970-01-01 00:00:00`) if parsing fails. ### Syntax ```sql parseDateTimeBestEffortUSOrZero(time_string [, time_zone]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime` value if parsing is successful, otherwise `1970-01-01 00:00:00`. [DateTime](../data-types/datetime). ### Example ```sql SELECT parseDateTimeBestEffortUSOrZero('invalid_date_string') AS result ``` Result: ```result ┌─result─┐ │ 1970-01-01 00:00:00 │ └────────┘ ``` ## parseDateTime64BestEffort Converts a string representation of a date and time to a `DateTime64` data type, attempting to parse various common formats including sub-second precision. ### Syntax ```sql parseDateTime64BestEffort(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision (e.g., `3` for milliseconds, `6` for microseconds). Defaults to `3`. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value converted from the input string. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346', 6, 'Asia/Istanbul') AS result ``` Result: ```result ┌─────────────────────result─┐ │ 2020-12-31 22:01:00.123460 │ └────────────────────────────┘ ``` ## parseDateTime64BestEffortUS Converts a string representation of a date and time to a `DateTime64` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists, and including sub-second precision. ### Syntax ```sql parseDateTime64BestEffortUS(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision (e.g., `3` for milliseconds, `6` for microseconds). Defaults to `3`. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value converted from the input string. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64BestEffortUS('01/02/2023 10:00:00.123', 3) AS us_format_datetime ``` Result: ```result ┌─us_format_datetime─┐ │ 2023-01-02 10:00:00.123 │ └────────────────────┘ ``` ## parseDateTime64BestEffortOrNull Converts a string representation of a date and time to a `DateTime64` data type, returning `NULL` if parsing fails. ### Syntax ```sql parseDateTime64BestEffortOrNull(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime64](../data-types/datetime64)). ### Example ```sql SELECT parseDateTime64BestEffortOrNull('invalid_datetime_string', 3) AS result ``` Result: ```result ┌─result─┐ │ ᴺᵁᴸᴸ │ └────────┘ ``` ## parseDateTime64BestEffortOrZero Converts a string representation of a date and time to a `DateTime64` data type, returning the zero date (`1970-01-01 00:00:00.000000`) if parsing fails. ### Syntax ```sql parseDateTime64BestEffortOrZero(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `1970-01-01 00:00:00.000000`. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64BestEffortOrZero('invalid_datetime_string', 3) AS result ``` Result: ```result ┌─result─┐ │ 1970-01-01 00:00:00.000 │ └────────┘ ``` ## parseDateTime64BestEffortUSOrNull Converts a string representation of a date and time to a `DateTime64` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists, and returning `NULL` if parsing fails. ### Syntax ```sql parseDateTime64BestEffortUSOrNull(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `NULL`. [Nullable](../data-types/nullable)([DateTime64](../data-types/datetime64)). ### Example ```sql SELECT parseDateTime64BestEffortUSOrNull('invalid_datetime_string', 3) AS result ``` Result: ```result ┌─result─┐ │ ᴺᵁᴸᴸ │ └────────┘ ``` ## parseDateTime64BestEffortUSOrZero Converts a string representation of a date and time to a `DateTime64` data type, prioritizing US date formats (`MM/DD/YYYY`) when ambiguity exists, and returning the zero date (`1970-01-01 00:00:00.000000`) if parsing fails. ### Syntax ```sql parseDateTime64BestEffortUSOrZero(time_string [, precision [, time_zone]]) ``` ### Arguments - `time_string`: The string containing the date and time to convert. [String](../data-types/string). - `precision` (optional): The desired sub-second precision. [UInt8](../data-types/int-uint). - `time_zone` (optional): The timezone to use for parsing the `time_string`. [String](../data-types/string). ### Returns A `DateTime64` value if parsing is successful, otherwise `1970-01-01 00:00:00.000000`. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT parseDateTime64BestEffortUSOrZero('invalid_datetime_string', 3) AS result ``` Result: ```result ┌─result─┐ │ 1970-01-01 00:00:00.000 │ └────────┘ ``` ## toLowCardinality Converts an input value to its `LowCardinality` version, which can optimize storage and query performance for columns with a limited number of distinct values. ### Syntax ```sql toLowCardinality(expr) ``` ### Arguments - `expr`: An expression that evaluates to a value of any [supported data type](../data-types). ### Returns The input value converted to a `LowCardinality` type. [LowCardinality](../data-types/lowcardinality). ### Example ```sql SELECT toLowCardinality('1') ``` Result: ```result ┌─toLowCardinality('1')─┐ │ 1 │ └───────────────────────┘ ``` ## toUnixTimestamp64Second Converts a `DateTime64` value to an `Int64` Unix timestamp with second precision. The input `DateTime64` value is scaled appropriately. ### Syntax ```sql toUnixTimestamp64Second(value) ``` ### Arguments - `value`: A `DateTime64` value with any precision. [DateTime64](../data-types/datetime64). ### Returns An `Int64` Unix timestamp representing seconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). ### Example ```sql WITH toDateTime64('2009-02-13 23:31:31.011', 3, 'UTC') AS dt64 SELECT toUnixTimestamp64Second(dt64) ``` Result: ```result ┌─toUnixTimestamp64Second(dt64)─┐ │ 1234567891 │ └───────────────────────────────┘ ``` ## toUnixTimestamp64Milli Converts a `DateTime64` value to an `Int64` Unix timestamp with millisecond precision. The input `DateTime64` value is scaled appropriately. ### Syntax ```sql toUnixTimestamp64Milli(value) ``` ### Arguments - `value`: A `DateTime64` value with any precision. [DateTime64](../data-types/datetime64). ### Returns An `Int64` Unix timestamp representing milliseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). ### Example ```sql WITH toDateTime64('2009-02-13 23:31:31.011', 3, 'UTC') AS dt64 SELECT toUnixTimestamp64Milli(dt64) ``` Result: ```result ┌─toUnixTimestamp64Milli(dt64)─┐ │ 1234567891011 │ └──────────────────────────────┘ ``` ## toUnixTimestamp64Micro Converts a `DateTime64` value to an `Int64` Unix timestamp with microsecond precision. The input `DateTime64` value is scaled appropriately. ### Syntax ```sql toUnixTimestamp64Micro(value) ``` ### Arguments - `value`: A `DateTime64` value with any precision. [DateTime64](../data-types/datetime64). ### Returns An `Int64` Unix timestamp representing microseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). ### Example ```sql WITH toDateTime64('1970-01-15 06:56:07.891011', 6, 'UTC') AS dt64 SELECT toUnixTimestamp64Micro(dt64) ``` Result: ```result ┌─toUnixTimestamp64Micro(dt64)─┐ │ 1234567891011 │ └──────────────────────────────┘ ``` ## toUnixTimestamp64Nano Converts a `DateTime64` value to an `Int64` Unix timestamp with nanosecond precision. The input `DateTime64` value is scaled appropriately. ### Syntax ```sql toUnixTimestamp64Nano(value) ``` ### Arguments - `value`: A `DateTime64` value with any precision. [DateTime64](../data-types/datetime64). ### Returns An `Int64` Unix timestamp representing nanoseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). ### Example ```sql WITH toDateTime64('1970-01-01 00:20:34.567891011', 9, 'UTC') AS dt64 SELECT toUnixTimestamp64Nano(dt64) ``` Result: ```result ┌─toUnixTimestamp64Nano(dt64)─┐ │ 1234567891011 │ └─────────────────────────────┘ ``` ## fromUnixTimestamp64Second Converts an `Int64` Unix timestamp (seconds) to a `DateTime64` value with second precision, optionally specifying a timezone. ### Syntax ```sql fromUnixTimestamp64Second(value[, timezone]) ``` ### Arguments - `value`: An `Int64` Unix timestamp representing seconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for the resulting `DateTime64` value. [String](../data-types/string). ### Returns A `DateTime64` value with 0 precision (seconds). [DateTime64](../data-types/datetime64). ### Example ```sql WITH CAST(1733935988, 'Int64') AS i64 SELECT fromUnixTimestamp64Second(i64, 'UTC') AS x, toTypeName(x) ``` Result: ```result ┌───────────────────x─┬─toTypeName(x)────────┐ │ 2024-12-11 16:53:08 │ DateTime64(0, 'UTC') │ └─────────────────────┴──────────────────────┘ ``` ## fromUnixTimestamp64Milli Converts an `Int64` Unix timestamp (milliseconds) to a `DateTime64` value with millisecond precision, optionally specifying a timezone. ### Syntax ```sql fromUnixTimestamp64Milli(value[, timezone]) ``` ### Arguments - `value`: An `Int64` Unix timestamp representing milliseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for the resulting `DateTime64` value. [String](../data-types/string). ### Returns A `DateTime64` value with 3 precision (milliseconds). [DateTime64](../data-types/datetime64). ### Example ```sql WITH CAST(1733935988123, 'Int64') AS i64 SELECT fromUnixTimestamp64Milli(i64, 'UTC') AS x, toTypeName(x) ``` Result: ```result ┌───────────────────────x─┬─toTypeName(x)────────┐ │ 2024-12-11 16:53:08.123 │ DateTime64(3, 'UTC') │ └─────────────────────────┴──────────────────────┘ ``` ## fromUnixTimestamp64Micro Converts an `Int64` Unix timestamp (microseconds) to a `DateTime64` value with microsecond precision, optionally specifying a timezone. ### Syntax ```sql fromUnixTimestamp64Micro(value[, timezone]) ``` ### Arguments - `value`: An `Int64` Unix timestamp representing microseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for the resulting `DateTime64` value. [String](../data-types/string). ### Returns A `DateTime64` value with 6 precision (microseconds). [DateTime64](../data-types/datetime64). ### Example ```sql WITH CAST(1733935988123456, 'Int64') AS i64 SELECT fromUnixTimestamp64Micro(i64, 'UTC') AS x, toTypeName(x) ``` Result: ```result ┌──────────────────────────x─┬─toTypeName(x)────────┐ │ 2024-12-11 16:53:08.123456 │ DateTime64(6, 'UTC') │ └────────────────────────────┴──────────────────────┘ ``` ## fromUnixTimestamp64Nano Converts an `Int64` Unix timestamp (nanoseconds) to a `DateTime64` value with nanosecond precision, optionally specifying a timezone. ### Syntax ```sql fromUnixTimestamp64Nano(value[, timezone]) ``` ### Arguments - `value`: An `Int64` Unix timestamp representing nanoseconds since the Unix Epoch in UTC. [Int64](../data-types/int-uint). - `timezone` (optional): A string specifying the timezone for the resulting `DateTime64` value. [String](../data-types/string). ### Returns A `DateTime64` value with 9 precision (nanoseconds). [DateTime64](../data-types/datetime64). ### Example ```sql WITH CAST(1733935988123456789, 'Int64') AS i64 SELECT fromUnixTimestamp64Nano(i64, 'UTC') AS x, toTypeName(x) ``` Result: ```result ┌─────────────────────────────x─┬─toTypeName(x)────────┐ │ 2024-12-11 16:53:08.123456789 │ DateTime64(9, 'UTC') │ └───────────────────────────────┴──────────────────────┘ ``` ## formatRow Formats multiple expressions into a single string using a specified text format. The output typically includes a newline character at the end. ### Syntax ```sql formatRow(format, x, y, ...) ``` ### Arguments - `format`: The name of the text format to use (e.g., `'CSV'`, `'TSV'`). - `x`, `y`, ...: The expressions to format. ### Returns A formatted string, usually terminated with a newline character. [String](../data-types/string). ### Example ```sql SELECT formatRow('CSV', number, 'good') FROM numbers(3) ``` Result: ```result ┌─formatRow('CSV', number, 'good')─┐ │ 0,"good" │ │ 1,"good" │ │ 2,"good" │ └──────────────────────────────────┘ ``` ## formatRowNoNewline Formats multiple expressions into a single string using a specified text format, similar to `formatRow`, but removes any trailing newline character. ### Syntax ```sql formatRowNoNewline(format, x, y, ...) ``` ### Arguments - `format`: The name of the text format to use (e.g., `'CSV'`, `'TSV'`). - `x`, `y`, ...: The expressions to format. ### Returns A formatted string, without a trailing newline character. [String](../data-types/string). ### Example ```sql SELECT formatRowNoNewline('CSV', number, 'good') FROM numbers(3) ``` Result: ```result ┌─formatRowNoNewline('CSV', number, 'good')─┐ │ 0,"good" │ │ 1,"good" │ │ 2,"good" │ └───────────────────────────────────────────┘ ```