Type conversion functions

Common issues with data conversion

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

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

Example:

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

┌

Notes on toString functions

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

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

Notes on toDate/toDateTime functions

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

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

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

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

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

Example

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

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

Also see the toUnixTimestamp function.

CAST

Introduced in: v1.1.0

Converts a value to a specified data type. Unlike the reinterpret function, CAST tries to generate the same value in the target type. If that is not possible, an exception is raised.

Syntax

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

Arguments

• x — A value of any type. Any
• T — The target data type. String

Returned value

Returns the converted value with the target data type. Any

Examples

Basic usage

SELECT CAST(42, 'String')

┌─CAST(42, 'String')─┐
│ 42                 │
└────────────────────┘

Using AS syntax

SELECT CAST('2025-01-01' AS Date)

┌─CAST('2025-01-01', 'Date')─┐
│                 2025-01-01 │
└────────────────────────────┘

Using :: syntax

SELECT '123'::UInt32

┌─CAST('123', 'UInt32')─┐
│                   123 │
└───────────────────────┘

DATE

Introduced in: v21.2.0

Converts the argument to the Date data type. This is a MySQL compatibility alias for toDate. It behaves the same as toDate.

Syntax

DATE(expr)

Arguments

• expr — The value to convert. String or UInt32 or Date or DateTime

Returned value

Returns a Date value. Date

Examples

Basic usage

SELECT DATE('2023-01-01')

2023-01-01

accurateCast

Introduced in: v1.1.0

Converts a value to a specified data type. Unlike CAST, accurateCast performs stricter type checking and throws an exception if the conversion would result in a loss of data precision or if the conversion is not possible.

This function is safer than regular CAST as it prevents precision loss and invalid conversions.

Syntax

accurateCast(x, T)

Arguments

• x — A value to convert. Any
• T — The target data type name. String

Returned value

Returns the converted value with the target data type. Any

Examples

Successful conversion

SELECT accurateCast(42, 'UInt16')

┌─accurateCast(42, 'UInt16')─┐
│                         42 │
└────────────────────────────┘

String to number

SELECT accurateCast('123.45', 'Float64')

┌─accurateCast('123.45', 'Float64')─┐
│                            123.45 │
└───────────────────────────────────┘

accurateCastOrDefault

Introduced in: v21.1.0

Converts a value to a specified data type. Like accurateCast, but returns a default value instead of throwing an exception if the conversion cannot be performed accurately.

If a default value is provided as the second argument, it must be of the target type. If no default value is provided, the default value of the target type is used.

Syntax

accurateCastOrDefault(x, T[, default_value])

Arguments

• x — A value to convert. Any
• T — The target data type name. const String
• default_value — Optional. Default value to return if conversion fails. Any

Returned value

Returns the converted value with the target data type, or the default value if conversion is not possible. Any

Examples

Successful conversion

SELECT accurateCastOrDefault(42, 'String')

┌─accurateCastOrDefault(42, 'String')─┐
│ 42                                  │
└─────────────────────────────────────┘

Failed conversion with explicit default

SELECT accurateCastOrDefault('abc', 'UInt32', 999::UInt32)

┌─accurateCastOrDefault('abc', 'UInt32', CAST('999', 'UInt32'))─┐
│                                                           999 │
└───────────────────────────────────────────────────────────────┘

Failed conversion with implicit default

SELECT accurateCastOrDefault('abc', 'UInt32')

┌─accurateCastOrDefault('abc', 'UInt32')─┐
│                                      0 │
└────────────────────────────────────────┘

accurateCastOrNull

Introduced in: v1.1.0

Converts a value to a specified data type. Like accurateCast, but returns NULL instead of throwing an exception if the conversion cannot be performed accurately.

This function combines the safety of accurateCast with graceful error handling.

Syntax

accurateCastOrNull(x, T)

Arguments

• x — A value to convert. Any
• T — The target data type name. String

Returned value

Returns the converted value with the target data type, or NULL if conversion is not possible. Any

Examples

Successful conversion

SELECT accurateCastOrNull(42, 'String')

┌─accurateCastOrNull(42, 'String')─┐
│ 42                               │
└──────────────────────────────────┘

Failed conversion returns NULL

SELECT accurateCastOrNull('abc', 'UInt32')

┌─accurateCastOrNull('abc', 'UInt32')─┐
│                                ᴺᵁᴸᴸ │
└─────────────────────────────────────┘

formatRow

Introduced in: v20.7.0

Converts arbitrary expressions into a string via given format.

Syntax

formatRow(format, x, y, ...)

Arguments

• format — Text format. For example, CSV, TSV. String
• x, y, ... — Expressions. Any

Returned value

A formatted string. (for text formats it’s usually terminated with the new line character). String

Examples

Basic usage

SELECT formatRow('CSV', number, 'good')
FROM numbers(3)

┌─formatRow('CSV', number, 'good')─┐
│ 0,"good"                        ↴│
│ 1,"good"                        ↴│
│ 2,"good"                        ↴│
└──────────────────────────────────┘

With custom format

SELECT formatRow('CustomSeparated', number, 'good')
FROM numbers(3)
SETTINGS format_custom_result_before_delimiter='<prefix>\n', format_custom_result_after_delimiter='<suffix>'

┌─formatRow('CustomSeparated', number, 'good')─┐
│ <prefix>                                    ↴│
│↳0	good                                     ↴│
│↳<suffix>                                     │
│ <prefix>                                    ↴│
│↳1	good                                     ↴│
│↳<suffix>                                     │
│ <prefix>                                    ↴│
│↳2	good                                     ↴│
│↳<suffix>                                     │
└──────────────────────────────────────────────┘

formatRowNoNewline

Introduced in: v20.7.0

Same as formatRow, but trims the newline character of each row.

Converts arbitrary expressions into a string via given format, but removes any trailing newline characters from the result.

Syntax

formatRowNoNewline(format, x, y, ...)

Arguments

• format — Text format. For example, CSV, TSV. String
• x, y, ... — Expressions. Any

Returned value

Returns a formatted string with newlines removed. String

Examples

Basic usage

SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3)

┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good"                                  │
│ 1,"good"                                  │
│ 2,"good"                                  │
└───────────────────────────────────────────┘

fromUnixTimestamp64Micro

Introduced in: v20.5.0

Converts a Unix timestamp in microseconds to a DateTime64 value with microsecond precision.

