Documentation Index
Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt
Use this file to discover all available pages before exploring further.
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.
Be aware of potential data loss if values of a datatype are converted to a smaller datatype (for example from
Int64 to Int32) or between
incompatible datatypes (for example from String to Int). Make sure to check carefully if the result is as expected.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
toStringfunction of theDateTimeargument 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:
toDate(unix_timestamp), which otherwise would be an error and would require writing the more cumbersome toDate(toDateTime(unix_timestamp)).
Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.
Conversion between numeric types uses the same rules as assignments between different numeric types in C++.
Example
Query:
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. SyntaxAny
Examples
Basic usage
Query
Response
Query
Response
Query
Response
DATE
Introduced in: v21.2.0 Converts the argument to the Date data type. This is a MySQL compatibility alias fortoDate. It behaves the same as toDate.
Syntax
Date
Examples
Basic usage
Query
Response
accurateCast
Introduced in: v1.1.0 Converts a value to a specified data type. UnlikeCAST, 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
Any
Examples
Successful conversion
Query
Response
Query
Response
accurateCastOrDefault
Introduced in: v21.1.0 Converts a value to a specified data type. LikeaccurateCast, 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
x— A value to convert.AnyT— The target data type name.const Stringdefault_value— Optional. Default value to return if conversion fails.Any
Any
Examples
Successful conversion
Query
Response
Query
Response
Query
Response
accurateCastOrNull
Introduced in: v1.1.0 Converts a value to a specified data type. LikeaccurateCast, 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
NULL if conversion is not possible. Any
Examples
Successful conversion
Query
Response
Query
Response
formatRow
Introduced in: v20.7.0 Converts arbitrary expressions into a string via given format.If the format contains a suffix/prefix, it will be written in each row.
Only row-based formats are supported in this function.
String
Examples
Basic usage
Query
Response
Query
Response
formatRowNoNewline
Introduced in: v20.7.0 Same asformatRow, 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
String
Examples
Basic usage
Query
Response
fromUnixTimestamp64Micro
Introduced in: v20.5.0 Converts a Unix timestamp in microseconds to aDateTime64 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
value— Unix timestamp in microseconds.Int64timezone— Optional. Timezone for the returned value.String
DateTime64 value with microsecond precision. DateTime64(6)
Examples
Usage example
Query
Response
fromUnixTimestamp64Milli
Introduced in: v20.5.0 Converts a Unix timestamp in milliseconds to aDateTime64 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
value— Unix timestamp in milliseconds.Int64timezone— Optional. Timezone for the returned value.String
DateTime64 value with millisecond precision. DateTime64(3)
Examples
Usage example
Query
Response
fromUnixTimestamp64Nano
Introduced in: v20.5.0 Converts a Unix timestamp in nanoseconds to aDateTime64 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).
Please note that the input value is treated as a UTC timestamp, not the timezone of the input value.
value— Unix timestamp in nanoseconds.Int64timezone— Optional. Timezone for the returned value.String
DateTime64 value with nanosecond precision. DateTime64(9)
Examples
Usage example
Query
Response
fromUnixTimestamp64Second
Introduced in: v24.12.0 Converts a Unix timestamp in seconds to aDateTime64 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
value— Unix timestamp in seconds.Int64timezone— Optional. Timezone for the returned value.String
DateTime64 value with second precision. DateTime64(0)
Examples
Usage example
Query
Response
parseDateTime
Introduced in: v23.3.0 Parses a date and time string according to a MySQL date format string. This function is the inverse offormatDateTime.
It parses a String argument using a format String. Returns a DateTime type.
Syntax
TO_UNIXTIME
Arguments
time_string— String to be parsed into DateTime.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime
Examples
Usage example
Query
Response
parseDateTime32BestEffort
Introduced in: v20.9.0 Converts a string representation of a date and time to theDateTime 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
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsedString
time_string as a DateTime. DateTime
Examples
Usage example
Query
Response
Query
Response
Query
Response
parseDateTime32BestEffortOrNull
Introduced in: v20.9.0 Same asparseDateTime32BestEffort except that it returns NULL when it encounters a date format that cannot be processed.
Syntax
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
DateTime object parsed from the string, or NULL if the parsing fails. DateTime
Examples
Usage example
Query
Response
parseDateTime32BestEffortOrZero
Introduced in: v20.9.0 Same asparseDateTime32BestEffort except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.
Syntax
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
DateTime object parsed from the string, or zero date (1970-01-01 00:00:00) if the parsing fails. DateTime
Examples
Usage example
Query
Response
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 offormatDateTime for DateTime64.
It parses a String argument using a format String. Returns a DateTime64 type which can represent dates from 1900 to 2299 with sub-second precision.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime64
Examples
Usage example
Query
Response
parseDateTime64BestEffort
Introduced in: v20.1.0 Same asparseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime64 data type.
Syntax
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
time_string converted to the DateTime64 data type. DateTime64
Examples
Usage example
Query
Response
parseDateTime64BestEffortOrNull
Introduced in: v20.1.0 Same asparseDateTime64BestEffort except that it returns NULL when it encounters a date format that cannot be processed.
Syntax
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
time_string converted to DateTime64, or NULL if the input cannot be parsed. DateTime64 or NULL
Examples
Usage example
Query
Response
parseDateTime64BestEffortOrZero
Introduced in: v20.1.0 Same asparseDateTime64BestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.
Syntax
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
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
Query
Response
parseDateTime64BestEffortUS
Introduced in: v22.8.0 Same asparseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity.
Syntax
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
time_string converted to DateTime64 using US date format preference for ambiguous cases. DateTime64
Examples
Usage example
Query
Response
parseDateTime64BestEffortUSOrNull
Introduced in: v22.8.0 Same asparseDateTime64BestEffort, 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
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
time_string converted to DateTime64 using US format preference, or NULL if the input cannot be parsed. DateTime64 or NULL
Examples
Usage example
Query
Response
parseDateTime64BestEffortUSOrZero
Introduced in: v22.8.0 Same asparseDateTime64BestEffort, 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
time_string— String containing a date or date with time to convert.Stringprecision— Optional. Required precision.3for milliseconds,6for microseconds. Default:3.UInt8time_zone— Optional. Timezone. The function parsestime_stringaccording to the timezone.String
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
Query
Response
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 offormatDateTimeInJodaSyntax for DateTime64.
It parses a String argument using a Joda-style format String. Returns a DateTime64 type which can represent dates from 1900 to 2299 with sub-second precision.
Refer to Joda Time documentation for the format patterns.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime64
Examples
Usage example
Query
Response
parseDateTime64InJodaSyntaxOrNull
Introduced in: v24.10.0 Same asparseDateTime64InJodaSyntax but returns NULL when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
Nullable(DateTime64)
Examples
Usage example
Query
Response
parseDateTime64InJodaSyntaxOrZero
Introduced in: v24.10.0 Same asparseDateTime64InJodaSyntax but returns zero date when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime64
Examples
Usage example
Query
Response
parseDateTime64OrNull
Introduced in: v24.11.0 Same asparseDateTime64 but returns NULL when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
Nullable(DateTime64)
Examples
Usage example
Query
Response
parseDateTime64OrZero
Introduced in: v24.11.0 Same asparseDateTime64 but returns zero date when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime64.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime64
Examples
Usage example
Query
Response
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-YYetc. - A string with a day and time:
DD,DD hh,DD hh:mm. In this caseMMis substituted by01. - 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.
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
time_string as a DateTime. DateTime
Examples
Usage example
Query
Response
Query
Response
Query
Response
parseDateTimeBestEffortOrNull
Introduced in: v1.1.0 The same asparseDateTimeBestEffort 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-YYetc. - A string with a day and time:
DD,DD hh,DD hh:mm. In this caseMMis substituted by01. - 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.
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
time_string as a DateTime, or NULL if the input cannot be parsed. DateTime or NULL
Examples
Usage example
Query
Response
parseDateTimeBestEffortOrZero
Introduced in: v1.1.0 Same asparseDateTimeBestEffort 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-YYetc. - A string with a day and time:
DD,DD hh,DD hh:mm. In this caseMMis substituted by01. - 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.
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
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
Query
Response
parseDateTimeBestEffortUS
Introduced in: v1.1.0 This function behaves likeparseDateTimeBestEffort 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
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
time_string as a DateTime using US date format preference for ambiguous cases. DateTime
Examples
Usage example
Query
Response
parseDateTimeBestEffortUSOrNull
Introduced in: v1.1.0 Same asparseDateTimeBestEffortUS 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
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
time_string as a DateTime using US format preference, or NULL if the input cannot be parsed. DateTime or NULL
Examples
Usage example
Query
Response
parseDateTimeBestEffortUSOrZero
Introduced in: v1.1.0 Same asparseDateTimeBestEffortUS 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
time_string— String containing a date and time to convert.Stringtime_zone— Optional. Time zone according to whichtime_stringis parsed.String
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
Query
Response
parseDateTimeInJodaSyntax
Introduced in: v23.3.0 Parses a date and time string according to a Joda date format string. This function is the inverse offormatDateTimeInJodaSyntax.
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
time_string— String to be parsed into DateTime.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime
Examples
Usage example
Query
Response
parseDateTimeInJodaSyntaxOrNull
Introduced in: v23.3.0 Same asparseDateTimeInJodaSyntax but returns NULL when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
Nullable(DateTime)
Examples
Usage example
Query
Response
parseDateTimeInJodaSyntaxOrZero
Introduced in: v23.3.0 Same asparseDateTimeInJodaSyntax but returns zero date when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime.Stringformat— Format string in Joda syntax specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime
Examples
Usage example
Query
Response
parseDateTimeOrNull
Introduced in: v23.3.0 Same asparseDateTime but returns NULL when it encounters an unparsable date format.
Syntax
str_to_date
Arguments
time_string— String to be parsed into DateTime.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
Nullable(DateTime)
Examples
Usage example
Query
Response
parseDateTimeOrZero
Introduced in: v23.3.0 Same asparseDateTime but returns zero date when it encounters an unparsable date format.
Syntax
time_string— String to be parsed into DateTime.Stringformat— Format string specifying how to parse time_string.Stringtimezone— Optional. Timezone.String
DateTime
Examples
Usage example
Query
Response
reinterpret
Introduced in: v1.1.0 Uses the same source in-memory bytes sequence for the provided valuex and reinterprets it to the destination type.
Syntax
x— Any type.Anytype— Destination type. If it is an array, then the array element type must be a fixed length type.String
Any
Examples
Usage example
Query
Response
Query
Response
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 Syntaxx— Number of days since the beginning of the Unix Epoch.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
Date
Examples
Usage example
Query
Response
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 Syntaxx— Number of seconds since the beginning of the Unix Epoch.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
DateTime
Examples
Usage example
Query
Response
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. Syntaxx. FixedString
Examples
Usage example
Query
Response
reinterpretAsFloat32
Introduced in: v1.1.0 Reinterprets the input value as a value of type Float32. UnlikeCAST, 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
x— Value to reinterpret as Float32.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Float32
Examples
Usage example
Query
Response
reinterpretAsFloat64
Introduced in: v1.1.0 Reinterprets the input value as a value of type Float64. UnlikeCAST, 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
x— Value to reinterpret as Float64.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Float64
Examples
Usage example
Query
Response
reinterpretAsInt128
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int128. UnlikeCAST, 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
x— Value to reinterpret as Int128.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int128
Examples
Usage example
Query
Response
reinterpretAsInt16
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int16. UnlikeCAST, 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
x— Value to reinterpret as Int16.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int16
Examples
Usage example
Query
Response
reinterpretAsInt256
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int256. UnlikeCAST, 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
x— Value to reinterpret as Int256.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int256
Examples
Usage example
Query
Response
reinterpretAsInt32
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int32. UnlikeCAST, 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
x— Value to reinterpret as Int32.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int32
Examples
Usage example
Query
Response
reinterpretAsInt64
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int64. UnlikeCAST, 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
x— Value to reinterpret as Int64.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int64
Examples
Usage example
Query
Response
reinterpretAsInt8
Introduced in: v1.1.0 Reinterprets the input value as a value of type Int8. UnlikeCAST, 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
x— Value to reinterpret as Int8.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. Int8
Examples
Usage example
Query
Response
reinterpretAsString
Introduced in: v1.1.0 Reinterprets the input value as a string (assuming little endian order). Null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character. Syntaxx. String
Examples
Usage example
Query
Response
reinterpretAsUInt128
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt128. UnlikeCAST, 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
x— Value to reinterpret as UInt128.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt128
Examples
Usage example
Query
Response
reinterpretAsUInt16
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt16. UnlikeCAST, 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
x— Value to reinterpret as UInt16.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt16
Examples
Usage example
Query
Response
reinterpretAsUInt256
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt256. UnlikeCAST, 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
x— Value to reinterpret as UInt256.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt256
Examples
Usage example
Query
Response
reinterpretAsUInt32
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt32. UnlikeCAST, 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
x— Value to reinterpret as UInt32.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt32
Examples
Usage example
Query
Response
reinterpretAsUInt64
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt64. UnlikeCAST, 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
x— Value to reinterpret as UInt64.Int*orUInt*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt64
Examples
Usage example
Query
Response
reinterpretAsUInt8
Introduced in: v1.1.0 Reinterprets the input value as a value of type UInt8. UnlikeCAST, 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
x— Value to reinterpret as UInt8.(U)Int*orFloat*orDateorDateTimeorUUIDorStringorFixedString
x. UInt8
Examples
Usage example
Query
Response
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. Syntaxfixed_string— Big-endian byte string.FixedString
UUID
Examples
String to UUID
Query
Response
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: Syntaxexpr— Expression returning a number or a string representation of a number.Expression
BFloat16
Examples
Usage example
Query
Response
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.
NULL):
- String representations of binary and hexadecimal values.
- Numeric values.
The function allows a silent loss of precision while converting from the string representation.
x— A String representation of a number.String
NULL. BFloat16 or NULL
Examples
Usage example
Query
Response
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.
0):
- String representations of binary and hexadecimal values.
- Numeric values.
The function allows a silent loss of precision while converting from the string representation.
x— A String representation of a number.String
0. BFloat16
Examples
Usage example
Query
Response
toBool
Introduced in: v22.2.0 Converts an input value to a value of type Bool. Syntaxexpr— Expression returning a number or a string. For strings, accepts ‘true’ or ‘false’ (case-insensitive).(U)Int*orFloat*orStringorExpression
true or false based on evaluation of the argument. Bool
Examples
Usage example
Query
Response
toDate
Introduced in: v1.1.0 Converts an input value to typeDate.
Supports conversion from String, FixedString, DateTime, or numeric types.
Syntax
x— Input value to convert.StringorFixedStringorDateTimeor(U)Int*orFloat*
Date
Examples
String to Date conversion
Query
Response
Query
Response
Query
Response
toDate32
Introduced in: v21.9.0 Converts the argument to the Date32 data type. If the value is outside the range,toDate32 returns the border values supported by Date32.
If the argument is of type Date, it’s bounds are taken into account.
Syntax
Date32
Examples
Within range
Query
Response
Query
Response
toDate32OrDefault
Introduced in: v21.11.0 Converts the argument to the Date32 data type. If the value is outside the range,toDate32OrDefault returns the lower border value supported by Date32. If the argument has Date type, it’s borders are taken into account. Returns default value if an invalid argument is received.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Date32
Date32
Examples
Successful conversion
Query
Response
Query
Response
toDate32OrNull
Introduced in: v21.9.0 Converts an input value to a value of type Date32 but returnsNULL if an invalid argument is received.
The same as toDate32 but returns NULL if an invalid argument is received.
Syntax
x— A string representation of a date.String
NULL. Date32 or NULL
Examples
Usage example
Query
Response
toDate32OrZero
Introduced in: v21.9.0 Converts an input value to a value of type Date32 but returns the lower boundary of Date32 if an invalid argument is received. The same as toDate32 but returns lower boundary of Date32 if an invalid argument is received. See also: Syntaxx— A string representation of a date.String
1900-01-01). Date32
Examples
Usage example
Query
Response
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. Syntaxexpr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Date
Date
Examples
Successful conversion
Query
Response
Query
Response
toDateOrNull
Introduced in: v1.1.0 Converts an input value to a value of typeDate but returns NULL if an invalid argument is received.
The same as toDate but returns NULL if an invalid argument is received.
Syntax
x— A string representation of a date.String
NULL. Date or NULL
Examples
Usage example
Query
Response
toDateOrZero
Introduced in: v1.1.0 Converts an input value to a value of typeDate but returns the lower boundary of Date if an invalid argument is received.
The same as toDate but returns lower boundary of Date if an invalid argument is received.
See also:
Syntax
x— A string representation of a date.String
1970-01-01). Date
Examples
Usage example
Query
Response
toDateTime
Introduced in: v1.1.0 Converts an input value to type DateTime.If
expr is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp).
If expr is a String, it may be interpreted as a Unix timestamp or as a string representation of date / date with time.
Thus, parsing of short numbers’ string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string '1999' may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.DateTime
Examples
Usage example
Query
Response
toDateTime32
Introduced in: v20.9.0 Converts an input value to typeDateTime.
Supports conversion from String, FixedString, Date, Date32, DateTime, or numeric types ((U)Int*, Float*, Decimal).
DateTime32 provides extended range compared to DateTime, supporting dates from 1900-01-01 to 2299-12-31.
Syntax
x— Input value to convert.StringorFixedStringorUInt*orFloat*orDateorDateTimeorDateTime64timezone— Optional. Timezone for the returnedDateTimevalue.String
DateTime
Examples
The value is within the range
Query
Response
Query
Response
Query
Response
toDateTime64
Introduced in: v20.1.0 Converts an input value to a value of typeDateTime64.
Syntax
expr— Expression returning a number or a string representation of a number.Expressionscale— Tick size (precision): 10^(-scale) seconds.UInt8timezone— Optional. Time zone for the specifiedDateTime64object.String
DateTime64
Examples
The value is within the range
Query
Response
Query
Response
Query
Response
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. Syntaxexpr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*scale— Tick size (precision): 10^-precision seconds.UInt8timezone— Optional. Time zone.Stringdefault— Optional. The default value to return if parsing is unsuccessful.DateTime64
DateTime64
Examples
Successful conversion
Query
Response
Query
Response
toDateTime64OrNull
Introduced in: v20.1.0 Converts an input value to a value of typeDateTime64 but returns NULL if an invalid argument is received.
The same as toDateTime64 but returns NULL if an invalid argument is received.
Syntax
x— A string representation of a date with time and subsecond precision.String
NULL. DateTime64 or NULL
Examples
Usage example
Query
Response
toDateTime64OrZero
Introduced in: v20.1.0 Converts an input value to a value of type DateTime64 but returns the lower boundary of DateTime64 if an invalid argument is received. The same as toDateTime64 but returns lower boundary of DateTime64 if an invalid argument is received. See also: Syntaxx— A string representation of a date with time and subsecond precision.String
1970-01-01 00:00:00.000). DateTime64
Examples
Usage example
Query
Response
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. Syntaxexpr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*timezone— Optional. Time zone.Stringdefault— Optional. The default value to return if parsing is unsuccessful.DateTime
DateTime
Examples
Successful conversion
Query
Response
Query
Response
toDateTimeOrNull
Introduced in: v1.1.0 Converts an input value to a value of typeDateTime but returns NULL if an invalid argument is received.
The same as toDateTime but returns NULL if an invalid argument is received.
Syntax
x— A string representation of a date with time.String
DateTime value if successful, otherwise NULL. DateTime or NULL
Examples
Usage example
Query
Response
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. Syntaxx— A string representation of a date with time.String
1970-01-01 00:00:00). DateTime
Examples
Usage example
Query
Response
toDecimal128
Introduced in: v18.12.0 Converts an input value to a value of typeDecimal(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*.
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128('0xc0fe', 1);.
An overflow can occur if the value of
expr exceeds the bounds of Decimal128:(-1*10^(38 - S), 1*10^(38 - S)).
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.UInt8
Decimal(38, S) Decimal128(S)
Examples
Usage example
Query
Response
toDecimal128OrDefault
Introduced in: v21.11.0 LiketoDecimal128, 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
expr— A String representation of a number.StringS— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.UInt8default— Optional. The default value to return if parsing to type Decimal128(S) is unsuccessful.Decimal128(S)
Decimal128(S)
Examples
Successful conversion
Query
Response
Query
Response
toDecimal128OrNull
Introduced in: v20.1.0 Converts an input value to a value of typeDecimal(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*.
NULL):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
- Values that exceed the bounds of
Decimal128:(-1*10^(38 - S), 1*10^(38 - S)).
expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.UInt8
NULL. Decimal128(S) or NULL
Examples
Usage example
Query
Response
toDecimal128OrZero
Introduced in: v20.1.0 Converts an input value to a value of type Decimal(38, S) but returns0 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*.
0):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
If the input value exceeds the bounds of
Decimal128:(-1*10^(38 - S), 1*10^(38 - S)), the function returns 0.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.UInt8
0. Decimal128(S)
Examples
Basic usage
Query
Response
toDecimal256
Introduced in: v20.8.0 Converts an input value to a value of typeDecimal(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*.
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256('0xc0fe', 1);.
An overflow can occur if the value of
expr exceeds the bounds of Decimal256:(-1*10^(76 - S), 1*10^(76 - S)).
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.UInt8
Decimal(76, S). Decimal256(S)
Examples
Usage example
Query
Response
toDecimal256OrDefault
Introduced in: v21.11.0 LiketoDecimal256, 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
expr— A String representation of a number.StringS— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.UInt8default— Optional. The default value to return if parsing to type Decimal256(S) is unsuccessful.Decimal256(S)
Decimal256(S)
Examples
Successful conversion
Query
Response
Query
Response
toDecimal256OrNull
Introduced in: v20.8.0 Converts an input value to a value of typeDecimal(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*.
NULL):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
- Values that exceed the bounds of
Decimal256:(-1 * 10^(76 - S), 1 * 10^(76 - S)).
expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.UInt8
NULL. Decimal256(S) or NULL
Examples
Usage example
Query
Response
toDecimal256OrZero
Introduced in: v20.8.0 Converts an input value to a value of type Decimal(76, S) but returns0 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*.
0):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
If the input value exceeds the bounds of
Decimal256:(-1*10^(76 - S), 1*10^(76 - S)), the function returns 0.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.UInt8
0. Decimal256(S)
Examples
Usage example
Query
Response
toDecimal32
Introduced in: v18.12.0 Converts an input value to a value of typeDecimal(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*.
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32('0xc0fe', 1);.
An overflow can occur if the value of
expr exceeds the bounds of Decimal32:(-1*10^(9 - S), 1*10^(9 - S)).
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.UInt8
Decimal(9, S) Decimal32(S)
Examples
Usage example
Query
Response
toDecimal32OrDefault
Introduced in: v21.11.0 LiketoDecimal32, 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
expr— A String representation of a number.StringS— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.UInt8default— Optional. The default value to return if parsing to type Decimal32(S) is unsuccessful.Decimal32(S)
Decimal32(S)
Examples
Successful conversion
Query
Response
Query
Response
toDecimal32OrNull
Introduced in: v20.1.0 Converts an input value to a value of typeDecimal(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*.
NULL):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
- Values that exceed the bounds of
Decimal32:(-1*10^(9 - S), 1*10^(9 - S)).
expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.UInt8
NULL. Decimal32(S) or NULL
Examples
Usage example
Query
Response
toDecimal32OrZero
Introduced in: v20.1.0 Converts an input value to a value of type Decimal(9, S) but returns0 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*.
0):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
If the input value exceeds the bounds of
Decimal32:(-1*10^(9 - S), 1*10^(9 - S)), the function returns 0.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.UInt8
0. Decimal32(S)
Examples
Usage example
Query
Response
toDecimal64
Introduced in: v18.12.0 Converts an input value to a value of typeDecimal(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*.
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64('0xc0fe', 1);.
An overflow can occur if the value of
expr exceeds the bounds of Decimal64:(-1*10^(18 - S), 1*10^(18 - S)).
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.UInt8
Decimal(18, S)
Examples
Usage example
Query
Response
toDecimal64OrDefault
Introduced in: v21.11.0 LiketoDecimal64, 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
expr— A String representation of a number.StringS— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.UInt8default— Optional. The default value to return if parsing to type Decimal64(S) is unsuccessful.Decimal64(S)
Decimal64(S)
Examples
Successful conversion
Query
Response
Query
Response
toDecimal64OrNull
Introduced in: v20.1.0 Converts an input value to a value of type Decimal(18, S) but returnsNULL 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*.
NULL):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
- Values that exceed the bounds of
Decimal64:(-1*10^(18 - S), 1*10^(18 - S)).
expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.UInt8
NULL. Decimal64(S) or NULL
Examples
Usage example
Query
Response
toDecimal64OrZero
Introduced in: v20.1.0 Converts an input value to a value of type Decimal(18, S) but returns0 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*.
0):
- Values or string representations of Float* values
NaNandInf(case-insensitive). - String representations of binary and hexadecimal values.
If the input value exceeds the bounds of
Decimal64:(-1*10^(18 - S), 1*10^(18 - S)), the function returns 0.expr— Expression returning a number or a string representation of a number.ExpressionS— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.UInt8
0. Decimal64(S)
Examples
Usage example
Query
Response
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. Syntaxnumber— The numeric value to convert to a string. Can be any numeric type (Int, UInt, Float, Decimal).Int8orInt16orInt32orInt64orUInt8orUInt16orUInt32orUInt64orFloat32orFloat64orDecimalscale— The number of digits to display in the fractional part. The result will be rounded if necessary.UInt8
String
Examples
Round and format a number
Query
Response
Query
Response
Query
Response
toFixedString
Introduced in: v1.1.0 Converts aString 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
s— String to convert.StringN— Length of the resulting FixedString.const UInt*
FixedString(N)
Examples
Usage example
Query
Response
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
NaNandInf. - String representations of Float*, including
NaNandInf(case-insensitive).
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32('0xc0fe');.
expr— Expression returning a number or a string representation of a number.Expression
Float32
Examples
Usage example
Query
Response
toFloat32OrDefault
Introduced in: v21.11.0 LiketoFloat32, 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
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Float32
Float32
Examples
Successful conversion
Query
Response
Query
Response
toFloat32OrNull
Introduced in: v1.1.0 Converts an input value to a value of type Float32 but returnsNULL 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
NaNandInf. - String representations of Float*, including
NaNandInf(case-insensitive).
NULL):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32OrNull('0xc0fe');. - Invalid string formats.
x— A string representation of a number.String
NULL. Float32 or NULL
Examples
Usage example
Query
Response
toFloat32OrZero
Introduced in: v1.1.0 Converts an input value to a value of type Float32 but returns0 in case of an error.
Like toFloat32 but returns 0 instead of throwing an exception on conversion errors.
See also:
Syntax
x— A string representation of a number.String
0. Float32
Examples
Usage example
Query
Response
toFloat64
Introduced in: v1.1.0 Converts an input value to a value of typeFloat64.
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
NaNandInf. - String representations of type Float*, including
NaNandInf(case-insensitive).
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64('0xc0fe');.
expr— Expression returning a number or a string representation of a number.Expression
Float64
Examples
Usage example
Query
Response
toFloat64OrDefault
Introduced in: v21.11.0 LiketoFloat64, 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
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Float64
Float64
Examples
Successful conversion
Query
Response
Query
Response
toFloat64OrNull
Introduced in: v1.1.0 Converts an input value to a value of type Float64 but returnsNULL in case of an error.
Like toFloat64 but returns NULL instead of throwing an exception on conversion errors.
Supported arguments:
- Values of type (U)Int*.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float*, including
NaNandInf. - String representations of type Float*, including
NaNandInf(case-insensitive).
NULL):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64OrNull('0xc0fe');. - Invalid string formats.
x— A string representation of a number.String
NULL. Float64 or NULL
Examples
Usage example
Query
Response
toFloat64OrZero
Introduced in: v1.1.0 Converts an input value to a value of type Float64 but returns0 in case of an error.
Like toFloat64 but returns 0 instead of throwing an exception on conversion errors.
See also:
Syntax
x— A string representation of a number.String
0. Float64
Examples
Usage example
Query
Response
toInt128
Introduced in: v1.1.0 Converts an input value to a value of type Int128. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers. Supported arguments:- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128('0xc0fe');.
If the input value cannot be represented within the bounds of Int128, the result over or under flows.
This is not considered an error.
expr— Expression returning a number or a string representation of a number.Expression
Int128
Examples
Usage example
Query
Response
toInt128OrDefault
Introduced in: v21.11.0 LiketoInt128, this function converts an input value to a value of type Int128 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int128
Int128
Examples
Successful conversion
Query
Response
Query
Response
toInt128OrNull
Introduced in: v20.8.0 LiketoInt128, this function converts an input value to a value of type Int128 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
NULL if the conversion is unsuccessful. Int128 or NULL
Examples
Usage example
Query
Response
toInt128OrZero
Introduced in: v20.8.0 Converts an input value to type Int128 but returns0 in case of an error.
Like toInt128 but returns 0 instead of throwing an exception.
See also:
Syntax
x— Input value to convert.StringorFixedStringorFloat*orDecimalor(U)Int*orDateorDateTime
0 if conversion fails. Int128
Examples
Usage example
Query
Response
Query
Response
toInt16
Introduced in: v1.1.0 Converts an input value to a value of typeInt16.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16('0xc0fe');.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs.
This is not considered an error.
For example:
SELECT toInt16(32768) == -32768;.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
Int16
Examples
Usage example
Query
Response
toInt16OrDefault
Introduced in: v21.11.0 LiketoInt16, this function converts an input value to a value of type Int16 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int16
Int16
Examples
Successful conversion
Query
Response
Query
Response
toInt16OrNull
Introduced in: v1.1.0 LiketoInt16, this function converts an input value to a value of type Int16 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
Int16, otherwise NULL if the conversion is unsuccessful. Int16 or NULL
Examples
Usage example
Query
Response
toInt16OrZero
Introduced in: v1.1.0 LiketoInt16, this function converts an input value to a value of type Int16 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
0 if the conversion is unsuccessful. Int16
Examples
Usage example
Query
Response
toInt256
Introduced in: v1.1.0 Converts an input value to a value of type Int256. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers. Supported arguments:- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256('0xc0fe');.
If the input value cannot be represented within the bounds of Int256, the result over or under flows.
This is not considered an error.
expr— Expression returning a number or a string representation of a number.Expression
Int256
Examples
Usage example
Query
Response
toInt256OrDefault
Introduced in: v21.11.0 LiketoInt256, this function converts an input value to a value of type Int256 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int256
Int256
Examples
Successful conversion
Query
Response
Query
Response
toInt256OrNull
Introduced in: v20.8.0 LiketoInt256, this function converts an input value to a value of type Int256 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
NULL if the conversion is unsuccessful. Int256 or NULL
Examples
Usage example
Query
Response
toInt256OrZero
Introduced in: v20.8.0 Converts an input value to type Int256 but returns0 in case of an error.
Like toInt256 but returns 0 instead of throwing an exception.
See also:
Syntax
x— Input value to convert.StringorFixedStringorFloat*orDecimalor(U)Int*orDateorDateTime
0 if conversion fails. Int256
Examples
Usage example
Query
Response
Query
Response
toInt32
Introduced in: v1.1.0 Converts an input value to a value of typeInt32.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32('0xc0fe');.
If the input value cannot be represented within the bounds of Int32, the result over or under flows.
This is not considered an error.
For example:
SELECT toInt32(2147483648) == -2147483648;The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
Int32
Examples
Usage example
Query
Response
toInt32OrDefault
Introduced in: v21.11.0 LiketoInt32, this function converts an input value to a value of type Int32 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int32
Int32
Examples
Successful conversion
Query
Response
Query
Response
toInt32OrNull
Introduced in: v1.1.0 LiketoInt32, this function converts an input value to a value of type Int32 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
NULL if the conversion is unsuccessful. Int32 or NULL
Examples
Usage example
Query
Response
toInt32OrZero
Introduced in: v1.1.0 LiketoInt32, this function converts an input value to a value of type Int32 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
0 if the conversion is unsuccessful. Int32
Examples
Usage example
Query
Response
toInt64
Introduced in: v1.1.0 Converts an input value to a value of typeInt64.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64('0xc0fe');.
If the input value cannot be represented within the bounds of Int64, the result over or under flows.
This is not considered an error.
For example:
SELECT toInt64(9223372036854775808) == -9223372036854775808;The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number. Supported: values or string representations of type (U)Int*, values of type Float*. Unsupported: string representations of Float* values including NaN and Inf, string representations of binary and hexadecimal values.Expression
Int64
Examples
Usage example
Query
Response
toInt64OrDefault
Introduced in: v21.11.0 LiketoInt64, this function converts an input value to a value of type Int64 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int64
Int64
Examples
Successful conversion
Query
Response
Query
Response
toInt64OrNull
Introduced in: v1.1.0 LiketoInt64, this function converts an input value to a value of type Int64 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
NULL if the conversion is unsuccessful. Int64 or NULL
Examples
Usage example
Query
Response
toInt64OrZero
Introduced in: v1.1.0 Converts an input value to type Int64 but returns0 in case of an error.
Like toInt64 but returns 0 instead of throwing an exception.
See also:
Syntax
x— Input value to convert.StringorFixedStringorFloat*orDecimalor(U)Int*orDateorDateTime
0 if conversion fails. Int64
Examples
Usage example
Query
Response
Query
Response
toInt8
Introduced in: v1.1.0 Converts an input value to a value of typeInt8.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8('0xc0fe');.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs.
This is not considered an error.
For example:
SELECT toInt8(128) == -128;.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
Int8
Examples
Usage example
Query
Response
toInt8OrDefault
Introduced in: v21.11.0 LiketoInt8, this function converts an input value to a value of type Int8 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.Int8
Int8
Examples
Successful conversion
Query
Response
Query
Response
toInt8OrNull
Introduced in: v1.1.0 LiketoInt8, this function converts an input value to a value of type Int8 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
NULL if the conversion is unsuccessful. Int8 or NULL
Examples
Usage example
Query
Response
toInt8OrZero
Introduced in: v1.1.0 LiketoInt8, this function converts an input value to a value of type Int8 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs.
This is not considered an error.
x— A String representation of a number.String
0 if the conversion is unsuccessful. Int8
Examples
Usage example
Query
Response
toInterval
Introduced in: v25.4.0 Creates an Interval value from a numeric value and a unit string. This function provides a unified way to create intervals of different types (seconds, minutes, hours, days, weeks, months, quarters, years) from a single function by specifying the unit as a string argument. The unit string is case-insensitive. This is equivalent to calling type-specific functions liketoIntervalSecond, toIntervalMinute, toIntervalDay, etc.,
but allows the unit to be specified dynamically as a string parameter.
Syntax
value— The numeric value representing the number of units. Can be any numeric type.Int8orInt16orInt32orInt64orUInt8orUInt16orUInt32orUInt64orFloat32orFloat64unit— The unit of time. Must be a constant string. Valid values: ‘nanosecond’, ‘microsecond’, ‘millisecond’, ‘second’, ‘minute’, ‘hour’, ‘day’, ‘week’, ‘month’, ‘quarter’, ‘year’.String
Interval
Examples
Create intervals with different units
Query
Response
Query
Response
Query
Response
toIntervalDay
Introduced in: v1.1.0 Returns an interval ofn days of data type IntervalDay.
Syntax
n— Number of days. Integer numbers or string representations thereof, and float numbers.(U)Int*orFloat*orString
n days. Interval
Examples
Usage example
Query
Response
toIntervalHour
Introduced in: v1.1.0 Returns an interval ofn hours of data type IntervalHour.
Syntax
n— Number of hours. Integer numbers or string representations thereof, and float numbers.Int*orUInt*orFloat*orString
n hours. Interval
Examples
Usage example
Query
Response
toIntervalMicrosecond
Introduced in: v22.6.0 Returns an interval ofn microseconds of data type IntervalMicrosecond.
Syntax
n microseconds. Interval
Examples
Usage example
Query
Response
toIntervalMillisecond
Introduced in: v22.6.0 Returns an interval ofn milliseconds of data type IntervalMillisecond.
Syntax
n milliseconds. Interval
Examples
Usage example
Query
Response
toIntervalMinute
Introduced in: v1.1.0 Returns an interval ofn minutes of data type IntervalMinute.
Syntax
n— Number of minutes. Integer numbers or string representations thereof, and float numbers.(U)Int*orFloat*orString
n minutes. Interval
Examples
Usage example
Query
Response
toIntervalMonth
Introduced in: v1.1.0 Returns an interval ofn months of data type IntervalMonth.
Syntax
n months. Interval
Examples
Usage example
Query
Response
toIntervalNanosecond
Introduced in: v22.6.0 Returns an interval ofn nanoseconds of data type IntervalNanosecond.
Syntax
n nanoseconds. Interval
Examples
Usage example
Query
Response
toIntervalQuarter
Introduced in: v1.1.0 Returns an interval ofn quarters of data type IntervalQuarter.
Syntax
n quarters. Interval
Examples
Usage example
Query
Response
toIntervalSecond
Introduced in: v1.1.0 Returns an interval ofn seconds of data type IntervalSecond.
Syntax
n— Number of seconds. Integer numbers or string representations thereof, and float numbers.(U)Int*orFloat*orString
n seconds. Interval
Examples
Usage example
Query
Response
toIntervalWeek
Introduced in: v1.1.0 Returns an interval ofn weeks of data type IntervalWeek.
Syntax
n weeks. Interval
Examples
Usage example
Query
Response
toIntervalYear
Introduced in: v1.1.0 Returns an interval ofn years of data type IntervalYear.
Syntax
n years. Interval
Examples
Usage example
Query
Response
toLowCardinality
Introduced in: v18.12.0 Converts the input argument to the LowCardinality version of same data type. Syntaxexpr— Expression resulting in one of the supported data types.StringorFixedStringorDateorDateTimeor(U)Int*orFloat*
LowCardinality data type. LowCardinality
Examples
Usage example
Query
Response
toString
Introduced in: v1.1.0 Converts values to their string representation. For DateTime arguments, the function can take a second String argument containing the name of the time zone. Syntaxvalue— Value to convert to string.Anytimezone— Optional. Timezone name for DateTime conversion.String
String
Examples
Usage example
Query
Response
toStringCutToZero
Introduced in: v1.1.0 Accepts a String or FixedString argument and returns a String that contains a copy of the original string truncated at the first null byte. Null bytes (\0) are considered as string terminators. This function is useful for processing C-style strings or binary data where null bytes mark the end of meaningful content. Syntaxs— String or FixedString to process.StringorFixedString
String
Examples
Usage example
Query
Response
toTime
Introduced in: v1.1.0 Converts an input value to type Time. Supports conversion from String, FixedString, DateTime, or numeric types representing seconds since midnight. Syntaxx— Input value to convert.StringorFixedStringorDateTimeor(U)Int*orFloat*
Time
Examples
String to Time conversion
Query
Response
Query
Response
Query
Response
toTime64
Introduced in: v25.6.0 Converts an input value to type Time64. Supports conversion from String, FixedString, DateTime64, or numeric types representing microseconds since midnight. Provides microsecond precision for time values. Syntaxx— Input value to convert.StringorFixedStringorDateTime64or(U)Int*orFloat*
Time64(6)
Examples
String to Time64 conversion
Query
Response
Query
Response
Query
Response
toTime64OrNull
Introduced in: v25.6.0 Converts an input value to a value of typeTime64 but returns NULL in case of an error.
Like toTime64 but returns NULL instead of throwing an exception on conversion errors.
See also:
Syntax
x— A string representation of a time with subsecond precision.String
NULL. Time64 or NULL
Examples
Usage example
Query
Response
toTime64OrZero
Introduced in: v25.6.0 Converts an input value to a value of type Time64 but returns00:00:00.000 in case of an error.
Like toTime64 but returns 00:00:00.000 instead of throwing an exception on conversion errors.
Syntax
x— A string representation of a time with subsecond precision.String
00:00:00.000. Time64
Examples
Usage example
Query
Response
toTimeOrNull
Introduced in: v1.1.0 Converts an input value to a value of type Time but returnsNULL in case of an error.
Like toTime but returns NULL instead of throwing an exception on conversion errors.
See also:
Syntax
x— A string representation of a time.String
NULL. Time or NULL
Examples
Usage example
Query
Response
toTimeOrZero
Introduced in: v1.1.0 Converts an input value to a value of type Time but returns00:00:00 in case of an error.
Like toTime but returns 00:00:00 instead of throwing an exception on conversion errors.
Syntax
x— A string representation of a time.String
00:00:00. Time
Examples
Usage example
Query
Response
toUInt128
Introduced in: v1.1.0 Converts an input value to a value of typeUInt128.
Throws an exception in case of an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128('0xc0fe');.
If the input value cannot be represented within the bounds of UInt128, the result over or under flows.
This is not considered an error.
expr— Expression returning a number or a string representation of a number.Expression
UInt128
Examples
Usage example
Query
Response
toUInt128OrDefault
Introduced in: v21.11.0 LiketoUInt128, this function converts an input value to a value of type UInt128 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt128
UInt128
Examples
Successful conversion
Query
Response
Query
Response
toUInt128OrNull
Introduced in: v21.6.0 LiketoUInt128, this function converts an input value to a value of type UInt128 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt128, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
NULL if the conversion is unsuccessful. UInt128 or NULL
Examples
Usage example
Query
Response
toUInt128OrZero
Introduced in: v1.1.0 LiketoUInt128, this function converts an input value to a value of type UInt128 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt128, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt128
Examples
Usage example
Query
Response
toUInt16
Introduced in: v1.1.0 Converts an input value to a value of typeUInt16.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt16, overflow or underflow of the result occurs.
This is not considered an error.
For example: SELECT toUInt16(65536) == 0;.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
UInt16
Examples
Usage example
Query
Response
toUInt16OrDefault
Introduced in: v21.11.0 LiketoUInt16, this function converts an input value to a value of type UInt16 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt16
UInt16
Examples
Successful conversion
Query
Response
Query
Response
toUInt16OrNull
Introduced in: v1.1.0 LiketoUInt16, this function converts an input value to a value of type UInt16 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt16, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
UInt16, otherwise NULL if the conversion is unsuccessful. UInt16 or NULL
Examples
Usage example
Query
Response
toUInt16OrZero
Introduced in: v1.1.0 LiketoUInt16, this function converts an input value to a value of type UInt16 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt16, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt16
Examples
Usage example
Query
Response
toUInt256
Introduced in: v1.1.0 Converts an input value to a value of type UInt256. Throws an exception in case of an error. The function uses rounding towards zero, meaning it truncates fractional digits of numbers. Supported arguments:- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256('0xc0fe');.
If the input value cannot be represented within the bounds of UInt256, the result over or under flows.
This is not considered an error.
expr— Expression returning a number or a string representation of a number.Expression
UInt256
Examples
Usage example
Query
Response
toUInt256OrDefault
Introduced in: v21.11.0 LiketoUInt256, this function converts an input value to a value of type UInt256 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt256
UInt256
Examples
Successful conversion
Query
Response
Query
Response
toUInt256OrNull
Introduced in: v20.8.0 LiketoUInt256, this function converts an input value to a value of type UInt256 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt256, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
NULL if the conversion is unsuccessful. UInt256 or NULL
Examples
Usage example
Query
Response
toUInt256OrZero
Introduced in: v20.8.0 LiketoUInt256, this function converts an input value to a value of type UInt256 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt256, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt256
Examples
Usage example
Query
Response
toUInt32
Introduced in: v1.1.0 Converts an input value to a value of typeUInt32.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt32, the result over or under flows.
This is not considered an error.
For example: SELECT toUInt32(4294967296) == 0;The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
UInt32
Examples
Usage example
Query
Response
toUInt32OrDefault
Introduced in: v21.11.0 LiketoUInt32, this function converts an input value to a value of type UInt32 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt32
UInt32
Examples
Successful conversion
Query
Response
Query
Response
toUInt32OrNull
Introduced in: v1.1.0 LiketoUInt32, this function converts an input value to a value of type UInt32 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt32, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
UInt32, otherwise NULL if the conversion is unsuccessful. UInt32 or NULL
Examples
Usage example
Query
Response
toUInt32OrZero
Introduced in: v1.1.0 LiketoUInt32, this function converts an input value to a value of type UInt32 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt32, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt32
Examples
Usage example
Query
Response
toUInt64
Introduced in: v1.1.0 Converts an input value to a value of typeUInt64.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt64, the result over or under flows.
This is not considered an error.
For example: SELECT toUInt64(18446744073709551616) == 0;The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
UInt64
Examples
Usage example
Query
Response
toUInt64OrDefault
Introduced in: v21.11.0 LiketoUInt64, this function converts an input value to a value of type UInt64 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt64
UInt64
Examples
Successful conversion
Query
Response
Query
Response
toUInt64OrNull
Introduced in: v1.1.0 LiketoUInt64, this function converts an input value to a value of type UInt64 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int*.
NULL):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt64, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
NULL if the conversion is unsuccessful. UInt64 or NULL
Examples
Usage example
Query
Response
toUInt64OrZero
Introduced in: v1.1.0 LiketoUInt64, this function converts an input value to a value of type UInt64 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int*.
0):
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt64, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt64
Examples
Usage example
Query
Response
toUInt8
Introduced in: v1.1.0 Converts an input value to a value of typeUInt8.
Throws an exception in case of an error.
Supported arguments:
- Values or string representations of type (U)Int*.
- Values of type Float*.
- String representations of Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8('0xc0fe');.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs.
This is not considered an error.
For example:
SELECT toUInt8(256) == 0;.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
expr— Expression returning a number or a string representation of a number.Expression
UInt8
Examples
Usage example
Query
Response
toUInt8OrDefault
Introduced in: v21.11.0 LiketoUInt8, this function converts an input value to a value of type UInt8 but returns the default value in case of an error.
If no default value is passed then 0 is returned in case of an error.
Syntax
expr— Expression returning a number or a string representation of a number.Stringor(U)Int*orFloat*default— Optional. The default value to return if parsing is unsuccessful.UInt8
UInt8
Examples
Successful conversion
Query
Response
Query
Response
toUInt8OrNull
Introduced in: v1.1.0 LiketoUInt8, this function converts an input value to a value of type UInt8 but returns NULL in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
NULL):
- String representations of ordinary Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt8, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
NULL if the conversion is unsuccessful. UInt8 or NULL
Examples
Usage example
Query
Response
toUInt8OrZero
Introduced in: v1.1.0 LiketoUInt8, this function converts an input value to a value of type UInt8 but returns 0 in case of an error.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
0):
- String representations of ordinary Float* values, including
NaNandInf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of
UInt8, overflow or underflow of the result occurs.
This is not considered an error.x— A String representation of a number.String
0 if the conversion is unsuccessful. UInt8
Examples
Usage example
Query
Response
toUUID
Introduced in: v1.1.0 Converts a String value to a UUID value. Syntaxstring— UUID as a string.StringorFixedString
UUID
Examples
Usage example
Query
Response
toUUIDOrZero
Introduced in: v20.12.0 Converts an input value to a value of type UUID but returns zero UUID in case of an error. LiketoUUID but returns zero UUID (00000000-0000-0000-0000-000000000000) instead of throwing an exception on conversion errors.
Supported arguments:
- String representations of UUID in standard format (8-4-4-4-12 hexadecimal digits).
- String representations of UUID without hyphens (32 hexadecimal digits).
- Invalid string formats.
- Non-string types.
x— A string representation of a UUID.String
00000000-0000-0000-0000-000000000000). UUID
Examples
Usage example
Query
Response
toUnixTimestamp64Micro
Introduced in: v20.5.0 Converts aDateTime64 to a Int64 value with fixed microsecond precision.
The input value is scaled up or down appropriately depending on its precision.
The output value is relative to UTC, not to the timezone of the input value.
value— DateTime64 value with any precision.DateTime64
Int64
Examples
Usage example
Query
Response
toUnixTimestamp64Milli
Introduced in: v20.5.0 Converts aDateTime64 to a Int64 value with fixed millisecond precision.
The input value is scaled up or down appropriately depending on its precision.
The output value is relative to UTC, not to the timezone of the input value.
value— DateTime64 value with any precision.DateTime64
Int64
Examples
Usage example
Query
Response
toUnixTimestamp64Nano
Introduced in: v20.5.0 Converts aDateTime64 to a Int64 value with fixed nanosecond precision.
The input value is scaled up or down appropriately depending on its precision.
The output value is relative to UTC, not to the timezone of the input value.
value— DateTime64 value with any precision.DateTime64
Int64
Examples
Usage example
Query
Response
toUnixTimestamp64Second
Introduced in: v24.12.0 Converts aDateTime64 to a Int64 value with fixed second precision.
The input value is scaled up or down appropriately depending on its precision.
The output value is relative to UTC, not to the timezone of the input value.
value— DateTime64 value with any precision.DateTime64
Int64
Examples
Usage example
Query
Response