The input value is treated as a Unix timestamp with microsecond precision (number of microseconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Micro(value[, timezone])

Arguments

• value — Unix timestamp in microseconds. Int64
• timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with microsecond precision. DateTime64(6)

Examples

Usage example

SELECT fromUnixTimestamp64Micro(1640995200123456)

┌─fromUnixTimestamp64Micro(1640995200123456)─┐
│                 2022-01-01 00:00:00.123456 │
└────────────────────────────────────────────┘

fromUnixTimestamp64Milli

Introduced in: v20.5.0

Converts a Unix timestamp in milliseconds to a DateTime64 value with millisecond precision.

The input value is treated as a Unix timestamp with millisecond precision (number of milliseconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Milli(value[, timezone])

Arguments

• value — Unix timestamp in milliseconds. Int64
• timezone — Optional. Timezone for the returned value. String

Returned value

A DateTime64 value with millisecond precision. DateTime64(3)

Examples

Usage example

SELECT fromUnixTimestamp64Milli(1640995200123)

┌─fromUnixTimestamp64Milli(1640995200123)─┐
│                 2022-01-01 00:00:00.123 │
└─────────────────────────────────────────┘

fromUnixTimestamp64Nano

Introduced in: v20.5.0

Converts a Unix timestamp in nanoseconds to a DateTime64 value with nanosecond precision.

The input value is treated as a Unix timestamp with nanosecond precision (number of nanoseconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Nano(value[, timezone])

Arguments

• value — Unix timestamp in nanoseconds. Int64
• timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with nanosecond precision. DateTime64(9)

Examples

Usage example

SELECT fromUnixTimestamp64Nano(1640995200123456789)

┌─fromUnixTimestamp64Nano(1640995200123456789)─┐
│                2022-01-01 00:00:00.123456789 │
└──────────────────────────────────────────────┘

fromUnixTimestamp64Second

Introduced in: v24.12.0

Converts a Unix timestamp in seconds to a DateTime64 value with second precision.

The input value is treated as a Unix timestamp with second precision (number of seconds since 1970-01-01 00:00:00 UTC).

Syntax

fromUnixTimestamp64Second(value[, timezone])

Arguments

• value — Unix timestamp in seconds. Int64
• timezone — Optional. Timezone for the returned value. String

Returned value

Returns a DateTime64 value with second precision. DateTime64(0)

Examples

Usage example

SELECT fromUnixTimestamp64Second(1640995200)

┌─fromUnixTimestamp64Second(1640995200)─┐
│                   2022-01-01 00:00:00 │
└───────────────────────────────────────┘

kqlParameterCast

Introduced in: v26.9.0

Checks a value against a declared Kusto Query Language type and converts it to that type’s ClickHouse carrier: an argument whose type does not belong to the declared KQL type is rejected during analysis, and a value that does not fit (an int overflow, say) is a per-row error. The second argument is the declared KQL type name and the third describes the parameter for the error message; both must be constant strings.

This function backs typed function parameters when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlParameterCast(value, type, parameter)

Arguments

• value — The argument a KQL function call supplies. - type — The declared KQL type name, a constant string. - parameter — A description of the parameter, a constant string used in the error message.

Returned value

value as the declared type’s ClickHouse carrier.

Examples

long

SELECT kqlParameterCast(5, 'long', 'the parameter') AS x, toTypeName(x)

5	Int64

real

SELECT kqlParameterCast(5, 'real', 'the parameter') AS x, toTypeName(x)

5	Float64

kqlToTimespan

Introduced in: v26.8.0

Converts a value to a timespan (an Interval in nanoseconds) as the Kusto Query Language’s totimespan() does: a timespan passes through, a number counts days, and a string is read as [-][d.]hh:mm:ss[.fffffff] - converting to NULL when it does not parse.

This function backs totimespan() when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlToTimespan(x)

Arguments

• x — A timespan, a number of days, or a string spelling a timespan.

Returned value

The value as an interval of nanoseconds; NULL for a string that does not spell a timespan.

Examples

string

SELECT kqlToTimespan('0.00:01:00')

60000000000

days

SELECT kqlToTimespan(2)

172800000000000

parseDateTime

Introduced in: v23.3.0

Parses a date and time string according to a MySQL date format string.

This function is the inverse of formatDateTime. It parses a String argument using a format String. Returns a DateTime type.

Syntax

parseDateTime(time_string, format[, timezone])

Aliases: TO_UNIXTIME

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns a DateTime parsed from the input string according to the MySQL style format string. DateTime

Examples

Usage example

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

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

parseDateTime32BestEffort

Introduced in: v20.9.0

Converts a string representation of a date and time to the DateTime data type.

The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse’s and some other date and time formats.

Syntax

parseDateTime32BestEffort(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed String

Returned value

Returns time_string as a DateTime. DateTime

Examples

Usage example

SELECT parseDateTime32BestEffort('23/10/2025 12:12:57')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2025-10-23 12:12:57 │
└───────────────────────────┘

With timezone

SELECT parseDateTime32BestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2025-08-18 10:22:16 │
└───────────────────────────┘

Unix timestamp

SELECT parseDateTime32BestEffort('1284101485')
AS parseDateTime32BestEffort

┌─parseDateTime32BestEffort─┐
│       2010-09-10 06:51:25 │
└───────────────────────────┘

parseDateTime32BestEffortOrNull

Introduced in: v20.9.0

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

Syntax

parseDateTime32BestEffortOrNull(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns a DateTime object parsed from the string, or NULL if the parsing fails. DateTime

Examples

Usage example

SELECT
    parseDateTime32BestEffortOrNull('23/10/2025 12:12:57') AS valid,
    parseDateTime32BestEffortOrNull('invalid date') AS invalid

┌───────────────valid─┬─invalid─┐
│ 2025-10-23 12:12:57 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTime32BestEffortOrZero

Introduced in: v20.9.0

Same as parseDateTime32BestEffort except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime32BestEffortOrZero(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns a DateTime object parsed from the string, or zero date (1970-01-01 00:00:00) if the parsing fails. DateTime

Examples

Usage example

SELECT
    parseDateTime32BestEffortOrZero('23/10/2025 12:12:57') AS valid,
    parseDateTime32BestEffortOrZero('invalid date') AS invalid

┌───────────────valid─┬─────────────invalid─┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTime64

Introduced in: v24.11.0

Parses a date and time string with sub-second precision according to a MySQL date format string.

This function is the inverse of formatDateTime for DateTime64. It parses a String argument using a format String. Returns a DateTime64 type which can represent dates from 0000 to 9999 with sub-second precision (high precisions cover a narrower range because the value is stored in an Int64 number of ticks: precision 8 reaches about 4892 and precision 9 spans 1677 to 2262).

Syntax

parseDateTime64(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns a DateTime64 parsed from the input string according to the MySQL style format string. DateTime64

Examples

Usage example

SELECT parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')

┌─parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│                                         2025-01-04 23:00:00.123000 │
└────────────────────────────────────────────────────────────────────┘

parseDateTime64BestEffort

Introduced in: v20.1.0

Same as parseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime64 data type.

Syntax

parseDateTime64BestEffort(time_string[, precision[, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to the DateTime64 data type. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffort('2025-01-01') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',6) AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',3,'Asia/Istanbul') AS a, toTypeName(a) AS t
FORMAT PrettyCompactMonoBlock

┌──────────────────────────a─┬─t──────────────────────────────┐
│ 2025-01-01 01:01:00.123000 │ DateTime64(3)                  │
│ 2025-01-01 00:00:00.000000 │ DateTime64(3)                  │
│ 2025-01-01 01:01:00.123460 │ DateTime64(6)                  │
│ 2025-12-31 22:01:00.123000 │ DateTime64(3, 'Asia/Istanbul') │
└────────────────────────────┴────────────────────────────────┘

parseDateTime64BestEffortOrNull

Introduced in: v20.1.0

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

Syntax

parseDateTime64BestEffortOrNull(time_string[, precision[, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64, or NULL if the input cannot be parsed. DateTime64 or NULL

Examples

Usage example

SELECT parseDateTime64BestEffortOrNull('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrNull('invalid') AS invalid

┌───────────────────valid─┬─invalid─┐
│ 2025-01-01 01:01:00.123 │    ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘

parseDateTime64BestEffortOrZero

Introduced in: v20.1.0

Same as parseDateTime64BestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortOrZero(time_string[, precision[, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64, or zero date/datetime (1970-01-01 00:00:00.000) if the input cannot be parsed. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortOrZero('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrZero('invalid') AS invalid

┌───────────────────valid─┬─────────────────invalid─┐
│ 2025-01-01 01:01:00.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

parseDateTime64BestEffortUS

Introduced in: v22.8.0

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity.

Syntax

parseDateTime64BestEffortUS(time_string [, precision [, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US date format preference for ambiguous cases. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortUS('02/10/2025 12:30:45.123') AS us_format,
       parseDateTime64BestEffortUS('15/08/2025 10:15:30.456') AS fallback_to_standard

┌───────────────us_format─┬────fallback_to_standard─┐
│ 2025-02-10 12:30:45.123 │ 2025-08-15 10:15:30.456 │
└─────────────────────────┴─────────────────────────┘

parseDateTime64BestEffortUSOrNull

Introduced in: v22.8.0

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns NULL when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortUSOrNull(time_string[, precision[, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US format preference, or NULL if the input cannot be parsed. DateTime64 or NULL

Examples

Usage example

SELECT parseDateTime64BestEffortUSOrNull('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrNull('invalid') AS invalid

┌────────────────valid_us─┬─invalid─┐
│ 2025-02-10 12:30:45.123 │    ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘

parseDateTime64BestEffortUSOrZero

Introduced in: v22.8.0

Same as parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns zero date or zero date time when it encounters a date format that cannot be processed.

Syntax

parseDateTime64BestEffortUSOrZero(time_string [, precision [, time_zone]])

Arguments

• time_string — String containing a date or date with time to convert. String
• precision — Optional. Required precision. 3 for milliseconds, 6 for microseconds. Default: 3. UInt8
• time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns time_string converted to DateTime64 using US format preference, or zero date/datetime (1970-01-01 00:00:00.000) if the input cannot be parsed. DateTime64

Examples

Usage example

SELECT parseDateTime64BestEffortUSOrZero('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrZero('invalid') AS invalid

┌────────────────valid_us─┬─────────────────invalid─┐
│ 2025-02-10 12:30:45.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

parseDateTime64InJodaSyntax

Introduced in: v24.10.0

Parses a date and time string with sub-second precision according to a Joda date format string.

This function is the inverse of formatDateTimeInJodaSyntax for DateTime64. It parses a String argument using a Joda-style format String. Returns a DateTime64 type which can represent dates from 0000 to 9999 with sub-second precision (high precisions cover a narrower range because the value is stored in an Int64 number of ticks: precision 8 reaches about 4892 and precision 9 spans 1677 to 2262).

Refer to Joda Time documentation for the format patterns.

Syntax

parseDateTime64InJodaSyntax(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns a DateTime64 parsed from the input string according to the Joda style format string. DateTime64

Examples

Usage example

SELECT parseDateTime64InJodaSyntax('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')

┌─parseDateTime64InJodaSyntax('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│                                                           2025-01-04 23:00:00.123 │
└───────────────────────────────────────────────────────────────────────────────────┘

parseDateTime64InJodaSyntaxOrNull

Introduced in: v24.10.0

Same as parseDateTime64InJodaSyntax but returns NULL when it encounters an unparsable date format.

Syntax

parseDateTime64InJodaSyntaxOrNull(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime64 parsed from input string, or NULL if parsing fails. Nullable(DateTime64)

Examples

Usage example

SELECT parseDateTime64InJodaSyntaxOrNull('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')

┌─parseDateTime64InJodaSyntaxOrNull('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│                                                                 2025-01-04 23:00:00.123 │
└─────────────────────────────────────────────────────────────────────────────────────────┘

parseDateTime64InJodaSyntaxOrZero

Introduced in: v24.10.0

Same as parseDateTime64InJodaSyntax but returns zero date when it encounters an unparsable date format.

Syntax

parseDateTime64InJodaSyntaxOrZero(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime64 parsed from input string, or zero DateTime64 if parsing fails. DateTime64

Examples

Usage example

SELECT parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')

┌─parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│                                                                 2025-01-04 23:00:00.123 │
└─────────────────────────────────────────────────────────────────────────────────────────┘

parseDateTime64OrNull

Introduced in: v24.11.0

Same as parseDateTime64 but returns NULL when it encounters an unparsable date format.

Syntax

parseDateTime64OrNull(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime64 parsed from input string, or NULL if parsing fails. Nullable(DateTime64)

Examples

Usage example

SELECT parseDateTime64OrNull('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')

┌─parseDateTime64OrNull('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│                                               2025-01-04 23:00:00.123000 │
└──────────────────────────────────────────────────────────────────────────┘

parseDateTime64OrZero

Introduced in: v24.11.0

Same as parseDateTime64 but returns zero date when it encounters an unparsable date format.

Syntax

parseDateTime64OrZero(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime64. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime64 parsed from input string, or zero DateTime64 if parsing fails. DateTime64

Examples

Usage example

SELECT parseDateTime64OrZero('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')

┌─parseDateTime64OrZero('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│                                               2025-01-04 23:00:00.123000 │
└──────────────────────────────────────────────────────────────────────────┘

parseDateTimeBestEffort

Introduced in: v1.1.0

Converts a date and time in the String representation to DateTime data type. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse’s and some other date and time formats.

Supported non-standard formats:

• A string containing 9..10 digit unix timestamp.
• A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
• A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
• A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
• A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
• A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffort(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffort('23/10/2025 12:12:57') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-10-23 12:12:57 │
└─────────────────────────┘

With timezone

SELECT parseDateTimeBestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-08-18 10:22:16 │
└─────────────────────────┘

Unix timestamp

SELECT parseDateTimeBestEffort('1735689600') AS parseDateTimeBestEffort

┌─parseDateTimeBestEffort─┐
│     2025-01-01 00:00:00 │
└─────────────────────────┘

parseDateTimeBestEffortOrNull

Introduced in: v1.1.0

The same as parseDateTimeBestEffort except that it returns NULL when it encounters a date format that cannot be processed. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse’s and some other date and time formats.

Supported non-standard formats:

• A string containing 9..10 digit unix timestamp.
• A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
• A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
• A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
• A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
• A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffortOrNull(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime, or NULL if the input cannot be parsed. DateTime or NULL

Examples

Usage example

SELECT parseDateTimeBestEffortOrNull('23/10/2025 12:12:57') AS valid,
       parseDateTimeBestEffortOrNull('invalid') AS invalid

┌───────────────valid─┬─invalid─┐
│ 2025-10-23 12:12:57 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTimeBestEffortOrZero

Introduced in: v1.1.0

Same as parseDateTimeBestEffort except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed. The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouse’s and some other date and time formats.

Supported non-standard formats:

• A string containing 9..10 digit unix timestamp.
• A string with a date and a time component: YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss, etc.
• A string with a date, but no time component: YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY etc.
• A string with a day and time: DD, DD hh, DD hh:mm. In this case MM is substituted by 01.
• A string that includes the date and time along with time zone offset information: YYYY-MM-DD hh:mm:ss ±h:mm, etc.
• A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. If the year is not specified, it is considered to be equal to the current year.

Syntax

parseDateTimeBestEffortOrZero(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime, or zero date/datetime (1970-01-01 or 1970-01-01 00:00:00) if the input cannot be parsed. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortOrZero('23/10/2025 12:12:57') AS valid,
       parseDateTimeBestEffortOrZero('invalid') AS invalid

┌───────────────valid─┬─────────────invalid─┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTimeBestEffortUS

Introduced in: v1.1.0

This function behaves like parseDateTimeBestEffort for ISO date formats, e.g. YYYY-MM-DD hh:mm:ss, and other date formats where the month and date components can be unambiguously extracted, e.g. YYYYMMDDhhmmss, YYYY-MM, DD hh, or YYYY-MM-DD hh:mm:ss ±h:mm. If the month and the date components cannot be unambiguously extracted, e.g. MM/DD/YYYY, MM-DD-YYYY, or MM-DD-YY, it prefers the US date format instead of DD/MM/YYYY, DD-MM-YYYY, or DD-MM-YY. As an exception to the previous statement, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of parseDateTimeBestEffort, e.g. 15/08/2020 is parsed as 2020-08-15.

Syntax

parseDateTimeBestEffortUS(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US date format preference for ambiguous cases. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortUS('02/10/2025') AS us_format,
       parseDateTimeBestEffortUS('15/08/2025') AS fallback_to_standard

┌───────────us_format─┬─fallback_to_standard─┐
│ 2025-02-10 00:00:00 │  2025-08-15 00:00:00 │
└─────────────────────┴──────────────────────┘

parseDateTimeBestEffortUSOrNull

Introduced in: v1.1.0

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

This function behaves like parseDateTimeBestEffort for ISO date formats, but prefers the US date format for ambiguous cases, with NULL return on parsing errors.

Syntax

parseDateTimeBestEffortUSOrNull(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US format preference, or NULL if the input cannot be parsed. DateTime or NULL

Examples

Usage example

SELECT parseDateTimeBestEffortUSOrNull('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrNull('invalid') AS invalid

┌────────────valid_us─┬─invalid─┐
│ 2025-02-10 00:00:00 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘

parseDateTimeBestEffortUSOrZero

Introduced in: v1.1.0

Same as parseDateTimeBestEffortUS function except that it returns zero date (1970-01-01) or zero date with time (1970-01-01 00:00:00) when it encounters a date format that cannot be processed.

This function behaves like parseDateTimeBestEffort for ISO date formats, but prefers the US date format for ambiguous cases, with zero return on parsing errors.

Syntax

parseDateTimeBestEffortUSOrZero(time_string[, time_zone])

Arguments

• time_string — String containing a date and time to convert. String
• time_zone — Optional. Time zone according to which time_string is parsed. String

Returned value

Returns time_string as a DateTime using US format preference, or zero date/datetime (1970-01-01 or 1970-01-01 00:00:00) if the input cannot be parsed. DateTime

Examples

Usage example

SELECT parseDateTimeBestEffortUSOrZero('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrZero('invalid') AS invalid

┌────────────valid_us─┬─────────────invalid─┐
│ 2025-02-10 00:00:00 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

parseDateTimeInJodaSyntax

Introduced in: v23.3.0

Parses a date and time string according to a Joda date format string.

This function is the inverse of formatDateTimeInJodaSyntax. It parses a String argument using a Joda-style format String. Returns a DateTime type.

Refer to Joda Time documentation for the format patterns.

Syntax

parseDateTimeInJodaSyntax(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns a DateTime parsed from the input string according to the Joda style format string. DateTime

Examples

Usage example

SELECT parseDateTimeInJodaSyntax('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')

┌─parseDateTimeInJodaSyntax('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│                                                     2025-01-04 23:00:00 │
└─────────────────────────────────────────────────────────────────────────┘

parseDateTimeInJodaSyntaxOrNull

Introduced in: v23.3.0

Same as parseDateTimeInJodaSyntax but returns NULL when it encounters an unparsable date format.

Syntax

parseDateTimeInJodaSyntaxOrNull(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or NULL if parsing fails. Nullable(DateTime)

Examples

Usage example

SELECT parseDateTimeInJodaSyntaxOrNull('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')

┌─parseDateTimeInJodaSyntaxOrNull('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│                                                           2025-01-04 23:00:00 │
└───────────────────────────────────────────────────────────────────────────────┘

parseDateTimeInJodaSyntaxOrZero

Introduced in: v23.3.0

Same as parseDateTimeInJodaSyntax but returns zero date when it encounters an unparsable date format.

Syntax

parseDateTimeInJodaSyntaxOrZero(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string in Joda syntax specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or zero DateTime if parsing fails. DateTime

Examples

Usage example

SELECT parseDateTimeInJodaSyntaxOrZero('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')

┌─parseDateTimeInJodaSyntaxOrZero('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│                                                           2025-01-04 23:00:00 │
└───────────────────────────────────────────────────────────────────────────────┘

parseDateTimeOrNull

Introduced in: v23.3.0

Same as parseDateTime but returns NULL when it encounters an unparsable date format.

Syntax

parseDateTimeOrNull(time_string, format[, timezone])

Aliases: str_to_date

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or NULL if parsing fails. Nullable(DateTime)

Examples

Usage example

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

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

parseDateTimeOrZero

Introduced in: v23.3.0

Same as parseDateTime but returns zero date when it encounters an unparsable date format.

Syntax

parseDateTimeOrZero(time_string, format[, timezone])

Arguments

• time_string — String to be parsed into DateTime. String
• format — Format string specifying how to parse time_string. String
• timezone — Optional. Timezone. String

Returned value

Returns DateTime parsed from input string, or zero DateTime if parsing fails. DateTime

Examples

Usage example

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

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

reinterpret

Introduced in: v1.1.0

Uses the same source in-memory bytes sequence for the provided value x and reinterprets it to the destination type. When the destination type is String, a source Array is supported only if its element type is fixed size and contiguous in memory (for example, Array(UInt8) or Array(FixedString(2))); its element bytes are copied verbatim in their native in-memory order, so the result can be reinterpreted back to the original Array type on any architecture. Arrays whose elements are variable-length (for example, Array(String)) or nullable (for example, Array(Nullable(Int32))) are not supported.

Syntax

reinterpret(x, type)

Arguments

• x — Value to reinterpret. When reinterpreting to String, a source Array must have fixed-size, contiguous elements. Any
• type — Destination type. If it is an array, then the array element type must be a fixed length type. String

Returned value

Destination type value. Any

Examples

Usage example

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

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

Array example

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

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

Array to String example

SELECT hex(reinterpret([toUInt8(1), toUInt8(2), toUInt8(255)]::Array(UInt8), 'String')) AS array_of_UInt8_to_string

┌─array_of_UInt8_to_string─┐
│ 0102FF                   │
└──────────────────────────┘

reinterpretAsDate

Introduced in: v1.1.0

Reinterprets the input value as a Date value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01

Syntax

reinterpretAsDate(x)

Arguments

• x — Number of days since the beginning of the Unix Epoch. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Date. Date

Examples

Usage example

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

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

reinterpretAsDateTime

Introduced in: v1.1.0

Reinterprets the input value as a DateTime value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01

Syntax

reinterpretAsDateTime(x)

Arguments

• x — Number of seconds since the beginning of the Unix Epoch. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Date and Time. DateTime

Examples

Usage example

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

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

reinterpretAsFixedString

Introduced in: v1.1.0

Reinterprets the input value as a fixed string (assuming little endian order). Null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character.

Syntax

reinterpretAsFixedString(x)

Arguments

• x — Value to reinterpret to string. (U)Int* or Float* or Date or DateTime

Returned value

Fixed string containing bytes representing x. FixedString

Examples

Usage example

-- The result is a sequence of bytes, so it is shown with `hex`.
SELECT
    hex(reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))),
    hex(reinterpretAsFixedString(toDate('1970-03-07')))

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

reinterpretAsFloat32

Introduced in: v1.1.0

Reinterprets the input value as a value of type Float32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsFloat32(x)

Arguments

• x — Value to reinterpret as Float32. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Float32

Examples

Usage example

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

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

reinterpretAsFloat64

Introduced in: v1.1.0

Reinterprets the input value as a value of type Float64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsFloat64(x)

Arguments

• x — Value to reinterpret as Float64. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Float64

Examples

Usage example

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

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

reinterpretAsInt128

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt128(x)

Arguments

• x — Value to reinterpret as Int128. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int128

Examples

Usage example

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

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

reinterpretAsInt16

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt16(x)

Arguments

• x — Value to reinterpret as Int16. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int16

Examples

Usage example

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

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

reinterpretAsInt256

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt256(x)

Arguments

• x — Value to reinterpret as Int256. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int256

Examples

Usage example

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

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

reinterpretAsInt32

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt32(x)

Arguments

• x — Value to reinterpret as Int32. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int32

Examples

Usage example

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

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

reinterpretAsInt64

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt64(x)

Arguments

• x — Value to reinterpret as Int64. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int64

Examples

Usage example

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

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

reinterpretAsInt8

Introduced in: v1.1.0

Reinterprets the input value as a value of type Int8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsInt8(x)

Arguments

• x — Value to reinterpret as Int8. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. Int8

Examples

Usage example

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

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

reinterpretAsString

Introduced in: v1.1.0

Reinterprets the input value as a string. For scalar values, the byte order is normalized to little endian, and null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character. For an Array of fixed-size elements, the element bytes are copied verbatim in their native in-memory order, without trimming trailing zero bytes and without normalizing to little endian, so the result can be reinterpreted back to the original Array type on any architecture.

Syntax

reinterpretAsString(x)

Arguments

• x — Value to reinterpret to string. (U)Int* or Float* or Date or DateTime or Array

Returned value

String containing bytes representing x. String

Examples

Usage example

-- The result is a sequence of bytes, so it is shown with `hex`.
SELECT
    hex(reinterpretAsString(toDateTime('1970-01-01 01:01:05'))),
    hex(reinterpretAsString(toDate('1970-03-07')))

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

Array to String example

SELECT hex(reinterpretAsString([toUInt8(1), toUInt8(2), toUInt8(255)]::Array(UInt8))) AS array_of_UInt8_to_string

┌─array_of_UInt8_to_string─┐
│ 0102FF                   │
└──────────────────────────┘

reinterpretAsUInt128

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt128(x)

Arguments

• x — Value to reinterpret as UInt128. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. UInt128

Examples

Usage example

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

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

reinterpretAsUInt16

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt16(x)

Arguments

• x — Value to reinterpret as UInt16. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. UInt16

Examples

Usage example

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

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

reinterpretAsUInt256

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt256(x)

Arguments

• x — Value to reinterpret as UInt256. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. UInt256

Examples

Usage example

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

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

reinterpretAsUInt32

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt32(x)

Arguments

• x — Value to reinterpret as UInt32. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. UInt32

Examples

Usage example

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

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

reinterpretAsUInt64

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt64(x)

Arguments

• x — Value to reinterpret as UInt64. Int* or UInt* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value of x. UInt64

Examples

Usage example

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

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

reinterpretAsUInt8

Introduced in: v1.1.0

Reinterprets the input value as a value of type UInt8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.

Syntax

reinterpretAsUInt8(x)

Arguments

• x — Value to reinterpret as UInt8. (U)Int* or Float* or Date or DateTime or UUID or String or FixedString

Returned value

Returns the reinterpreted value x. UInt8

Examples

Usage example

SELECT
    toInt8(-1) AS val,
    toTypeName(val),
    reinterpretAsUInt8(val) AS res,
    toTypeName(res);

┌─val─┬─toTypeName(val)─┬─res─┬─toTypeName(res)─┐
│  -1 │ Int8            │ 255 │ UInt8           │
└─────┴─────────────────┴─────┴─────────────────┘

reinterpretAsUUID

Introduced in: v1.1.0

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

Syntax

reinterpretAsUUID(fixed_string)

Arguments

• fixed_string — Big-endian byte string. FixedString

Returned value

The UUID type value. UUID

Examples

String to UUID

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

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

toBFloat16

Introduced in: v1.1.0

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

See also:

• toBFloat16OrZero.
• toBFloat16OrNull.

Syntax

toBFloat16(expr)

Arguments

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

Returned value

Returns a 16-bit brain-float value. BFloat16

Examples

Usage example

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

Row 1:
──────
toBFloat16(toFloat32(42.7)):   42.5
toBFloat16(toFloat32('42.7')): 42.5
toBFloat16('42.7'):            42.5

toBFloat16OrNull

Introduced in: v1.1.0

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

Supported arguments:

• String representations of numeric values.

Unsupported arguments (return NULL):

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

See also:

• toBFloat16.
• toBFloat16OrZero.

Syntax

toBFloat16OrNull(x)

Arguments

• x — A String representation of a number. String

Returned value

Reurns a 16-bit brain-float value, otherwise NULL. BFloat16 or NULL

Examples

Usage example

SELECT toBFloat16OrNull('0x5E'), -- unsupported arguments
       toBFloat16OrNull('12.3'), -- typical use
       toBFloat16OrNull('12.3456789') -- silent loss of precision

\N	12.25	12.3125

toBFloat16OrZero

Introduced in: v1.1.0

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

Supported arguments:

• String representations of numeric values.

Unsupported arguments (return 0):

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

See also:

• toBFloat16.
• toBFloat16OrNull.

Syntax

toBFloat16OrZero(x)

Arguments

• x — A String representation of a number. String

Returned value

Returns a 16-bit brain-float value, otherwise 0. BFloat16

Examples

Usage example

SELECT toBFloat16OrZero('0x5E'), -- unsupported arguments
       toBFloat16OrZero('12.3'), -- typical use
       toBFloat16OrZero('12.3456789') -- silent loss of precision

0	12.25	12.3125

toBool

Introduced in: v22.2.0

Converts an input value to a value of type Bool.

Syntax

toBool(expr)

Arguments

• expr — Expression returning a number or a string. For strings, accepts ‘true’ or ‘false’ (case-insensitive). (U)Int* or Float* or String or Expression

Returned value

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

Examples

Usage example

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

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

toDate

Introduced in: v1.1.0

Converts an input value to type Date. Supports conversion from String, FixedString, DateTime, or numeric types.

Syntax

toDate(x)

Arguments

• x — Input value to convert. String or FixedString or DateTime or (U)Int* or Float*

Returned value

Returns the converted input value. Date

Examples

String to Date conversion

SELECT toDate('2025-04-15')

2025-04-15

DateTime to Date conversion

SELECT toDate(toDateTime('2025-04-15 10:30:00'))

2025-04-15

Integer to Date conversion

SELECT toDate(20297)

2025-07-28

toDate32

Introduced in: v21.9.0

Converts the argument to the Date32 data type. If the value is outside the [0000-01-01, 9999-12-31] range, toDate32 returns the border values supported by Date32. If the argument is of type Date, it’s bounds are taken into account.

Syntax

toDate32(expr)

Arguments

• expr — The value to convert. String or UInt32 or Date

Returned value

Returns a calendar date. Date32

Examples

Within range

SELECT toDate32('2025-01-01') AS value, toTypeName(value)
FORMAT Vertical

Row 1:
──────
value:             2025-01-01
toTypeName(value): Date32

Before 1900

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

Row 1:
──────
value:             1899-01-01
toTypeName(value): Date32

toDate32OrDefault

Introduced in: v21.11.0

Converts the argument to the Date32 data type. If the argument cannot be converted, which includes a numeric value outside of the range of Date32, the function returns a default value which is either the second argument (if specified), or otherwise 1900-01-01, the historical default value of Date32 (not its lower boundary, which is 0000-01-01). If the argument has Date type, its borders are taken into account.

Note that, unlike toDate32, this function does not saturate an out-of-range numeric argument to the boundaries of Date32; such an argument is treated as unconvertible.

Syntax

toDate32OrDefault(expr[, default])

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• default — Optional. The default value to return if parsing is unsuccessful. Date32

Returned value

Value of type Date32 if successful, otherwise returns the default value if passed or 1900-01-01 if not. Date32

Examples

Successful conversion

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

1930-01-01

Failed conversion

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

2020-01-01

Out-of-range number

SELECT toDate32OrDefault(toUInt64(18446744073709551615), toDate32('2020-01-01'))

2020-01-01

toDate32OrNull

Introduced in: v21.9.0

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

Syntax

toDate32OrNull(x)

Arguments

• x — A string representation of a date. String

Returned value

Returns a Date32 value if successful, otherwise NULL. Date32 or NULL

Examples

Usage example

SELECT toDate32OrNull('2025-01-01'), toDate32OrNull('invalid')

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

toDate32OrZero

Introduced in: v21.9.0

Converts an input value to a value of type Date32 but returns the default value 1900-01-01 if an invalid argument is received. The same as toDate32 but returns 1900-01-01 if an invalid argument is received. Note that 1900-01-01 is a historical default value for invalid input, not the lower boundary of Date32, which is 0000-01-01.

See also:

• toDate32
• toDate32OrNull
• toDate32OrDefault

Syntax

toDate32OrZero(x)

Arguments

• x — A string representation of a date. String

Returned value

Returns a Date32 value if successful, otherwise the historical default value for invalid input (1900-01-01). Date32

Examples

Usage example

SELECT toDate32OrZero('2025-01-01'), toDate32OrZero('')

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

toDateOrDefault

Introduced in: v21.11.0

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

Syntax

toDateOrDefault(expr[, default])

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• default — Optional. The default value to return if parsing is unsuccessful. Date

Returned value

Value of type Date if successful, otherwise returns the default value if passed or 1970-01-01 if not. Date

Examples

Successful conversion

SELECT toDateOrDefault('2022-12-30')

2022-12-30

Failed conversion

SELECT toDateOrDefault('', CAST('2023-01-01', 'Date'))

2023-01-01

toDateOrNull

Introduced in: v1.1.0

Converts an input value to a value of type Date but returns NULL if an invalid argument is received. The same as toDate but returns NULL if an invalid argument is received. An integer argument is interpreted the same way as by toDate (a number of days since 1970-01-01 if it does not exceed 65535, a Unix timestamp otherwise), and produces NULL if it is out of range of the Date type.

Syntax

toDateOrNull(x)

Arguments

• x — A string representation of a date, or an integer number of days or a Unix timestamp. String or (U)Int8/16/32/64

Returned value

Returns a Date value if successful, otherwise NULL. Date or NULL

Examples

Usage example

SELECT toDateOrNull('2025-12-30'), toDateOrNull('invalid')

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

toDateOrZero

Introduced in: v1.1.0

Converts an input value to a value of type Date but returns the lower boundary of Date if an invalid argument is received. The same as toDate but returns lower boundary of Date if an invalid argument is received.

See also:

• toDate
• toDateOrNull
• toDateOrDefault

Syntax

toDateOrZero(x)

Arguments

• x — A string representation of a date. String

Returned value

Returns a Date value if successful, otherwise the lower boundary of Date (1970-01-01). Date

Examples

Usage example

SELECT toDateOrZero('2025-12-30'), toDateOrZero('')

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

toDateTime

Introduced in: v1.1.0

Converts an input value to type DateTime.

Syntax

toDateTime(expr[, time_zone])

Arguments

• expr — The value. String or Int or Date or DateTime
• time_zone — Time zone. String

Returned value

Returns a date time. DateTime

Examples

Usage example

SELECT toDateTime('2025-01-01 00:00:00'), toDateTime(1735689600, 'UTC')
FORMAT Vertical

Row 1:
──────
toDateTime('2025-01-01 00:00:00'): 2025-01-01 00:00:00
toDateTime(1735689600, 'UTC'):     2025-01-01 00:00:00

toDateTime32

Introduced in: v20.9.0

Converts an input value to type DateTime. Supports conversion from String, FixedString, Date, Date32, Time, DateTime, DateTime64, or numeric types ((U)Int8, (U)Int16, (U)Int32, (U)Int64, BFloat16, Float32, Float64). Decimal and Time64 values are not supported and result in an exception; the Time type and wide integer types such as (U)Int128/(U)Int256 wrap around instead of saturating. DateTime32 is an alias of DateTime and supports dates from 1970-01-01 00:00:00 to 2106-02-07 06:28:15. When converting from a string or from one of the saturating numeric types listed above, values outside of this range are saturated to the range boundaries. Non-finite floating-point values (NaN, inf, -inf) cannot be converted and result in an exception. Conversions from other date and time types such as Date32 or DateTime64 follow the date_time_overflow_behavior setting: the default ignore mode may produce wrapped-around values, saturate clamps them to the range boundaries, and throw throws an exception.

Syntax

toDateTime32(x[, timezone])

Arguments

• x — Input value to convert. String or FixedString or (U)Int* or Float* or BFloat16 or Date or Date32 or Time or DateTime or DateTime64
• timezone — Optional. Timezone for the returned DateTime value. String

Returned value

Returns the converted input value. DateTime

Examples

From a string

SELECT toDateTime32('2025-01-01 00:00:00') AS value, toTypeName(value);

┌───────────────value─┬─toTypeName(value)─┐
│ 2025-01-01 00:00:00 │ DateTime          │
└─────────────────────┴───────────────────┘

From a Unix timestamp

SELECT toDateTime32(1735689600, 'UTC') AS value, toTypeName(value);

┌───────────────value─┬─toTypeName(value)─┐
│ 2025-01-01 00:00:00 │ DateTime('UTC')   │
└─────────────────────┴───────────────────┘

With a timezone

SELECT toDateTime32('2025-01-01 00:00:00', 'Asia/Istanbul') AS value, toTypeName(value);

┌───────────────value─┬─toTypeName(value)─────────┐
│ 2025-01-01 00:00:00 │ DateTime('Asia/Istanbul') │
└─────────────────────┴───────────────────────────┘

toDateTime64

Introduced in: v20.1.0

Converts an input value to a value of type DateTime64.

Syntax

toDateTime64(expr, scale[, timezone])

Arguments

• expr — Expression returning a number or a string representation of a number. Expression
• scale — Tick size (precision): 10^(-scale) seconds. UInt8
• timezone — Optional. Time zone for the specified DateTime64 object. String

Returned value

Returns a calendar date and time of day, with sub-second precision. DateTime64

Examples

The value is within the range

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

┌───────────────────value─┬─toTypeName(value)─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3)     │
└─────────────────────────┴───────────────────┘

As decimal with precision

SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);
-- Without the decimal point the value is still treated as Unix Timestamp in seconds
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);

┌───────────────────value─┬─toTypeName(value)─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3)     │
└─────────────────────────┴───────────────────┘
┌───────────────────value─┬─toTypeName(value)─┐
│ 9999-12-31 23:59:59.000 │ DateTime64(3)     │
└─────────────────────────┴───────────────────┘

With timezone

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

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

toDateTime64OrDefault

Introduced in: v21.11.0

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

Syntax

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

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• scale — Tick size (precision): 10^-precision seconds. UInt8
• timezone — Optional. Time zone. String
• default — Optional. The default value to return if parsing is unsuccessful. DateTime64

Returned value

Value of type DateTime64 if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00.000 if not. DateTime64

Examples

Successful conversion

SELECT toDateTime64OrDefault('1976-10-18 00:00:00.30', 3)

1976-10-18 00:00:00.300

Failed conversion

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

2001-01-01 00:00:00.000

toDateTime64OrNull

Introduced in: v20.1.0

Converts an input value to a value of type DateTime64 but returns NULL if an invalid argument is received. The same as toDateTime64 but returns NULL if an invalid argument is received. An integer argument is interpreted as a Unix timestamp in whole seconds and produces NULL if it is out of range of the DateTime64 type.

Syntax

toDateTime64OrNull(x[, precision[, timezone]])

Arguments

• x — A string representation of a date with time and subsecond precision, or an integer Unix timestamp. String or (U)Int8/16/32/64
• precision — Optional. The subsecond precision of the returned value. UInt8
• timezone — Optional. Time zone of the returned value. String

Returned value

Returns a DateTime64 value if successful, otherwise NULL. DateTime64 or NULL

Examples

Usage example

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

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

toDateTime64OrZero

Introduced in: v20.1.0

Converts an input value to a value of type DateTime64 but returns the zero DateTime64 value (the Unix epoch, 1970-01-01 00:00:00) if an invalid argument is received. The same as toDateTime64 but returns the zero DateTime64 value if an invalid argument is received.

See also:

• toDateTime64.
• toDateTime64OrNull.
• toDateTime64OrDefault.

Syntax

toDateTime64OrZero(x[, precision[, timezone]])

Arguments

• x — A string representation of a date with time and subsecond precision. String
• precision — Optional. The subsecond precision of the returned value. UInt8
• timezone — Optional. Time zone of the returned value. String

Returned value

Returns a DateTime64 value if successful, otherwise the zero DateTime64 value (1970-01-01 00:00:00) at the requested precision. DateTime64

Examples

Usage example

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

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

toDateTimeOrDefault

Introduced in: v21.11.0

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

Syntax

toDateTimeOrDefault(expr[, timezone, default])

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• timezone — Optional. Time zone. String
• default — Optional. The default value to return if parsing is unsuccessful. DateTime

Returned value

Value of type DateTime if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00 if not. DateTime

Examples

Successful conversion

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

2022-12-30 13:44:17

Failed conversion

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

2023-01-01 00:00:00

toDateTimeOrNull

Introduced in: v1.1.0

Converts an input value to a value of type DateTime but returns NULL if an invalid argument is received. The same as toDateTime but returns NULL if an invalid argument is received. An integer argument is interpreted as a Unix timestamp and produces NULL if it is out of range of the DateTime type.

Syntax

toDateTimeOrNull(x[, timezone])

Arguments

• x — A string representation of a date with time, or an integer Unix timestamp. String or (U)Int8/16/32/64
• timezone — Optional. Time zone of the returned value. String

Returned value

Returns a DateTime value if successful, otherwise NULL. DateTime or NULL

Examples

Usage example

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

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

Integer argument with a time zone

SELECT toDateTimeOrNull(1583851242, 'Asia/Shanghai'), toDateTimeOrNull(4294967296)

┌─toDateTimeOrNull(1583851242, 'Asia/Shanghai')─┬─toDateTimeOrNull(4294967296)─┐
│                           2020-03-10 22:40:42 │                         ᴺᵁᴸᴸ │
└───────────────────────────────────────────────┴──────────────────────────────┘

toDateTimeOrZero

Introduced in: v1.1.0

Converts an input value to a value of type DateTime but returns the lower boundary of DateTime if an invalid argument is received. The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received.

Syntax

toDateTimeOrZero(x)

Arguments

• x — A string representation of a date with time. String

Returned value

Returns a DateTime value if successful, otherwise the lower boundary of DateTime (1970-01-01 00:00:00). DateTime

Examples

Usage example

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

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

toDecimal128

Introduced in: v18.12.0

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

Supported arguments:

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

Unsupported arguments:

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

Syntax

toDecimal128(expr, S)

Arguments

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

Returned value

Returns a value of type Decimal(38, S) Decimal128(S)

Examples

Usage example

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

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

toDecimal128OrDefault

Introduced in: v21.11.0

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

Syntax

toDecimal128OrDefault(expr, S[, default])

Arguments

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

Returned value

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

Examples

Successful conversion

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

0.023809523809523808

Failed conversion

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

-1

toDecimal128OrNull

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(38, S) but returns NULL in case of an error. Like toDecimal128 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.
• Values that exceed the bounds of Decimal128:(-1*10^(38 - S), 1*10^(38 - S)).

See also:

• toDecimal128.
• toDecimal128OrZero.
• toDecimal128OrDefault.

Syntax

toDecimal128OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(38, S) value if successful, otherwise NULL. Decimal128(S) or NULL

Examples

Usage example

SELECT toDecimal128OrNull('42.7', 2), toDecimal128OrNull('invalid', 2)

┌─toDecimal128OrNull('42.7', 2)─┬─toDecimal128OrNull('invalid', 2)─┐
│                          42.7 │                             ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal128OrZero

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(38, S) but returns 0 in case of an error. Like toDecimal128 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.

Syntax

toDecimal128OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(38, S) value if successful, otherwise 0. Decimal128(S)

Examples

Basic usage

SELECT toDecimal128OrZero('42.7', 2), toDecimal128OrZero('invalid', 2)

┌─toDecimal128OrZero('42.7', 2)─┬─toDecimal128OrZero('invalid', 2)─┐
│                          42.7 │                                0 │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal256

Introduced in: v20.8.0

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

Supported arguments:

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

Unsupported arguments:

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

Syntax

toDecimal256(expr, S)

Arguments

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

Returned value

Returns a value of type Decimal(76, S). Decimal256(S)

Examples

Usage example

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

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

toDecimal256OrDefault

Introduced in: v21.11.0

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

Syntax

toDecimal256OrDefault(expr, S[, default])

Arguments

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

Returned value

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

Examples

Successful conversion

SELECT toDecimal256OrDefault(toString(1/42), 76)

0.023809523809523808

Failed conversion

SELECT toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)'))

-1

toDecimal256OrNull

Introduced in: v20.8.0

Converts an input value to a value of type Decimal(76, S) but returns NULL in case of an error. Like toDecimal256 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.
• Values that exceed the bounds of Decimal256: (-1 * 10^(76 - S), 1 * 10^(76 - S)).

See also:

• toDecimal256.
• toDecimal256OrZero.
• toDecimal256OrDefault.

Syntax

toDecimal256OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(76, S) value if successful, otherwise NULL. Decimal256(S) or NULL

Examples

Usage example

SELECT toDecimal256OrNull('42.7', 2), toDecimal256OrNull('invalid', 2)

┌─toDecimal256OrNull('42.7', 2)─┬─toDecimal256OrNull('invalid', 2)─┐
│                          42.7 │                             ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal256OrZero

Introduced in: v20.8.0

Converts an input value to a value of type Decimal(76, S) but returns 0 in case of an error. Like toDecimal256 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.

See also:

• toDecimal256.
• toDecimal256OrNull.
• toDecimal256OrDefault.

Syntax

toDecimal256OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(76, S) value if successful, otherwise 0. Decimal256(S)

Examples

Usage example

SELECT toDecimal256OrZero('42.7', 2), toDecimal256OrZero('invalid', 2)

┌─toDecimal256OrZero('42.7', 2)─┬─toDecimal256OrZero('invalid', 2)─┐
│                          42.7 │                                0 │
└───────────────────────────────┴──────────────────────────────────┘

toDecimal32

Introduced in: v18.12.0

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

Supported arguments:

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

Unsupported arguments:

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

Syntax

toDecimal32(expr, S)

Arguments

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

Returned value

Returns a value of type Decimal(9, S) Decimal32(S)

Examples

Usage example

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

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

toDecimal32OrDefault

Introduced in: v21.11.0

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

Syntax

toDecimal32OrDefault(expr, S[, default])

Arguments

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

Returned value

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

Examples

Successful conversion

SELECT toDecimal32OrDefault(toString(0.0001), 5)

0.0001

Failed conversion

SELECT toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)'))

-1

toDecimal32OrNull

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(9, S) but returns NULL in case of an error. Like toDecimal32 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.
• Values that exceed the bounds of Decimal32:(-1*10^(9 - S), 1*10^(9 - S)).

See also:

• toDecimal32.
• toDecimal32OrZero.
• toDecimal32OrDefault.

Syntax

toDecimal32OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(9, S) value if successful, otherwise NULL. Decimal32(S) or NULL

Examples

Usage example

SELECT toDecimal32OrNull('42.7', 2), toDecimal32OrNull('invalid', 2)

┌─toDecimal32OrNull('42.7', 2)─┬─toDecimal32OrNull('invalid', 2)─┐
│                         42.7 │                            ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal32OrZero

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(9, S) but returns 0 in case of an error. Like toDecimal32 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.

Syntax

toDecimal32OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(9, S) value if successful, otherwise 0. Decimal32(S)

Examples

Usage example

SELECT toDecimal32OrZero('42.7', 2), toDecimal32OrZero('invalid', 2)

┌─toDecimal32OrZero('42.7', 2)─┬─toDecimal32OrZero('invalid', 2)─┐
│                         42.7 │                               0 │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal64

Introduced in: v18.12.0

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

Supported arguments:

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

Unsupported arguments:

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

Syntax

toDecimal64(expr, S)

Arguments

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

Returned value

Returns a decimal value. Decimal(18, S)

Examples

Usage example

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

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

toDecimal64OrDefault

Introduced in: v21.11.0

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

Syntax

toDecimal64OrDefault(expr, S[, default])

Arguments

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

Returned value

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

Examples

Successful conversion

SELECT toDecimal64OrDefault(toString(0.0001), 18)

0.0001

Failed conversion

SELECT toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)'))

-1

toDecimal64OrNull

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(18, S) but returns NULL in case of an error. Like toDecimal64 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.
• Values that exceed the bounds of Decimal64:(-1*10^(18 - S), 1*10^(18 - S)).

See also:

• toDecimal64.
• toDecimal64OrZero.
• toDecimal64OrDefault.

Syntax

toDecimal64OrNull(expr, S)

Arguments

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

Returned value

Returns a Decimal(18, S) value if successful, otherwise NULL. Decimal64(S) or NULL

Examples

Usage example

SELECT toDecimal64OrNull('42.7', 2), toDecimal64OrNull('invalid', 2)

┌─toDecimal64OrNull('42.7', 2)─┬─toDecimal64OrNull('invalid', 2)─┐
│                         42.7 │                            ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘

toDecimal64OrZero

Introduced in: v20.1.0

Converts an input value to a value of type Decimal(18, S) but returns 0 in case of an error. Like toDecimal64 but returns 0 instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return 0):

• Values or string representations of Float* values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values.

See also:

• toDecimal64.
• toDecimal64OrNull.
• toDecimal64OrDefault.

Syntax

toDecimal64OrZero(expr, S)

Arguments

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

Returned value

Returns a Decimal(18, S) value if successful, otherwise 0. Decimal64(S)

Examples

Usage example

SELECT toDecimal64OrZero('42.7', 2), toDecimal64OrZero('invalid', 2)

┌─toDecimal64OrZero('42.7', 2)─┬─toDecimal64OrZero('invalid', 2)─┐
│                         42.7 │                               0 │
└──────────────────────────────┴─────────────────────────────────┘

toDecimalString

Introduced in: v23.3.0

Converts a numeric value to a String with specified number of fractional digits.

The function rounds the input value to the specified number of decimal places. If the input value has fewer fractional digits than requested, the result is padded with zeros to achieve the exact number of fractional digits specified.

Syntax

toDecimalString(number, scale)

Arguments

• number — The numeric value to convert to a string. Can be any numeric type (Int, UInt, Float, Decimal). Int8 or Int16 or Int32 or Int64 or UInt8 or UInt16 or UInt32 or UInt64 or Float32 or Float64 or Decimal
• scale — The number of digits to display in the fractional part. The result will be rounded if necessary. UInt8

Returned value

Returns a String representation of the number with exactly the specified number of fractional digits. String

Examples

Round and format a number

SELECT toDecimalString(2.1456, 2)

┌─toDecimalString(2.1456, 2)─┐
│ 2.15                       │
└────────────────────────────┘

Pad with zeros

SELECT toDecimalString(5, 3)

┌─toDecimalString(5, 3)─┐
│ 5.000                 │
└───────────────────────┘

Different numeric types

SELECT toDecimalString(CAST(123.456 AS Decimal(10,3)), 2) AS decimal_val,
       toDecimalString(CAST(42.7 AS Float32), 4) AS float_val

┌─decimal_val─┬─float_val─┐
│ 123.46      │ 42.7000   │
└─────────────┴───────────┘

toFixedString

Introduced in: v1.1.0

Converts a String argument to a FixedString(N) type (a string of fixed length N).

If the string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.

Syntax

toFixedString(s, N)

Arguments

• s — String to convert. String
• N — Length of the resulting FixedString. const UInt*

Returned value

Returns a FixedString of length N. FixedString(N)

Examples

Usage example

-- The padding is made of null bytes, so the result is shown with `hex`.
SELECT hex(toFixedString('foo', 8)) AS s;

┌─s────────────────┐
│ 666F6F0000000000 │
└──────────────────┘

toFloat32

Introduced in: v1.1.0

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

Supported arguments:

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

Unsupported arguments:

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

See also:

• toFloat32OrZero.
• toFloat32OrNull.
• toFloat32OrDefault.

Syntax

toFloat32(expr)

Arguments

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

Returned value

Returns a 32-bit floating point value. Float32

Examples

Usage example

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

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

toFloat32OrDefault

Introduced in: v21.11.0

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

Syntax

toFloat32OrDefault(expr[, default])

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• default — Optional. The default value to return if parsing is unsuccessful. Float32

Returned value

Returns a value of type Float32 if successful, otherwise returns the default value if passed or 0 if not. Float32

Examples

Successful conversion

SELECT toFloat32OrDefault('8', CAST('0', 'Float32'))

8

Failed conversion

SELECT toFloat32OrDefault('abc', CAST('0', 'Float32'))

0

toFloat32OrNull

Introduced in: v1.1.0

Converts an input value to a value of type Float32 but returns NULL in case of an error. Like toFloat32 but returns NULL instead of throwing an exception on conversion errors.

Supported arguments:

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

Unsupported arguments (return NULL):

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

See also:

• toFloat32.
• toFloat32OrZero.
• toFloat32OrDefault.

Syntax

toFloat32OrNull(x)

Arguments

• x — A string representation of a number. String

Returned value

Returns a 32-bit Float value if successful, otherwise NULL. Float32 or NULL

Examples

Usage example

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

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

toFloat32OrZero

Introduced in: v1.1.0

Converts an input value to a value of type Float32 but returns 0 in case of an error. Like toFloat32 but returns 0 instead of throwing an exception on conversion errors.

See also:

• toFloat32.
• toFloat32OrNull.
• toFloat32OrDefault.

Syntax

toFloat32OrZero(x)

Arguments

• x — A string representation of a number. String

Returned value

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

Examples

Usage example

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

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

toFloat64

Introduced in: v1.1.0

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

Supported arguments:

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

Unsupported arguments:

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

See also:

• toFloat64OrZero.
• toFloat64OrNull.
• toFloat64OrDefault.

Syntax

toFloat64(expr)

Arguments

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

Returned value

Returns a 64-bit floating point value. Float64

Examples

Usage example

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

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

toFloat64OrDefault

Introduced in: v21.11.0

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

Syntax

toFloat64OrDefault(expr[, default])

Arguments

• expr — Expression returning a number or a string representation of a number. String or (U)Int* or Float*
• default — Optional. The default value to return if parsing is unsuccessful. Float64

Returned value

Returns a value of type Float64 if successful, otherwise returns the default value if passed or 0 if not. Float64

Examples

Successful conversion

SELECT toFloat64OrDefault('8', CAST('0', 'Float64'))

8