> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Documentation for type conversion functions
# Type conversion functions
Common issues with data conversion
ClickHouse generally uses the [same behavior as C++ programs](https://en.cppreference.com/w/cpp/language/implicit_conversion).
`to` functions and [cast](#CAST) behave differently in some cases, for example in case of [LowCardinality](/reference/data-types/lowcardinality): [cast](#CAST) removes [LowCardinality](/reference/data-types/lowcardinality) trait `to` functions don't. The same with [Nullable](/reference/data-types/nullable), this behaviour is not compatible with SQL standard, and it can be changed using [cast\_keep\_nullable](/reference/settings/session-settings#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.
Example:
```sql theme={null}
SELECT
toTypeName(toLowCardinality('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
┌─source_type────────────┬─to_type_result_type────┬─cast_result_type─┐
│ LowCardinality(String) │ LowCardinality(String) │ String │
└────────────────────────┴────────────────────────┴──────────────────┘
SELECT
toTypeName(toNullable('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String) │ String │
└──────────────────┴─────────────────────┴──────────────────┘
SELECT
toTypeName(toNullable('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
SETTINGS cast_keep_nullable = 1
┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String) │ Nullable(String) │
└──────────────────┴─────────────────────┴──────────────────┘
```
Notes on `toString` functions
The `toString` family of functions allows for converting between numbers, strings (but not fixed strings), dates, and dates with times.
All of these functions accept one argument.
* When converting to or from a string, the value is formatted or parsed using the same rules as for the TabSeparated format (and almost all other text formats). If the string can't be parsed, an exception is thrown and the request is canceled.
* When converting dates to numbers or vice versa, the date corresponds to the number of days since the beginning of the Unix epoch.
* When converting dates with times to numbers or vice versa, the date with time corresponds to the number of seconds since the beginning of the Unix epoch.
* The `toString` function of the `DateTime` argument can take a second String argument containing the name of the time zone, for example: `Europe/Amsterdam`. In this case, the time is formatted according to the specified time zone.
Notes on `toDate`/`toDateTime` functions
The date and date-with-time formats for the `toDate`/`toDateTime` functions are defined as follows:
```response theme={null}
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
```
As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date.
This allows support for the common occurrence of writing `toDate(unix_timestamp)`, which otherwise would be an error and would require writing the more cumbersome `toDate(toDateTime(unix_timestamp))`.
Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.
Conversion between numeric types uses the same rules as assignments between different numeric types in C++.
**Example**
```sql title="Query" theme={null}
SELECT
now() AS ts,
time_zone,
toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10
```
```response title="Response" theme={null}
┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast │ 2023-09-08 20:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belgrade │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Berlin │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bratislava │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Brussels │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bucharest │ 2023-09-08 22:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘
```
Also see the [`toUnixTimestamp`](/reference/functions/regular-functions/date-time-functions#toUnixTimestamp) function.
{/*AUTOGENERATED_START*/}
CAST
Introduced in: v1.1.0
Converts a value to a specified data type.
Unlike the reinterpret function, CAST tries to generate the same value in the target type.
If that is not possible, an exception is raised.
**Syntax**
```sql theme={null}
CAST(x, T)
or CAST(x AS T)
or x::T
```
**Arguments**
* `x` — A value of any type. [`Any`](/reference/data-types)
* `T` — The target data type. [`String`](/reference/data-types/string)
**Returned value**
Returns the converted value with the target data type. [`Any`](/reference/data-types)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT CAST(42, 'String')
```
```response title=Response theme={null}
┌─CAST(42, 'String')─┐
│ 42 │
└────────────────────┘
```
**Using AS syntax**
```sql title=Query theme={null}
SELECT CAST('2025-01-01' AS Date)
```
```response title=Response theme={null}
┌─CAST('2025-01-01', 'Date')─┐
│ 2025-01-01 │
└────────────────────────────┘
```
**Using :: syntax**
```sql title=Query theme={null}
SELECT '123'::UInt32
```
```response title=Response theme={null}
┌─CAST('123', 'UInt32')─┐
│ 123 │
└───────────────────────┘
```
DATE
Introduced in: v21.2.0
Converts the argument to the Date data type. This is a MySQL compatibility alias for `toDate`. It behaves the same as `toDate`.
**Syntax**
```sql theme={null}
DATE(expr)
```
**Arguments**
* `expr` — The value to convert. [`String`](/reference/data-types/string) or [`UInt32`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns a Date value. [`Date`](/reference/data-types/date)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT DATE('2023-01-01')
```
```response title=Response theme={null}
2023-01-01
```
accurateCast
Introduced in: v1.1.0
Converts a value to a specified data type. Unlike [`CAST`](#CAST), `accurateCast` performs stricter type checking and throws an exception if the conversion would result in a loss of data precision or if the conversion is not possible.
This function is safer than regular `CAST` as it prevents precision loss and invalid conversions.
**Syntax**
```sql theme={null}
accurateCast(x, T)
```
**Arguments**
* `x` — A value to convert. [`Any`](/reference/data-types)
* `T` — The target data type name. [`String`](/reference/data-types/string)
**Returned value**
Returns the converted value with the target data type. [`Any`](/reference/data-types)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT accurateCast(42, 'UInt16')
```
```response title=Response theme={null}
┌─accurateCast(42, 'UInt16')─┐
│ 42 │
└───────────────────────────┘
```
**String to number**
```sql title=Query theme={null}
SELECT accurateCast('123.45', 'Float64')
```
```response title=Response theme={null}
┌─accurateCast('123.45', 'Float64')─┐
│ 123.45 │
└───────────────────────────────────┘
```
accurateCastOrDefault
Introduced in: v21.1.0
Converts a value to a specified data type.
Like [`accurateCast`](#accurateCast), but returns a default value instead of throwing an exception if the conversion cannot be performed accurately.
If a default value is provided as the second argument, it must be of the target type.
If no default value is provided, the default value of the target type is used.
**Syntax**
```sql theme={null}
accurateCastOrDefault(x, T[, default_value])
```
**Arguments**
* `x` — A value to convert. [`Any`](/reference/data-types)
* `T` — The target data type name. [`const String`](/reference/data-types/string)
* `default_value` — Optional. Default value to return if conversion fails. [`Any`](/reference/data-types)
**Returned value**
Returns the converted value with the target data type, or the default value if conversion is not possible. [`Any`](/reference/data-types)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT accurateCastOrDefault(42, 'String')
```
```response title=Response theme={null}
┌─accurateCastOrDefault(42, 'String')─┐
│ 42 │
└─────────────────────────────────────┘
```
**Failed conversion with explicit default**
```sql title=Query theme={null}
SELECT accurateCastOrDefault('abc', 'UInt32', 999::UInt32)
```
```response title=Response theme={null}
┌─accurateCastOrDefault('abc', 'UInt32', 999)─┐
│ 999 │
└─────────────────────────────────────────────┘
```
**Failed conversion with implicit default**
```sql title=Query theme={null}
SELECT accurateCastOrDefault('abc', 'UInt32')
```
```response title=Response theme={null}
┌─accurateCastOrDefault('abc', 'UInt32')─┐
│ 0 │
└────────────────────────────────────────┘
```
accurateCastOrNull
Introduced in: v1.1.0
Converts a value to a specified data type.
Like [`accurateCast`](#accurateCast), but returns `NULL` instead of throwing an exception if the conversion cannot be performed accurately.
This function combines the safety of [`accurateCast`](#accurateCast) with graceful error handling.
**Syntax**
```sql theme={null}
accurateCastOrNull(x, T)
```
**Arguments**
* `x` — A value to convert. [`Any`](/reference/data-types)
* `T` — The target data type name. [`String`](/reference/data-types/string)
**Returned value**
Returns the converted value with the target data type, or `NULL` if conversion is not possible. [`Any`](/reference/data-types)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT accurateCastOrNull(42, 'String')
```
```response title=Response theme={null}
┌─accurateCastOrNull(42, 'String')─┐
│ 42 │
└──────────────────────────────────┘
```
**Failed conversion returns NULL**
```sql title=Query theme={null}
SELECT accurateCastOrNull('abc', 'UInt32')
```
```response title=Response theme={null}
┌─accurateCastOrNull('abc', 'UInt32')─┐
│ ᴺᵁᴸᴸ │
└─────────────────────────────────────┘
```
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.
**Syntax**
```sql theme={null}
formatRow(format, x, y, ...)
```
**Arguments**
* `format` — Text format. For example, CSV, TSV. [`String`](/reference/data-types/string)
* `x, y, ...` — Expressions. [`Any`](/reference/data-types)
**Returned value**
A formatted string. (for text formats it's usually terminated with the new line character). [`String`](/reference/data-types/string)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT formatRow('CSV', number, 'good')
FROM numbers(3)
```
```response title=Response theme={null}
┌─formatRow('CSV', number, 'good')─┐
│ 0,"good"
│
│ 1,"good"
│
│ 2,"good"
│
└──────────────────────────────────┘
```
**With custom format**
```sql title=Query theme={null}
SELECT formatRow('CustomSeparated', number, 'good')
FROM numbers(3)
SETTINGS format_custom_result_before_delimiter='\n', format_custom_result_after_delimiter=''
```
```response title=Response theme={null}
┌─formatRow('CustomSeparated', number, 'good')─┐
│
0 good
│
│
1 good
│
│
2 good
│
└──────────────────────────────────────────────┘
```
Introduced in: v20.7.0
Same as [`formatRow`](#formatRow), but trims the newline character of each row.
Converts arbitrary expressions into a string via given format, but removes any trailing newline characters from the result.
**Syntax**
```sql theme={null}
formatRowNoNewline(format, x, y, ...)
```
**Arguments**
* `format` — Text format. For example, CSV, TSV. [`String`](/reference/data-types/string)
* `x, y, ...` — Expressions. [`Any`](/reference/data-types)
**Returned value**
Returns a formatted string with newlines removed. [`String`](/reference/data-types/string)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3)
```
```response title=Response theme={null}
┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good" │
│ 1,"good" │
│ 2,"good" │
└───────────────────────────────────────────┘
```
fromUnixTimestamp64Micro
Introduced in: v20.5.0
Converts a Unix timestamp in microseconds to a `DateTime64` value with microsecond precision.
The input value is treated as a Unix timestamp with microsecond precision (number of microseconds since 1970-01-01 00:00:00 UTC).
**Syntax**
```sql theme={null}
fromUnixTimestamp64Micro(value[, timezone])
```
**Arguments**
* `value` — Unix timestamp in microseconds. [`Int64`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime64` value with microsecond precision. [`DateTime64(6)`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT fromUnixTimestamp64Micro(1640995200123456)
```
```response title=Response theme={null}
┌─fromUnixTimestamp64Micro(1640995200123456)─┐
│ 2022-01-01 00:00:00.123456 │
└────────────────────────────────────────────┘
```
fromUnixTimestamp64Milli
Introduced in: v20.5.0
Converts a Unix timestamp in milliseconds to a `DateTime64` value with millisecond precision.
The input value is treated as a Unix timestamp with millisecond precision (number of milliseconds since 1970-01-01 00:00:00 UTC).
**Syntax**
```sql theme={null}
fromUnixTimestamp64Milli(value[, timezone])
```
**Arguments**
* `value` — Unix timestamp in milliseconds. [`Int64`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone for the returned value. [`String`](/reference/data-types/string)
**Returned value**
A `DateTime64` value with millisecond precision. [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT fromUnixTimestamp64Milli(1640995200123)
```
```response title=Response theme={null}
┌─fromUnixTimestamp64Milli(1640995200123)─┐
│ 2022-01-01 00:00:00.123 │
└─────────────────────────────────────────┘
```
fromUnixTimestamp64Nano
Introduced in: v20.5.0
Converts a Unix timestamp in nanoseconds to a [`DateTime64`](/reference/data-types/datetime64) value with nanosecond precision.
The input value is treated as a Unix timestamp with nanosecond precision (number of nanoseconds since 1970-01-01 00:00:00 UTC).
Please note that the input value is treated as a UTC timestamp, not the timezone of the input value.
**Syntax**
```sql theme={null}
fromUnixTimestamp64Nano(value[, timezone])
```
**Arguments**
* `value` — Unix timestamp in nanoseconds. [`Int64`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime64` value with nanosecond precision. [`DateTime64(9)`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT fromUnixTimestamp64Nano(1640995200123456789)
```
```response title=Response theme={null}
┌─fromUnixTimestamp64Nano(1640995200123456789)─┐
│ 2022-01-01 00:00:00.123456789 │
└──────────────────────────────────────────────┘
```
fromUnixTimestamp64Second
Introduced in: v24.12.0
Converts a Unix timestamp in seconds to a `DateTime64` value with second precision.
The input value is treated as a Unix timestamp with second precision (number of seconds since 1970-01-01 00:00:00 UTC).
**Syntax**
```sql theme={null}
fromUnixTimestamp64Second(value[, timezone])
```
**Arguments**
* `value` — Unix timestamp in seconds. [`Int64`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime64` value with second precision. [`DateTime64(0)`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT fromUnixTimestamp64Second(1640995200)
```
```response title=Response theme={null}
┌─fromUnixTimestamp64Second(1640995200)─┐
│ 2022-01-01 00:00:00 │
└───────────────────────────────────────┘
```
parseDateTime
Introduced in: v23.3.0
Parses a date and time string according to a MySQL date format string.
This function is the inverse of [`formatDateTime`](/reference/functions/regular-functions/date-time-functions).
It parses a String argument using a format String. Returns a DateTime type.
**Syntax**
```sql theme={null}
parseDateTime(time_string, format[, timezone])
```
**Aliases**: `TO_UNIXTIME`
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime parsed from the input string according to the MySQL style format string. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')
```
```response title=Response theme={null}
┌─parseDateTime('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│ 2025-01-04 23:00:00 │
└───────────────────────────────────────────────────────────┘
```
parseDateTime32BestEffort
Introduced in: v20.9.0
Converts a string representation of a date and time to the [`DateTime`](/reference/data-types/datetime) data type.
The function parses [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601), [RFC 1123 - 5.2.14 RFC-822 Date and Time Specification](https://tools.ietf.org/html/rfc1123#page-55), ClickHouse's and some other date and time formats.
**Syntax**
```sql theme={null}
parseDateTime32BestEffort(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a `DateTime`. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime32BestEffort('23/10/2025 12:12:57')
AS parseDateTime32BestEffort
```
```response title=Response theme={null}
┌─parseDateTime32BestEffort─┐
│ 2025-10-23 12:12:57 │
└───────────────────────────┘
```
**With timezone**
```sql title=Query theme={null}
SELECT parseDateTime32BestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul')
AS parseDateTime32BestEffort
```
```response title=Response theme={null}
┌─parseDateTime32BestEffort─┐
│ 2025-08-18 10:22:16 │
└───────────────────────────┘
```
**Unix timestamp**
```sql title=Query theme={null}
SELECT parseDateTime32BestEffort('1284101485')
AS parseDateTime32BestEffort
```
```response title=Response theme={null}
┌─parseDateTime32BestEffort─┐
│ 2015-07-07 12:04:41 │
└───────────────────────────┘
```
parseDateTime32BestEffortOrNull
Introduced in: v20.9.0
Same as [`parseDateTime32BestEffort`](#parseDateTime32BestEffort) except that it returns `NULL` when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime32BestEffortOrNull(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime` object parsed from the string, or `NULL` if the parsing fails. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
parseDateTime32BestEffortOrNull('23/10/2025 12:12:57') AS valid,
parseDateTime32BestEffortOrNull('invalid date') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────┬─invalid─┐
│ 2025-10-23 12:12:57 │ ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘
```
parseDateTime32BestEffortOrZero
Introduced in: v20.9.0
Same as [`parseDateTime32BestEffort`](#parseDateTime32BestEffort) except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime32BestEffortOrZero(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime` object parsed from the string, or zero date (`1970-01-01 00:00:00`) if the parsing fails. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
parseDateTime32BestEffortOrZero('23/10/2025 12:12:57') AS valid,
parseDateTime32BestEffortOrZero('invalid date') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────┬─invalid─────────────┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
```
parseDateTime64
Introduced in: v24.11.0
Parses a date and time string with sub-second precision according to a MySQL date format string.
This function is the inverse of [`formatDateTime`](/reference/functions/regular-functions/date-time-functions) 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**
```sql theme={null}
parseDateTime64(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime64 parsed from the input string according to the MySQL style format string. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')
```
```response title=Response theme={null}
┌─parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│ 2025-01-04 23:00:00.123 │
└─────────────────────────────────────────────────────────────────────┘
```
parseDateTime64BestEffort
Introduced in: v20.1.0
Same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) function but also parse milliseconds and microseconds and returns [`DateTime64`](/reference/data-types/datetime64) data type.
**Syntax**
```sql theme={null}
parseDateTime64BestEffort(time_string[, precision[, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to the [`DateTime64`](/reference/data-types/datetime64) data type. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffort('2025-01-01') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',6) AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2025-01-01 01:01:00.12346',3,'Asia/Istanbul') AS a, toTypeName(a) AS t
FORMAT PrettyCompactMonoBlock
```
```response title=Response theme={null}
┌──────────────────────────a─┬─t──────────────────────────────┐
│ 2025-01-01 01:01:00.123000 │ DateTime64(3) │
│ 2025-01-01 00:00:00.000000 │ DateTime64(3) │
│ 2025-01-01 01:01:00.123460 │ DateTime64(6) │
│ 2025-12-31 22:01:00.123000 │ DateTime64(3, 'Asia/Istanbul') │
└────────────────────────────┴────────────────────────────────┘
```
parseDateTime64BestEffortOrNull
Introduced in: v20.1.0
Same as [`parseDateTime64BestEffort`](#parseDateTime64BestEffort) except that it returns `NULL` when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime64BestEffortOrNull(time_string[, precision[, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to [`DateTime64`](/reference/data-types/datetime64), or `NULL` if the input cannot be parsed. [`DateTime64`](/reference/data-types/datetime64) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffortOrNull('2025-01-01 01:01:00.123') AS valid,
parseDateTime64BestEffortOrNull('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────────┬─invalid─┐
│ 2025-01-01 01:01:00.123 │ ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘
```
parseDateTime64BestEffortOrZero
Introduced in: v20.1.0
Same as [`parseDateTime64BestEffort`](#parseDateTime64BestEffort) except that it returns zero date or zero date time when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime64BestEffortOrZero(time_string[, precision[, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to [`DateTime64`](/reference/data-types/datetime64), or zero date/datetime (`1970-01-01 00:00:00.000`) if the input cannot be parsed. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffortOrZero('2025-01-01 01:01:00.123') AS valid,
parseDateTime64BestEffortOrZero('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────────┬─invalid─────────────────┐
│ 2025-01-01 01:01:00.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘
```
parseDateTime64BestEffortUS
Introduced in: v22.8.0
Same as [`parseDateTime64BestEffort`](#parseDateTime64BestEffort), except that this function prefers US date format (`MM/DD/YYYY` etc.) in case of ambiguity.
**Syntax**
```sql theme={null}
parseDateTime64BestEffortUS(time_string [, precision [, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to [`DateTime64`](/reference/data-types/datetime64) using US date format preference for ambiguous cases. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffortUS('02/10/2025 12:30:45.123') AS us_format,
parseDateTime64BestEffortUS('15/08/2025 10:15:30.456') AS fallback_to_standard
```
```response title=Response theme={null}
┌─us_format───────────────┬─fallback_to_standard────┐
│ 2025-02-10 12:30:45.123 │ 2025-08-15 10:15:30.456 │
└─────────────────────────┴─────────────────────────┘
```
parseDateTime64BestEffortUSOrNull
Introduced in: v22.8.0
Same as [`parseDateTime64BestEffort`](#parseDateTime64BestEffort), except that this function prefers US date format (`MM/DD/YYYY` etc.) in case of ambiguity and returns `NULL` when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime64BestEffortUSOrNull(time_string[, precision[, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to [`DateTime64`](/reference/data-types/datetime64) using US format preference, or `NULL` if the input cannot be parsed. [`DateTime64`](/reference/data-types/datetime64) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffortUSOrNull('02/10/2025 12:30:45.123') AS valid_us,
parseDateTime64BestEffortUSOrNull('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid_us────────────────┬─invalid─┐
│ 2025-02-10 12:30:45.123 │ ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘
```
parseDateTime64BestEffortUSOrZero
Introduced in: v22.8.0
Same as [`parseDateTime64BestEffort`](#parseDateTime64BestEffort), except that this function prefers US date format (`MM/DD/YYYY` etc.) in case of ambiguity and returns zero date or zero date time when it encounters a date format that cannot be processed.
**Syntax**
```sql theme={null}
parseDateTime64BestEffortUSOrZero(time_string [, precision [, time_zone]])
```
**Arguments**
* `time_string` — String containing a date or date with time to convert. [`String`](/reference/data-types/string)
* `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/reference/data-types/int-uint)
* `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` converted to [`DateTime64`](/reference/data-types/datetime64) using US format preference, or zero date/datetime (`1970-01-01 00:00:00.000`) if the input cannot be parsed. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64BestEffortUSOrZero('02/10/2025 12:30:45.123') AS valid_us,
parseDateTime64BestEffortUSOrZero('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid_us────────────────┬─invalid─────────────────┐
│ 2025-02-10 12:30:45.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘
```
parseDateTime64InJodaSyntax
Introduced in: v24.10.0
Parses a date and time string with sub-second precision according to a Joda date format string.
This function is the inverse of [`formatDateTimeInJodaSyntax`](/reference/functions/regular-functions/date-time-functions#formatDateTimeInJodaSyntax) 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](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) for the format patterns.
**Syntax**
```sql theme={null}
parseDateTime64InJodaSyntax(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime64 parsed from the input string according to the Joda style format string. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64InJodaSyntax('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')
```
```response title=Response theme={null}
┌─parseDateTime64InJodaSyntax('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│ 2025-01-04 23:00:00.123 │
└────────────────────────────────────────────────────────────────────────────────────┘
```
parseDateTime64InJodaSyntaxOrNull
Introduced in: v24.10.0
Same as [`parseDateTime64InJodaSyntax`](#parseDateTime64InJodaSyntax) but returns `NULL` when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTime64InJodaSyntaxOrNull(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime64 parsed from input string, or NULL if parsing fails. [`Nullable(DateTime64)`](/reference/data-types/nullable)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64InJodaSyntaxOrNull('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')
```
```response title=Response theme={null}
┌─parseDateTime64InJodaSyntaxOrNull('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│ 2025-01-04 23:00:00.123 │
└──────────────────────────────────────────────────────────────────────────────────────────┘
```
parseDateTime64InJodaSyntaxOrZero
Introduced in: v24.10.0
Same as [`parseDateTime64InJodaSyntax`](#parseDateTime64InJodaSyntax) but returns zero date when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTime64InJodaSyntaxOrZero(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime64 parsed from input string, or zero DateTime64 if parsing fails. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')
```
```response title=Response theme={null}
┌─parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│ 2025-01-04 23:00:00.123 │
└──────────────────────────────────────────────────────────────────────────────────────────┘
```
parseDateTime64OrNull
Introduced in: v24.11.0
Same as [`parseDateTime64`](#parseDateTime64) but returns `NULL` when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTime64OrNull(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime64 parsed from input string, or NULL if parsing fails. [`Nullable(DateTime64)`](/reference/data-types/nullable)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64OrNull('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')
```
```response title=Response theme={null}
┌─parseDateTime64OrNull('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│ 2025-01-04 23:00:00.123 │
└───────────────────────────────────────────────────────────────────────────┘
```
parseDateTime64OrZero
Introduced in: v24.11.0
Same as [`parseDateTime64`](#parseDateTime64) but returns zero date when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTime64OrZero(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime64. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime64 parsed from input string, or zero DateTime64 if parsing fails. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTime64OrZero('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')
```
```response title=Response theme={null}
┌─parseDateTime64OrZero('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│ 2025-01-04 23:00:00.123 │
└───────────────────────────────────────────────────────────────────────────┘
```
parseDateTimeBestEffort
Introduced in: v1.1.0
Converts a date and time in the String representation to DateTime data type.
The function parses [ISO 8601](https://www.iso.org/iso-8601-date-and-time-format.html), [RFC 1123 - 5.2.14 RFC-822](https://datatracker.ietf.org/doc/html/rfc822) Date and Time Specification, ClickHouse's and some other date and time formats.
Supported non-standard formats:
* A string containing 9..10 digit unix timestamp.
* A string with a date and a time component: `YYYYMMDDhhmmss`, `DD/MM/YYYY hh:mm:ss`, `DD-MM-YY hh:mm`, `YYYY-MM-DD hh:mm:ss`, etc.
* A string with a date, but no time component: `YYYY`, `YYYYMM`, `YYYY*MM`, `DD/MM/YYYY`, `DD-MM-YY` etc.
* A string with a day and time: `DD`, `DD hh`, `DD hh:mm`. In this case `MM` is substituted by `01`.
* A string that includes the date and time along with time zone offset information: `YYYY-MM-DD hh:mm:ss ±h:mm`, etc.
* A syslog timestamp: `Mmm dd hh:mm:ss`. For example, `Jun 9 14:20:32`.
For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name.
If the year is not specified, it is considered to be equal to the current year.
**Syntax**
```sql theme={null}
parseDateTimeBestEffort(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a `DateTime`. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffort('23/10/2025 12:12:57') AS parseDateTimeBestEffort
```
```response title=Response theme={null}
┌─parseDateTimeBestEffort─┐
│ 2025-10-23 12:12:57 │
└─────────────────────────┘
```
**With timezone**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffort
```
```response title=Response theme={null}
┌─parseDateTimeBestEffort─┐
│ 2025-08-18 10:22:16 │
└─────────────────────────┘
```
**Unix timestamp**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffort('1735689600') AS parseDateTimeBestEffort
```
```response title=Response theme={null}
┌─parseDateTimeBestEffort─┐
│ 2025-01-01 00:00:00 │
└─────────────────────────┘
```
parseDateTimeBestEffortOrNull
Introduced in: v1.1.0
The same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) except that it returns `NULL` when it encounters a date format that cannot be processed.
The function parses [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601), [RFC 1123 - 5.2.14 RFC-822 Date and Time Specification](https://tools.ietf.org/html/rfc1123#page-55), ClickHouse's and some other date and time formats.
Supported non-standard formats:
* A string containing 9..10 digit unix timestamp.
* A string with a date and a time component: `YYYYMMDDhhmmss`, `DD/MM/YYYY hh:mm:ss`, `DD-MM-YY hh:mm`, `YYYY-MM-DD hh:mm:ss`, etc.
* A string with a date, but no time component: `YYYY`, `YYYYMM`, `YYYY*MM`, `DD/MM/YYYY`, `DD-MM-YY` etc.
* A string with a day and time: `DD`, `DD hh`, `DD hh:mm`. In this case `MM` is substituted by `01`.
* A string that includes the date and time along with time zone offset information: `YYYY-MM-DD hh:mm:ss ±h:mm`, etc.
* A syslog timestamp: `Mmm dd hh:mm:ss`. For example, `Jun 9 14:20:32`.
For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name.
If the year is not specified, it is considered to be equal to the current year.
**Syntax**
```sql theme={null}
parseDateTimeBestEffortOrNull(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a DateTime, or `NULL` if the input cannot be parsed. [`DateTime`](/reference/data-types/datetime) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffortOrNull('23/10/2025 12:12:57') AS valid,
parseDateTimeBestEffortOrNull('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────┬─invalid─┐
│ 2025-10-23 12:12:57 │ ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘
```
parseDateTimeBestEffortOrZero
Introduced in: v1.1.0
Same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.
The function parses [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601), [RFC 1123 - 5.2.14 RFC-822 Date and Time Specification](https://tools.ietf.org/html/rfc1123#page-55), ClickHouse's and some other date and time formats.
Supported non-standard formats:
* A string containing 9..10 digit unix timestamp.
* A string with a date and a time component: `YYYYMMDDhhmmss`, `DD/MM/YYYY hh:mm:ss`, `DD-MM-YY hh:mm`, `YYYY-MM-DD hh:mm:ss`, etc.
* A string with a date, but no time component: `YYYY`, `YYYYMM`, `YYYY*MM`, `DD/MM/YYYY`, `DD-MM-YY` etc.
* A string with a day and time: `DD`, `DD hh`, `DD hh:mm`. In this case `MM` is substituted by `01`.
* A string that includes the date and time along with time zone offset information: `YYYY-MM-DD hh:mm:ss ±h:mm`, etc.
* A syslog timestamp: `Mmm dd hh:mm:ss`. For example, `Jun 9 14:20:32`.
For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name.
If the year is not specified, it is considered to be equal to the current year.
**Syntax**
```sql theme={null}
parseDateTimeBestEffortOrZero(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a `DateTime`, or zero date/datetime (`1970-01-01` or `1970-01-01 00:00:00`) if the input cannot be parsed. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffortOrZero('23/10/2025 12:12:57') AS valid,
parseDateTimeBestEffortOrZero('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid───────────────┬─invalid─────────────┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
```
parseDateTimeBestEffortUS
Introduced in: v1.1.0
This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, e.g. `YYYY-MM-DD hh:mm:ss`, and other date formats where the month and date components can be unambiguously extracted, e.g. `YYYYMMDDhhmmss`, `YYYY-MM`, `DD hh`, or `YYYY-MM-DD hh:mm:ss ±h:mm`.
If the month and the date components cannot be unambiguously extracted, e.g. `MM/DD/YYYY`, `MM-DD-YYYY`, or `MM-DD-YY`, it prefers the US date format instead of `DD/MM/YYYY`, `DD-MM-YYYY`, or `DD-MM-YY`.
As an exception to the previous statement, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of [`parseDateTimeBestEffort`](#parseDateTimeBestEffort), e.g. `15/08/2020` is parsed as `2020-08-15`.
**Syntax**
```sql theme={null}
parseDateTimeBestEffortUS(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a `DateTime` using US date format preference for ambiguous cases. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffortUS('02/10/2025') AS us_format,
parseDateTimeBestEffortUS('15/08/2025') AS fallback_to_standard
```
```response title=Response theme={null}
┌─us_format───────────┬─fallback_to_standard─┐
│ 2025-02-10 00:00:00 │ 2025-08-15 00:00:00 │
└─────────────────────┴──────────────────────┘
```
parseDateTimeBestEffortUSOrNull
Introduced in: v1.1.0
Same as [`parseDateTimeBestEffortUS`](#parseDateTimeBestEffortUS) function except that it returns `NULL` when it encounters a date format that cannot be processed.
This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, but prefers the US date format for ambiguous cases, with `NULL` return on parsing errors.
**Syntax**
```sql theme={null}
parseDateTimeBestEffortUSOrNull(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a DateTime using US format preference, or `NULL` if the input cannot be parsed. [`DateTime`](/reference/data-types/datetime) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffortUSOrNull('02/10/2025') AS valid_us,
parseDateTimeBestEffortUSOrNull('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid_us────────────┬─invalid─┐
│ 2025-02-10 00:00:00 │ ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘
```
parseDateTimeBestEffortUSOrZero
Introduced in: v1.1.0
Same as [`parseDateTimeBestEffortUS`](#parseDateTimeBestEffortUS) function except that it returns zero date (`1970-01-01`) or zero date with time (`1970-01-01 00:00:00`) when it encounters a date format that cannot be processed.
This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, but prefers the US date format for ambiguous cases, with zero return on parsing errors.
**Syntax**
```sql theme={null}
parseDateTimeBestEffortUSOrZero(time_string[, time_zone])
```
**Arguments**
* `time_string` — String containing a date and time to convert. [`String`](/reference/data-types/string)
* `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/reference/data-types/string)
**Returned value**
Returns `time_string` as a `DateTime` using US format preference, or zero date/datetime (`1970-01-01` or `1970-01-01 00:00:00`) if the input cannot be parsed. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeBestEffortUSOrZero('02/10/2025') AS valid_us,
parseDateTimeBestEffortUSOrZero('invalid') AS invalid
```
```response title=Response theme={null}
┌─valid_us────────────┬─invalid─────────────┐
│ 2025-02-10 00:00:00 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
```
parseDateTimeInJodaSyntax
Introduced in: v23.3.0
Parses a date and time string according to a Joda date format string.
This function is the inverse of [`formatDateTimeInJodaSyntax`](/reference/functions/regular-functions/date-time-functions#formatDateTimeInJodaSyntax).
It parses a String argument using a Joda-style format String. Returns a DateTime type.
Refer to [Joda Time documentation](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) for the format patterns.
**Syntax**
```sql theme={null}
parseDateTimeInJodaSyntax(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime parsed from the input string according to the Joda style format string. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeInJodaSyntax('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')
```
```response title=Response theme={null}
┌─parseDateTimeInJodaSyntax('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│ 2025-01-04 23:00:00 │
└──────────────────────────────────────────────────────────────────────────┘
```
parseDateTimeInJodaSyntaxOrNull
Introduced in: v23.3.0
Same as [`parseDateTimeInJodaSyntax`](#parseDateTimeInJodaSyntax) but returns `NULL` when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTimeInJodaSyntaxOrNull(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime parsed from input string, or NULL if parsing fails. [`Nullable(DateTime)`](/reference/data-types/nullable)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeInJodaSyntaxOrNull('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')
```
```response title=Response theme={null}
┌─parseDateTimeInJodaSyntaxOrNull('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│ 2025-01-04 23:00:00 │
└────────────────────────────────────────────────────────────────────────────────┘
```
parseDateTimeInJodaSyntaxOrZero
Introduced in: v23.3.0
Same as [`parseDateTimeInJodaSyntax`](#parseDateTimeInJodaSyntax) but returns zero date when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTimeInJodaSyntaxOrZero(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string in Joda syntax specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime parsed from input string, or zero DateTime if parsing fails. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeInJodaSyntaxOrZero('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')
```
```response title=Response theme={null}
┌─parseDateTimeInJodaSyntaxOrZero('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')─┐
│ 2025-01-04 23:00:00 │
└────────────────────────────────────────────────────────────────────────────────┘
```
parseDateTimeOrNull
Introduced in: v23.3.0
Same as [`parseDateTime`](#parseDateTime) but returns `NULL` when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTimeOrNull(time_string, format[, timezone])
```
**Aliases**: `str_to_date`
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime parsed from input string, or NULL if parsing fails. [`Nullable(DateTime)`](/reference/data-types/nullable)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeOrNull('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')
```
```response title=Response theme={null}
┌─parseDateTimeOrNull('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│ 2025-01-04 23:00:00 │
└─────────────────────────────────────────────────────────────────┘
```
parseDateTimeOrZero
Introduced in: v23.3.0
Same as [`parseDateTime`](#parseDateTime) but returns zero date when it encounters an unparsable date format.
**Syntax**
```sql theme={null}
parseDateTimeOrZero(time_string, format[, timezone])
```
**Arguments**
* `time_string` — String to be parsed into DateTime. [`String`](/reference/data-types/string)
* `format` — Format string specifying how to parse time\_string. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime parsed from input string, or zero DateTime if parsing fails. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseDateTimeOrZero('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')
```
```response title=Response theme={null}
┌─parseDateTimeOrZero('2025-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│ 2025-01-04 23:00:00 │
└─────────────────────────────────────────────────────────────────┘
```
reinterpret
Introduced in: v1.1.0
Uses the same source in-memory bytes sequence for the provided value `x` and reinterprets it to the destination type.
**Syntax**
```sql theme={null}
reinterpret(x, type)
```
**Arguments**
* `x` — Any type. [`Any`](/reference/data-types)
* `type` — Destination type. If it is an array, then the array element type must be a fixed length type. [`String`](/reference/data-types/string)
**Returned value**
Destination type value. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT reinterpret(toInt8(-1), 'UInt8') AS int_to_uint,
reinterpret(toInt8(1), 'Float32') AS int_to_float,
reinterpret('1', 'UInt32') AS string_to_int
```
```response title=Response theme={null}
┌─int_to_uint─┬─int_to_float─┬─string_to_int─┐
│ 255 │ 1e-45 │ 49 │
└─────────────┴──────────────┴───────────────┘
```
**Array example**
```sql title=Query theme={null}
SELECT reinterpret(x'3108b4403108d4403108b4403108d440', 'Array(Float32)') AS string_to_array_of_Float32
```
```response title=Response theme={null}
┌─string_to_array_of_Float32─┐
│ [5.626,6.626,5.626,6.626] │
└────────────────────────────┘
```
reinterpretAsDate
Introduced in: v1.1.0
Reinterprets the input value as a Date value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01
**Syntax**
```sql theme={null}
reinterpretAsDate(x)
```
**Arguments**
* `x` — Number of days since the beginning of the Unix Epoch. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Date. [`Date`](/reference/data-types/date)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT reinterpretAsDate(65), reinterpretAsDate('A')
```
```response title=Response theme={null}
┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐
│ 1970-03-07 │ 1970-03-07 │
└───────────────────────┴────────────────────────┘
```
reinterpretAsDateTime
Introduced in: v1.1.0
Reinterprets the input value as a DateTime value (assuming little endian order) which is the number of days since the beginning of the Unix epoch 1970-01-01
**Syntax**
```sql theme={null}
reinterpretAsDateTime(x)
```
**Arguments**
* `x` — Number of seconds since the beginning of the Unix Epoch. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Date and Time. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A')
```
```response title=Response theme={null}
┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐
│ 1970-01-01 01:01:05 │ 1970-01-01 01:01:05 │
└───────────────────────────┴────────────────────────────┘
```
reinterpretAsFixedString
Introduced in: v1.1.0
Reinterprets the input value as a fixed string (assuming little endian order).
Null bytes at the end are ignored, for example, the function returns for UInt32 value 255 a string with a single character.
**Syntax**
```sql theme={null}
reinterpretAsFixedString(x)
```
**Arguments**
* `x` — Value to reinterpret to string. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Fixed string containing bytes representing `x`. [`FixedString`](/reference/data-types/fixedstring)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')),
reinterpretAsFixedString(toDate('1970-03-07'))
```
```response title=Response theme={null}
┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐
│ A │ A │
└─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘
```
reinterpretAsFloat32
Introduced in: v1.1.0
Reinterprets the input value as a value of type Float32.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsFloat32(x)
```
**Arguments**
* `x` — Value to reinterpret as Float32. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Float32`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT reinterpretAsUInt32(toFloat32(0.2)) AS x, reinterpretAsFloat32(x)
```
```response title=Response theme={null}
┌──────────x─┬─reinterpretAsFloat32(x)─┐
│ 1045220557 │ 0.2 │
└────────────┴─────────────────────────┘
```
reinterpretAsFloat64
Introduced in: v1.1.0
Reinterprets the input value as a value of type Float64.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsFloat64(x)
```
**Arguments**
* `x` — Value to reinterpret as Float64. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT reinterpretAsUInt64(toFloat64(0.2)) AS x, reinterpretAsFloat64(x)
```
```response title=Response theme={null}
┌───────────────────x─┬─reinterpretAsFloat64(x)─┐
│ 4596373779694328218 │ 0.2 │
└─────────────────────┴─────────────────────────┘
```
reinterpretAsInt128
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int128.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt128(x)
```
**Arguments**
* `x` — Value to reinterpret as Int128. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt64(257) AS x,
toTypeName(x),
reinterpretAsInt128(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64 │ 257 │ Int128 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsInt16
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int16.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt16(x)
```
**Arguments**
* `x` — Value to reinterpret as Int16. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt8(257) AS x,
toTypeName(x),
reinterpretAsInt16(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8 │ 1 │ Int16 │
└───┴───────────────┴─────┴─────────────────┘
```
reinterpretAsInt256
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int256.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt256(x)
```
**Arguments**
* `x` — Value to reinterpret as Int256. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt128(257) AS x,
toTypeName(x),
reinterpretAsInt256(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int128 │ 257 │ Int256 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsInt32
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int32.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt32(x)
```
**Arguments**
* `x` — Value to reinterpret as Int32. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt16(257) AS x,
toTypeName(x),
reinterpretAsInt32(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16 │ 257 │ Int32 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsInt64
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int64.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt64(x)
```
**Arguments**
* `x` — Value to reinterpret as Int64. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt32(257) AS x,
toTypeName(x),
reinterpretAsInt64(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32 │ 257 │ Int64 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsInt8
Introduced in: v1.1.0
Reinterprets the input value as a value of type Int8.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsInt8(x)
```
**Arguments**
* `x` — Value to reinterpret as Int8. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`Int8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt8(257) AS x,
toTypeName(x),
reinterpretAsInt8(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8 │ 1 │ Int8 │
└───┴───────────────┴─────┴─────────────────┘
```
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.
**Syntax**
```sql theme={null}
reinterpretAsString(x)
```
**Arguments**
* `x` — Value to reinterpret to string. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
String containing bytes representing `x`. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
reinterpretAsString(toDateTime('1970-01-01 01:01:05')),
reinterpretAsString(toDate('1970-03-07'))
```
```response title=Response theme={null}
┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐
│ A │ A │
└────────────────────────────────────────────────────────┴───────────────────────────────────────────┘
```
reinterpretAsUInt128
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt128.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt128(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt128. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`UInt128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt64(257) AS x,
toTypeName(x),
reinterpretAsUInt128(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64 │ 257 │ UInt128 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsUInt16
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt16.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt16(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt16. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt8(257) AS x,
toTypeName(x),
reinterpretAsUInt16(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8 │ 1 │ UInt16 │
└───┴───────────────┴─────┴─────────────────┘
```
reinterpretAsUInt256
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt256.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt256(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt256. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`UInt256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt128(257) AS x,
toTypeName(x),
reinterpretAsUInt256(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128 │ 257 │ UInt256 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsUInt32
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt32.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt32(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt32. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt16(257) AS x,
toTypeName(x),
reinterpretAsUInt32(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16 │ 257 │ UInt32 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsUInt64
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt64.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt64(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt64. [`Int*`](/reference/data-types/int-uint) or [`UInt*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value of `x`. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt32(257) AS x,
toTypeName(x),
reinterpretAsUInt64(x) AS res,
toTypeName(res)
```
```response title=Response theme={null}
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32 │ 257 │ UInt64 │
└─────┴───────────────┴─────┴─────────────────┘
```
reinterpretAsUInt8
Introduced in: v1.1.0
Reinterprets the input value as a value of type UInt8.
Unlike [`CAST`](#CAST), the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is undefined.
**Syntax**
```sql theme={null}
reinterpretAsUInt8(x)
```
**Arguments**
* `x` — Value to reinterpret as UInt8. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`UUID`](/reference/data-types/uuid) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the reinterpreted value `x`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt8(-1) AS val,
toTypeName(val),
reinterpretAsUInt8(val) AS res,
toTypeName(res);
```
```response title=Response theme={null}
┌─val─┬─toTypeName(val)─┬─res─┬─toTypeName(res)─┐
│ -1 │ Int8 │ 255 │ UInt8 │
└─────┴─────────────────┴─────┴─────────────────┘
```
reinterpretAsUUID
Introduced in: v1.1.0
Accepts a 16 byte string and returns a UUID by interpreting each 8-byte half in little-endian byte order. If the string isn't long enough, the function works as if the string is padded with the necessary number of null bytes to the end. If the string is longer than 16 bytes, the extra bytes at the end are ignored.
**Syntax**
```sql theme={null}
reinterpretAsUUID(fixed_string)
```
**Arguments**
* `fixed_string` — Big-endian byte string. [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
The UUID type value. [`UUID`](/reference/data-types/uuid)
**Examples**
**String to UUID**
```sql title=Query theme={null}
SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))
```
```response title=Response theme={null}
┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│ 08090a0b-0c0d-0e0f-0001-020304050607 │
└───────────────────────────────────────────────────────────────────────┘
```
toBFloat16
Introduced in: v1.1.0
Converts an input value to a value of type BFloat16.
Throws an exception in case of an error.
See also:
* [`toBFloat16OrZero`](#toBFloat16OrZero).
* [`toBFloat16OrNull`](#toBFloat16OrNull).
**Syntax**
```sql theme={null}
toBFloat16(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 16-bit brain-float value. [`BFloat16`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toBFloat16(toFloat32(42.7)),
toBFloat16(toFloat32('42.7')),
toBFloat16('42.7')
FORMAT Vertical;
```
```response title=Response theme={null}
toBFloat16(toFloat32(42.7)): 42.5
toBFloat16(t⋯32('42.7')): 42.5
toBFloat16('42.7'): 42.5
```
toBFloat16OrNull
Introduced in: v1.1.0
Converts a String input value to a value of type BFloat16.
If the string does not represent a floating point value, the function returns NULL.
Supported arguments:
* String representations of numeric values.
Unsupported arguments (return `NULL`):
* String representations of binary and hexadecimal values.
* Numeric values.
The function allows a silent loss of precision while converting from the string representation.
See also:
* [`toBFloat16`](#toBFloat16).
* [`toBFloat16OrZero`](#toBFloat16OrZero).
**Syntax**
```sql theme={null}
toBFloat16OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Reurns a 16-bit brain-float value, otherwise `NULL`. [`BFloat16`](/reference/data-types/float) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toBFloat16OrNull('0x5E'), -- unsupported arguments
toBFloat16OrNull('12.3'), -- typical use
toBFloat16OrNull('12.3456789') -- silent loss of precision
```
```response title=Response theme={null}
\N
12.25
12.3125
```
toBFloat16OrZero
Introduced in: v1.1.0
Converts a String input value to a value of type BFloat16.
If the string does not represent a floating point value, the function returns zero.
Supported arguments:
* String representations of numeric values.
Unsupported arguments (return `0`):
* String representations of binary and hexadecimal values.
* Numeric values.
The function allows a silent loss of precision while converting from the string representation.
See also:
* [`toBFloat16`](#toBFloat16).
* [`toBFloat16OrNull`](#toBFloat16OrNull).
**Syntax**
```sql theme={null}
toBFloat16OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a 16-bit brain-float value, otherwise `0`. [`BFloat16`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toBFloat16OrZero('0x5E'), -- unsupported arguments
toBFloat16OrZero('12.3'), -- typical use
toBFloat16OrZero('12.3456789') -- silent loss of precision
```
```response title=Response theme={null}
0
12.25
12.3125
```
toBool
Introduced in: v22.2.0
Converts an input value to a value of type Bool.
**Syntax**
```sql theme={null}
toBool(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string. For strings, accepts 'true' or 'false' (case-insensitive). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string) or [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns `true` or `false` based on evaluation of the argument. [`Bool`](/reference/data-types/boolean)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toBool(toUInt8(1)),
toBool(toInt8(-1)),
toBool(toFloat32(1.01)),
toBool('true'),
toBool('false'),
toBool('FALSE')
FORMAT Vertical
```
```response title=Response theme={null}
toBool(toUInt8(1)): true
toBool(toInt8(-1)): true
toBool(toFloat32(1.01)): true
toBool('true'): true
toBool('false'): false
toBool('FALSE'): false
```
toDate
Introduced in: v1.1.0
Converts an input value to type [`Date`](/reference/data-types/date).
Supports conversion from String, FixedString, DateTime, or numeric types.
**Syntax**
```sql theme={null}
toDate(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`DateTime`](/reference/data-types/datetime) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns the converted input value. [`Date`](/reference/data-types/date)
**Examples**
**String to Date conversion**
```sql title=Query theme={null}
SELECT toDate('2025-04-15')
```
```response title=Response theme={null}
2025-04-15
```
**DateTime to Date conversion**
```sql title=Query theme={null}
SELECT toDate(toDateTime('2025-04-15 10:30:00'))
```
```response title=Response theme={null}
2025-04-15
```
**Integer to Date conversion**
```sql title=Query theme={null}
SELECT toDate(20297)
```
```response title=Response theme={null}
2025-07-28
```
toDate32
Introduced in: v21.9.0
Converts the argument to the [Date32](/reference/data-types/date32) data type.
If the value is outside the range, `toDate32` returns the border values supported by [Date32](/reference/data-types/date32).
If the argument is of type [`Date`](/reference/data-types/date), it's bounds are taken into account.
**Syntax**
```sql theme={null}
toDate32(expr)
```
**Arguments**
* `expr` — The value to convert. [`String`](/reference/data-types/string) or [`UInt32`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date)
**Returned value**
Returns a calendar date. [`Date32`](/reference/data-types/date32)
**Examples**
**Within range**
```sql title=Query theme={null}
SELECT toDate32('2025-01-01') AS value, toTypeName(value)
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
value: 2025-01-01
toTypeName(value): Date32
```
**Outside range**
```sql title=Query theme={null}
SELECT toDate32('1899-01-01') AS value, toTypeName(value)
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
value: 1900-01-01
toTypeName(value): Date32
```
toDate32OrDefault
Introduced in: v21.11.0
Converts the argument to the [Date32](/reference/data-types/date32) data type. If the value is outside the range, `toDate32OrDefault` returns the lower border value supported by [Date32](/reference/data-types/date32). If the argument has [Date](/reference/data-types/date) type, it's borders are taken into account. Returns default value if an invalid argument is received.
**Syntax**
```sql theme={null}
toDate32OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Date32`](/reference/data-types/date32)
**Returned value**
Value of type Date32 if successful, otherwise returns the default value if passed or 1900-01-01 if not. [`Date32`](/reference/data-types/date32)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))
```
```response title=Response theme={null}
1930-01-01
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))
```
```response title=Response theme={null}
2020-01-01
```
toDate32OrNull
Introduced in: v21.9.0
Converts an input value to a value of type Date32 but returns `NULL` if an invalid argument is received.
The same as [`toDate32`](#toDate32) but returns `NULL` if an invalid argument is received.
**Syntax**
```sql theme={null}
toDate32OrNull(x)
```
**Arguments**
* `x` — A string representation of a date. [`String`](/reference/data-types/string)
**Returned value**
Returns a Date32 value if successful, otherwise `NULL`. [`Date32`](/reference/data-types/date32) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDate32OrNull('2025-01-01'), toDate32OrNull('invalid')
```
```response title=Response theme={null}
┌─toDate32OrNull('2025-01-01')─┬─toDate32OrNull('invalid')─┐
│ 2025-01-01 │ ᴺᵁᴸᴸ │
└──────────────────────────────┴───────────────────────────┘
```
toDate32OrZero
Introduced in: v21.9.0
Converts an input value to a value of type [Date32](/reference/data-types/date32) but returns the lower boundary of [Date32](/reference/data-types/date32) if an invalid argument is received.
The same as [toDate32](#toDate32) but returns lower boundary of [Date32](/reference/data-types/date32) if an invalid argument is received.
See also:
* [`toDate32`](#toDate32)
* [`toDate32OrNull`](#toDate32OrNull)
* [`toDate32OrDefault`](#toDate32OrDefault)
**Syntax**
```sql theme={null}
toDate32OrZero(x)
```
**Arguments**
* `x` — A string representation of a date. [`String`](/reference/data-types/string)
**Returned value**
Returns a Date32 value if successful, otherwise the lower boundary of Date32 (`1900-01-01`). [`Date32`](/reference/data-types/date32)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDate32OrZero('2025-01-01'), toDate32OrZero('')
```
```response title=Response theme={null}
┌─toDate32OrZero('2025-01-01')─┬─toDate32OrZero('')─┐
│ 2025-01-01 │ 1900-01-01 │
└──────────────────────────────┴────────────────────┘
```
toDateOrDefault
Introduced in: v21.11.0
Like [toDate](#toDate) but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of [Date](/reference/data-types/date).
**Syntax**
```sql theme={null}
toDateOrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Date`](/reference/data-types/date)
**Returned value**
Value of type Date if successful, otherwise returns the default value if passed or 1970-01-01 if not. [`Date`](/reference/data-types/date)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDateOrDefault('2022-12-30')
```
```response title=Response theme={null}
2022-12-30
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDateOrDefault('', CAST('2023-01-01', 'Date'))
```
```response title=Response theme={null}
2023-01-01
```
toDateOrNull
Introduced in: v1.1.0
Converts an input value to a value of type `Date` but returns `NULL` if an invalid argument is received.
The same as [`toDate`](#toDate) but returns `NULL` if an invalid argument is received.
**Syntax**
```sql theme={null}
toDateOrNull(x)
```
**Arguments**
* `x` — A string representation of a date. [`String`](/reference/data-types/string)
**Returned value**
Returns a Date value if successful, otherwise `NULL`. [`Date`](/reference/data-types/date) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateOrNull('2025-12-30'), toDateOrNull('invalid')
```
```response title=Response theme={null}
┌─toDateOrNull('2025-12-30')─┬─toDateOrNull('invalid')─┐
│ 2025-12-30 │ ᴺᵁᴸᴸ │
└────────────────────────────┴────────────────────────┘
```
toDateOrZero
Introduced in: v1.1.0
Converts an input value to a value of type [`Date`](/reference/data-types/date) but returns the lower boundary of [`Date`](/reference/data-types/date) if an invalid argument is received.
The same as [toDate](#toDate) but returns lower boundary of [`Date`](/reference/data-types/date) if an invalid argument is received.
See also:
* [`toDate`](#toDate)
* [`toDateOrNull`](#toDateOrNull)
* [`toDateOrDefault`](#toDateOrDefault)
**Syntax**
```sql theme={null}
toDateOrZero(x)
```
**Arguments**
* `x` — A string representation of a date. [`String`](/reference/data-types/string)
**Returned value**
Returns a Date value if successful, otherwise the lower boundary of Date (`1970-01-01`). [`Date`](/reference/data-types/date)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateOrZero('2025-12-30'), toDateOrZero('')
```
```response title=Response theme={null}
┌─toDateOrZero('2025-12-30')─┬─toDateOrZero('')─┐
│ 2025-12-30 │ 1970-01-01 │
└────────────────────────────┴──────────────────┘
```
toDateTime
Introduced in: v1.1.0
Converts an input value to type [DateTime](/reference/data-types/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](/reference/data-types/string), it may be interpreted as a Unix timestamp or as a string representation of date / date with time.
Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string `'1999'` may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.
**Syntax**
```sql theme={null}
toDateTime(expr[, time_zone])
```
**Arguments**
* `expr` — The value. [`String`](/reference/data-types/string) or [`Int`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
* `time_zone` — Time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns a date time. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTime('2025-01-01 00:00:00'), toDateTime(1735689600, 'UTC')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toDateTime('2025-01-01 00:00:00'): 2025-01-01 00:00:00
toDateTime(1735689600, 'UTC'): 2025-01-01 00:00:00
```
toDateTime32
Introduced in: v20.9.0
Converts an input value to type `DateTime`.
Supports conversion from `String`, `FixedString`, `Date`, `Date32`, `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**
```sql theme={null}
toDateTime32(x[, timezone])
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`UInt*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned `DateTime` value. [`String`](/reference/data-types/string)
**Returned value**
Returns the converted input value. [`DateTime`](/reference/data-types/datetime)
**Examples**
**The value is within the range**
```sql title=Query theme={null}
SELECT toDateTime64('2025-01-01 00:00:00.000', 3) AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64('20255-01-01 00:00:00.000', 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────────────────┘
```
**As a decimal with precision**
```sql title=Query theme={null}
SELECT toDateTime64(1735689600.000, 3) AS value, toTypeName(value);
-- without the decimal point the value is still treated as Unix Timestamp in seconds
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64(1735689600.000, 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴──────────────────────────────────────────┘
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────┘
```
**With a timezone**
```sql title=Query theme={null}
SELECT toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘
```
toDateTime64
Introduced in: v20.1.0
Converts an input value to a value of type [`DateTime64`](/reference/data-types/datetime64).
**Syntax**
```sql theme={null}
toDateTime64(expr, scale[, timezone])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `scale` — Tick size (precision): 10^(-scale) seconds. [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. Time zone for the specified `DateTime64` object. [`String`](/reference/data-types/string)
**Returned value**
Returns a calendar date and time of day, with sub-second precision. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**The value is within the range**
```sql title=Query theme={null}
SELECT toDateTime64('2025-01-01 00:00:00.000', 3) AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00.000', 3))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────────────────┘
```
**As decimal with precision**
```sql title=Query theme={null}
SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);
-- Without the decimal point the value is still treated as Unix Timestamp in seconds
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────┘
```
**With timezone**
```sql title=Query theme={null}
SELECT toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value);
```
```response title=Response theme={null}
┌───────────────────value─┬─toTypeName(toDateTime64('2025-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2025-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘
```
toDateTime64OrDefault
Introduced in: v21.11.0
Like [toDateTime64](#toDateTime64), this function converts an input value to a value of type [DateTime64](/reference/data-types/datetime64),
but returns either the default value of [DateTime64](/reference/data-types/datetime64)
or the provided default if an invalid argument is received.
**Syntax**
```sql theme={null}
toDateTime64OrDefault(expr, scale[, timezone, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `scale` — Tick size (precision): 10^-precision seconds. [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. Time zone. [`String`](/reference/data-types/string)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Value of type DateTime64 if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00.000 if not. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDateTime64OrDefault('1976-10-18 00:00:00.30', 3)
```
```response title=Response theme={null}
1976-10-18 00:00:00.300
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDateTime64OrDefault('1976-10-18 00:00:00 30', 3, 'UTC', toDateTime64('2001-01-01 00:00:00.00',3))
```
```response title=Response theme={null}
2000-12-31 23:00:00.000
```
toDateTime64OrNull
Introduced in: v20.1.0
Converts an input value to a value of type `DateTime64` but returns `NULL` if an invalid argument is received.
The same as `toDateTime64` but returns `NULL` if an invalid argument is received.
**Syntax**
```sql theme={null}
toDateTime64OrNull(x)
```
**Arguments**
* `x` — A string representation of a date with time and subsecond precision. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime64 value if successful, otherwise `NULL`. [`DateTime64`](/reference/data-types/datetime64) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTime64OrNull('2025-12-30 13:44:17.123'), toDateTime64OrNull('invalid')
```
```response title=Response theme={null}
┌─toDateTime64OrNull('2025-12-30 13:44:17.123')─┬─toDateTime64OrNull('invalid')─┐
│ 2025-12-30 13:44:17.123 │ ᴺᵁᴸᴸ │
└─────────────────────────────────────────────────┴───────────────────────────────┘
```
toDateTime64OrZero
Introduced in: v20.1.0
Converts an input value to a value of type [DateTime64](/reference/data-types/datetime64) but returns the lower boundary of [DateTime64](/reference/data-types/datetime64) if an invalid argument is received.
The same as [toDateTime64](#toDateTime64) but returns lower boundary of [DateTime64](/reference/data-types/datetime64) if an invalid argument is received.
See also:
* [toDateTime64](#toDateTime64).
* [toDateTime64OrNull](#toDateTime64OrNull).
* [toDateTime64OrDefault](#toDateTime64OrDefault).
**Syntax**
```sql theme={null}
toDateTime64OrZero(x)
```
**Arguments**
* `x` — A string representation of a date with time and subsecond precision. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime64 value if successful, otherwise the lower boundary of DateTime64 (`1970-01-01 00:00:00.000`). [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTime64OrZero('2025-12-30 13:44:17.123'), toDateTime64OrZero('invalid')
```
```response title=Response theme={null}
┌─toDateTime64OrZero('2025-12-30 13:44:17.123')─┬─toDateTime64OrZero('invalid')─┐
│ 2025-12-30 13:44:17.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────────────────────────────┴─────────────────────────────────────┘
```
toDateTimeOrDefault
Introduced in: v21.11.0
Like [toDateTime](#toDateTime) but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of [DateTime](/reference/data-types/datetime).
**Syntax**
```sql theme={null}
toDateTimeOrDefault(expr[, timezone, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `timezone` — Optional. Time zone. [`String`](/reference/data-types/string)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`DateTime`](/reference/data-types/datetime)
**Returned value**
Value of type DateTime if successful, otherwise returns the default value if passed or 1970-01-01 00:00:00 if not. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDateTimeOrDefault('2022-12-30 13:44:17')
```
```response title=Response theme={null}
2022-12-30 13:44:17
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))
```
```response title=Response theme={null}
2023-01-01 00:00:00
```
toDateTimeOrNull
Introduced in: v1.1.0
Converts an input value to a value of type `DateTime` but returns `NULL` if an invalid argument is received.
The same as [`toDateTime`](#toDateTime) but returns `NULL` if an invalid argument is received.
**Syntax**
```sql theme={null}
toDateTimeOrNull(x)
```
**Arguments**
* `x` — A string representation of a date with time. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime` value if successful, otherwise `NULL`. [`DateTime`](/reference/data-types/datetime) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTimeOrNull('2025-12-30 13:44:17'), toDateTimeOrNull('invalid')
```
```response title=Response theme={null}
┌─toDateTimeOrNull('2025-12-30 13:44:17')─┬─toDateTimeOrNull('invalid')─┐
│ 2025-12-30 13:44:17 │ ᴺᵁᴸᴸ │
└─────────────────────────────────────────┴─────────────────────────────┘
```
toDateTimeOrZero
Introduced in: v1.1.0
Converts an input value to a value of type [DateTime](/reference/data-types/datetime) but returns the lower boundary of [DateTime](/reference/data-types/datetime) if an invalid argument is received.
The same as [toDateTime](#toDateTime) but returns lower boundary of [DateTime](/reference/data-types/datetime) if an invalid argument is received.
**Syntax**
```sql theme={null}
toDateTimeOrZero(x)
```
**Arguments**
* `x` — A string representation of a date with time. [`String`](/reference/data-types/string)
**Returned value**
Returns a DateTime value if successful, otherwise the lower boundary of DateTime (`1970-01-01 00:00:00`). [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTimeOrZero('2025-12-30 13:44:17'), toDateTimeOrZero('invalid')
```
```response title=Response theme={null}
┌─toDateTimeOrZero('2025-12-30 13:44:17')─┬─toDateTimeOrZero('invalid')─┐
│ 2025-12-30 13:44:17 │ 1970-01-01 00:00:00 │
└─────────────────────────────────────────┴─────────────────────────────┘
```
toDecimal128
Introduced in: v18.12.0
Converts an input value to a value of type [`Decimal(38, S)`](/reference/data-types/decimal) with scale of `S`.
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments:
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values, e.g. `SELECT toDecimal128('0xc0fe', 1);`.
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.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal128(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal128('1.15', 2) = 1.15`
**Syntax**
```sql theme={null}
toDecimal128(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type `Decimal(38, S)` [`Decimal128(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toDecimal128(99, 1) AS a, toTypeName(a) AS type_a,
toDecimal128(99.67, 2) AS b, toTypeName(b) AS type_b,
toDecimal128('99.67', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
a: 99
type_a: Decimal(38, 1)
b: 99.67
type_b: Decimal(38, 2)
c: 99.67
type_c: Decimal(38, 3)
```
toDecimal128OrDefault
Introduced in: v21.11.0
Like [`toDecimal128`](#toDecimal128), this function converts an input value to a value of type [Decimal(38, S)](/reference/data-types/decimal) but returns the default value in case of an error.
**Syntax**
```sql theme={null}
toDecimal128OrDefault(expr, S[, default])
```
**Arguments**
* `expr` — A String representation of a number. [`String`](/reference/data-types/string)
* `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
* `default` — Optional. The default value to return if parsing to type Decimal128(S) is unsuccessful. [`Decimal128(S)`](/reference/data-types/decimal)
**Returned value**
Value of type Decimal(38, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal128(S)`](/reference/data-types/decimal)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDecimal128OrDefault(toString(1/42), 18)
```
```response title=Response theme={null}
0.023809523809523808
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)'))
```
```response title=Response theme={null}
-1
```
toDecimal128OrNull
Introduced in: v20.1.0
Converts an input value to a value of type [`Decimal(38, S)`](/reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal128`](#toDecimal128) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `NULL`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
* Values that exceed the bounds of `Decimal128`:`(-1*10^(38 - S), 1*10^(38 - S))`.
See also:
* [`toDecimal128`](#toDecimal128).
* [`toDecimal128OrZero`](#toDecimal128OrZero).
* [`toDecimal128OrDefault`](#toDecimal128OrDefault).
**Syntax**
```sql theme={null}
toDecimal128OrNull(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(38, S) value if successful, otherwise `NULL`. [`Decimal128(S)`](/reference/data-types/decimal) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal128OrNull('42.7', 2), toDecimal128OrNull('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal128OrNull('42.7', 2)─┬─toDecimal128OrNull('invalid', 2)─┐
│ 42.70 │ ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘
```
toDecimal128OrZero
Introduced in: v20.1.0
Converts an input value to a value of type [Decimal(38, S)](/reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal128`](#toDecimal128) but returns `0` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `0`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
If the input value exceeds the bounds of `Decimal128`:`(-1*10^(38 - S), 1*10^(38 - S))`, the function returns `0`.
**Syntax**
```sql theme={null}
toDecimal128OrZero(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(38, S) value if successful, otherwise `0`. [`Decimal128(S)`](/reference/data-types/decimal)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT toDecimal128OrZero('42.7', 2), toDecimal128OrZero('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal128OrZero('42.7', 2)─┬─toDecimal128OrZero('invalid', 2)─┐
│ 42.70 │ 0.00 │
└───────────────────────────────┴──────────────────────────────────┘
```
toDecimal256
Introduced in: v20.8.0
Converts an input value to a value of type [`Decimal(76, S)`](/reference/data-types/decimal) with scale of `S`. Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments:
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values, e.g. `SELECT toDecimal256('0xc0fe', 1);`.
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.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal256(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal256('1.15', 2) = 1.15`
**Syntax**
```sql theme={null}
toDecimal256(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type `Decimal(76, S)`. [`Decimal256(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toDecimal256(99, 1) AS a, toTypeName(a) AS type_a,
toDecimal256(99.67, 2) AS b, toTypeName(b) AS type_b,
toDecimal256('99.67', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
a: 99
type_a: Decimal(76, 1)
b: 99.67
type_b: Decimal(76, 2)
c: 99.67
type_c: Decimal(76, 3)
```
toDecimal256OrDefault
Introduced in: v21.11.0
Like [`toDecimal256`](#toDecimal256), this function converts an input value to a value of type [Decimal(76, S)](/reference/data-types/decimal) but returns the default value in case of an error.
**Syntax**
```sql theme={null}
toDecimal256OrDefault(expr, S[, default])
```
**Arguments**
* `expr` — A String representation of a number. [`String`](/reference/data-types/string)
* `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
* `default` — Optional. The default value to return if parsing to type Decimal256(S) is unsuccessful. [`Decimal256(S)`](/reference/data-types/decimal)
**Returned value**
Value of type Decimal(76, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal256(S)`](/reference/data-types/decimal)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDecimal256OrDefault(toString(1/42), 76)
```
```response title=Response theme={null}
0.023809523809523808
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)'))
```
```response title=Response theme={null}
-1
```
toDecimal256OrNull
Introduced in: v20.8.0
Converts an input value to a value of type [`Decimal(76, S)`](/reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal256`](#toDecimal256) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `NULL`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
* Values that exceed the bounds of `Decimal256`: `(-1 * 10^(76 - S), 1 * 10^(76 - S))`.
See also:
* [`toDecimal256`](#toDecimal256).
* [`toDecimal256OrZero`](#toDecimal256OrZero).
* [`toDecimal256OrDefault`](#toDecimal256OrDefault).
**Syntax**
```sql theme={null}
toDecimal256OrNull(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(76, S) value if successful, otherwise `NULL`. [`Decimal256(S)`](/reference/data-types/decimal) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal256OrNull('42.7', 2), toDecimal256OrNull('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal256OrNull('42.7', 2)─┬─toDecimal256OrNull('invalid', 2)─┐
│ 42.70 │ ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘
```
toDecimal256OrZero
Introduced in: v20.8.0
Converts an input value to a value of type [Decimal(76, S)](/reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal256`](#toDecimal256) but returns `0` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `0`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
If the input value exceeds the bounds of `Decimal256`:`(-1*10^(76 - S), 1*10^(76 - S))`, the function returns `0`.
See also:
* [`toDecimal256`](#toDecimal256).
* [`toDecimal256OrNull`](#toDecimal256OrNull).
* [`toDecimal256OrDefault`](#toDecimal256OrDefault).
**Syntax**
```sql theme={null}
toDecimal256OrZero(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(76, S) value if successful, otherwise `0`. [`Decimal256(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal256OrZero('42.7', 2), toDecimal256OrZero('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal256OrZero('42.7', 2)─┬─toDecimal256OrZero('invalid', 2)─┐
│ 42.70 │ 0.00 │
└───────────────────────────────┴──────────────────────────────────┘
```
toDecimal32
Introduced in: v18.12.0
Converts an input value to a value of type [`Decimal(9, S)`](/reference/data-types/decimal) with scale of `S`. Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments:
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values, e.g. `SELECT toDecimal32('0xc0fe', 1);`.
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.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal32(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal32('1.15', 2) = 1.15`
**Syntax**
```sql theme={null}
toDecimal32(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type `Decimal(9, S)` [`Decimal32(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toDecimal32(2, 1) AS a, toTypeName(a) AS type_a,
toDecimal32(4.2, 2) AS b, toTypeName(b) AS type_b,
toDecimal32('4.2', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
a: 2
type_a: Decimal(9, 1)
b: 4.2
type_b: Decimal(9, 2)
c: 4.2
type_c: Decimal(9, 3)
```
toDecimal32OrDefault
Introduced in: v21.11.0
Like [`toDecimal32`](#toDecimal32), this function converts an input value to a value of type [Decimal(9, S)](/reference/data-types/decimal) but returns the default value in case of an error.
**Syntax**
```sql theme={null}
toDecimal32OrDefault(expr, S[, default])
```
**Arguments**
* `expr` — A String representation of a number. [`String`](/reference/data-types/string)
* `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
* `default` — Optional. The default value to return if parsing to type Decimal32(S) is unsuccessful. [`Decimal32(S)`](/reference/data-types/decimal)
**Returned value**
Value of type Decimal(9, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal32(S)`](/reference/data-types/decimal)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDecimal32OrDefault(toString(0.0001), 5)
```
```response title=Response theme={null}
0.0001
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)'))
```
```response title=Response theme={null}
-1
```
toDecimal32OrNull
Introduced in: v20.1.0
Converts an input value to a value of type [`Decimal(9, S)`](/reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal32`](#toDecimal32) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `NULL`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
* Values that exceed the bounds of `Decimal32`:`(-1*10^(9 - S), 1*10^(9 - S))`.
See also:
* [`toDecimal32`](#toDecimal32).
* [`toDecimal32OrZero`](#toDecimal32OrZero).
* [`toDecimal32OrDefault`](#toDecimal32OrDefault).
**Syntax**
```sql theme={null}
toDecimal32OrNull(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(9, S) value if successful, otherwise `NULL`. [`Decimal32(S)`](/reference/data-types/decimal) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal32OrNull('42.7', 2), toDecimal32OrNull('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal32OrNull('42.7', 2)─┬─toDecimal32OrNull('invalid', 2)─┐
│ 42.70 │ ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘
```
toDecimal32OrZero
Introduced in: v20.1.0
Converts an input value to a value of type [Decimal(9, S)](/reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal32`](#toDecimal32) but returns `0` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `0`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
If the input value exceeds the bounds of `Decimal32`:`(-1*10^(9 - S), 1*10^(9 - S))`, the function returns `0`.
**Syntax**
```sql theme={null}
toDecimal32OrZero(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(9, S) value if successful, otherwise `0`. [`Decimal32(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal32OrZero('42.7', 2), toDecimal32OrZero('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal32OrZero('42.7', 2)─┬─toDecimal32OrZero('invalid', 2)─┐
│ 42.70 │ 0.00 │
└──────────────────────────────┴─────────────────────────────────┘
```
toDecimal64
Introduced in: v18.12.0
Converts an input value to a value of type [`Decimal(18, S)`](/reference/data-types/decimal) with scale of `S`.
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments:
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values, e.g. `SELECT toDecimal64('0xc0fe', 1);`.
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.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal64(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal64('1.15', 2) = 1.15`
**Syntax**
```sql theme={null}
toDecimal64(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a decimal value. [`Decimal(18, S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toDecimal64(2, 1) AS a, toTypeName(a) AS type_a,
toDecimal64(4.2, 2) AS b, toTypeName(b) AS type_b,
toDecimal64('4.2', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
a: 2.0
type_a: Decimal(18, 1)
b: 4.20
type_b: Decimal(18, 2)
c: 4.200
type_c: Decimal(18, 3)
```
toDecimal64OrDefault
Introduced in: v21.11.0
Like [`toDecimal64`](#toDecimal64), this function converts an input value to a value of type [Decimal(18, S)](/reference/data-types/decimal) but returns the default value in case of an error.
**Syntax**
```sql theme={null}
toDecimal64OrDefault(expr, S[, default])
```
**Arguments**
* `expr` — A String representation of a number. [`String`](/reference/data-types/string)
* `S` — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
* `default` — Optional. The default value to return if parsing to type Decimal64(S) is unsuccessful. [`Decimal64(S)`](/reference/data-types/decimal)
**Returned value**
Value of type Decimal(18, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal64(S)`](/reference/data-types/decimal)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toDecimal64OrDefault(toString(0.0001), 18)
```
```response title=Response theme={null}
0.0001
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)'))
```
```response title=Response theme={null}
-1
```
toDecimal64OrNull
Introduced in: v20.1.0
Converts an input value to a value of type [Decimal(18, S)](/reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal64`](#toDecimal64) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `NULL`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
* Values that exceed the bounds of `Decimal64`:`(-1*10^(18 - S), 1*10^(18 - S))`.
See also:
* [`toDecimal64`](#toDecimal64).
* [`toDecimal64OrZero`](#toDecimal64OrZero).
* [`toDecimal64OrDefault`](#toDecimal64OrDefault).
**Syntax**
```sql theme={null}
toDecimal64OrNull(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(18, S) value if successful, otherwise `NULL`. [`Decimal64(S)`](/reference/data-types/decimal) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal64OrNull('42.7', 2), toDecimal64OrNull('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal64OrNull('42.7', 2)─┬─toDecimal64OrNull('invalid', 2)─┐
│ 42.70 │ ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────────┘
```
toDecimal64OrZero
Introduced in: v20.1.0
Converts an input value to a value of type [Decimal(18, S)](/reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal64`](#toDecimal64) but returns `0` instead of throwing an exception on conversion errors.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values or string representations of type Float\*.
Unsupported arguments (return `0`):
* Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
* String representations of binary and hexadecimal values.
If the input value exceeds the bounds of `Decimal64`:`(-1*10^(18 - S), 1*10^(18 - S))`, the function returns `0`.
See also:
* [`toDecimal64`](#toDecimal64).
* [`toDecimal64OrNull`](#toDecimal64OrNull).
* [`toDecimal64OrDefault`](#toDecimal64OrDefault).
**Syntax**
```sql theme={null}
toDecimal64OrZero(expr, S)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
* `S` — Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a Decimal(18, S) value if successful, otherwise `0`. [`Decimal64(S)`](/reference/data-types/decimal)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDecimal64OrZero('42.7', 2), toDecimal64OrZero('invalid', 2)
```
```response title=Response theme={null}
┌─toDecimal64OrZero('42.7', 2)─┬─toDecimal64OrZero('invalid', 2)─┐
│ 42.70 │ 0.00 │
└──────────────────────────────┴─────────────────────────────────┘
```
toDecimalString
Introduced in: v23.3.0
Converts a numeric value to a String with specified number of fractional digits.
The function rounds the input value to the specified number of decimal places. If the input value has fewer fractional
digits than requested, the result is padded with zeros to achieve the exact number of fractional digits specified.
**Syntax**
```sql theme={null}
toDecimalString(number, scale)
```
**Arguments**
* `number` — The numeric value to convert to a string. Can be any numeric type (Int, UInt, Float, Decimal). [`Int8`](/reference/data-types/int-uint) or [`Int16`](/reference/data-types/int-uint) or [`Int32`](/reference/data-types/int-uint) or [`Int64`](/reference/data-types/int-uint) or [`UInt8`](/reference/data-types/int-uint) or [`UInt16`](/reference/data-types/int-uint) or [`UInt32`](/reference/data-types/int-uint) or [`UInt64`](/reference/data-types/int-uint) or [`Float32`](/reference/data-types/float) or [`Float64`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `scale` — The number of digits to display in the fractional part. The result will be rounded if necessary. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a String representation of the number with exactly the specified number of fractional digits. [`String`](/reference/data-types/string)
**Examples**
**Round and format a number**
```sql title=Query theme={null}
SELECT toDecimalString(2.1456, 2)
```
```response title=Response theme={null}
┌─toDecimalString(2.1456, 2)─┐
│ 2.15 │
└────────────────────────────┘
```
**Pad with zeros**
```sql title=Query theme={null}
SELECT toDecimalString(5, 3)
```
```response title=Response theme={null}
┌─toDecimalString(5, 3)─┐
│ 5.000 │
└───────────────────────┘
```
**Different numeric types**
```sql title=Query theme={null}
SELECT toDecimalString(CAST(123.456 AS Decimal(10,3)), 2) AS decimal_val,
toDecimalString(CAST(42.7 AS Float32), 4) AS float_val
```
```response title=Response theme={null}
┌─decimal_val─┬─float_val─┐
│ 123.46 │ 42.7000 │
└─────────────┴───────────┘
```
toFixedString
Introduced in: v1.1.0
Converts a [`String`](/reference/data-types/string) argument to a [`FixedString(N)`](/reference/data-types/fixedstring) 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**
```sql theme={null}
toFixedString(s, N)
```
**Arguments**
* `s` — String to convert. [`String`](/reference/data-types/string)
* `N` — Length of the resulting FixedString. [`const UInt*`](/reference/data-types/int-uint)
**Returned value**
Returns a FixedString of length N. [`FixedString(N)`](/reference/data-types/fixedstring)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toFixedString('foo', 8) AS s;
```
```response title=Response theme={null}
┌─s─────────────┐
│ foo\0\0\0\0\0 │
└───────────────┘
```
toFloat32
Introduced in: v1.1.0
Converts an input value to a value of type [Float32](/reference/data-types/float).
Throws an exception in case of an error.
Supported arguments:
* Values of type (U)Int\*.
* String representations of (U)Int8/16/32/128/256.
* Values of type Float\*, including `NaN` and `Inf`.
* String representations of Float\*, including `NaN` and `Inf` (case-insensitive).
Unsupported arguments:
* String representations of binary and hexadecimal values, e.g. `SELECT toFloat32('0xc0fe');`.
See also:
* [`toFloat32OrZero`](#toFloat32OrZero).
* [`toFloat32OrNull`](#toFloat32OrNull).
* [`toFloat32OrDefault`](#toFloat32OrDefault).
**Syntax**
```sql theme={null}
toFloat32(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 32-bit floating point value. [`Float32`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat32(42.7),
toFloat32('42.7'),
toFloat32('NaN')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat32(42.7): 42.7
toFloat32('42.7'): 42.7
toFloat32('NaN'): nan
```
toFloat32OrDefault
Introduced in: v21.11.0
Like [`toFloat32`](#toFloat32), this function converts an input value to a value of type [Float32](/reference/data-types/float) 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**
```sql theme={null}
toFloat32OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Float32`](/reference/data-types/float)
**Returned value**
Returns a value of type Float32 if successful, otherwise returns the default value if passed or 0 if not. [`Float32`](/reference/data-types/float)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toFloat32OrDefault('8', CAST('0', 'Float32'))
```
```response title=Response theme={null}
8
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toFloat32OrDefault('abc', CAST('0', 'Float32'))
```
```response title=Response theme={null}
0
```
toFloat32OrNull
Introduced in: v1.1.0
Converts an input value to a value of type [Float32](/reference/data-types/float) but returns `NULL` in case of an error.
Like [`toFloat32`](#toFloat32) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values of type (U)Int\*.
* String representations of (U)Int8/16/32/128/256.
* Values of type Float\*, including `NaN` and `Inf`.
* String representations of Float\*, including `NaN` and `Inf` (case-insensitive).
Unsupported arguments (return `NULL`):
* String representations of binary and hexadecimal values, e.g. `SELECT toFloat32OrNull('0xc0fe');`.
* Invalid string formats.
See also:
* [`toFloat32`](#toFloat32).
* [`toFloat32OrZero`](#toFloat32OrZero).
* [`toFloat32OrDefault`](#toFloat32OrDefault).
**Syntax**
```sql theme={null}
toFloat32OrNull(x)
```
**Arguments**
* `x` — A string representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a 32-bit Float value if successful, otherwise `NULL`. [`Float32`](/reference/data-types/float) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat32OrNull('42.7'),
toFloat32OrNull('NaN'),
toFloat32OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat32OrNull('42.7'): 42.7
toFloat32OrNull('NaN'): nan
toFloat32OrNull('abc'): \N
```
toFloat32OrZero
Introduced in: v1.1.0
Converts an input value to a value of type [Float32](/reference/data-types/float) but returns `0` in case of an error.
Like [`toFloat32`](#toFloat32) but returns `0` instead of throwing an exception on conversion errors.
See also:
* [`toFloat32`](#toFloat32).
* [`toFloat32OrNull`](#toFloat32OrNull).
* [`toFloat32OrDefault`](#toFloat32OrDefault).
**Syntax**
```sql theme={null}
toFloat32OrZero(x)
```
**Arguments**
* `x` — A string representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a 32-bit Float value if successful, otherwise `0`. [`Float32`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat32OrZero('42.7'),
toFloat32OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat32OrZero('42.7'): 42.7
toFloat32OrZero('abc'): 0
```
toFloat64
Introduced in: v1.1.0
Converts an input value to a value of type [`Float64`](/reference/data-types/float).
Throws an exception in case of an error.
Supported arguments:
* Values of type (U)Int\*.
* String representations of (U)Int8/16/32/128/256.
* Values of type Float\*, including `NaN` and `Inf`.
* String representations of type Float\*, including `NaN` and `Inf` (case-insensitive).
Unsupported arguments:
* String representations of binary and hexadecimal values, e.g. `SELECT toFloat64('0xc0fe');`.
See also:
* [`toFloat64OrZero`](#toFloat64OrZero).
* [`toFloat64OrNull`](#toFloat64OrNull).
* [`toFloat64OrDefault`](#toFloat64OrDefault).
**Syntax**
```sql theme={null}
toFloat64(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 64-bit floating point value. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat64(42.7),
toFloat64('42.7'),
toFloat64('NaN')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat64(42.7): 42.7
toFloat64('42.7'): 42.7
toFloat64('NaN'): nan
```
toFloat64OrDefault
Introduced in: v21.11.0
Like [`toFloat64`](#toFloat64), this function converts an input value to a value of type [Float64](/reference/data-types/float) 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**
```sql theme={null}
toFloat64OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Float64`](/reference/data-types/float)
**Returned value**
Returns a value of type Float64 if successful, otherwise returns the default value if passed or 0 if not. [`Float64`](/reference/data-types/float)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toFloat64OrDefault('8', CAST('0', 'Float64'))
```
```response title=Response theme={null}
8
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toFloat64OrDefault('abc', CAST('0', 'Float64'))
```
```response title=Response theme={null}
0
```
toFloat64OrNull
Introduced in: v1.1.0
Converts an input value to a value of type [Float64](/reference/data-types/float) but returns `NULL` in case of an error.
Like [`toFloat64`](#toFloat64) but returns `NULL` instead of throwing an exception on conversion errors.
Supported arguments:
* Values of type (U)Int\*.
* String representations of (U)Int8/16/32/128/256.
* Values of type Float\*, including `NaN` and `Inf`.
* String representations of type Float\*, including `NaN` and `Inf` (case-insensitive).
Unsupported arguments (return `NULL`):
* String representations of binary and hexadecimal values, e.g. `SELECT toFloat64OrNull('0xc0fe');`.
* Invalid string formats.
See also:
* [`toFloat64`](#toFloat64).
* [`toFloat64OrZero`](#toFloat64OrZero).
* [`toFloat64OrDefault`](#toFloat64OrDefault).
**Syntax**
```sql theme={null}
toFloat64OrNull(x)
```
**Arguments**
* `x` — A string representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a 64-bit Float value if successful, otherwise `NULL`. [`Float64`](/reference/data-types/float) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat64OrNull('42.7'),
toFloat64OrNull('NaN'),
toFloat64OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat64OrNull('42.7'): 42.7
toFloat64OrNull('NaN'): nan
toFloat64OrNull('abc'): \N
```
toFloat64OrZero
Introduced in: v1.1.0
Converts an input value to a value of type [Float64](/reference/data-types/float) but returns `0` in case of an error.
Like [`toFloat64`](#toFloat64) but returns `0` instead of throwing an exception on conversion errors.
See also:
* [`toFloat64`](#toFloat64).
* [`toFloat64OrNull`](#toFloat64OrNull).
* [`toFloat64OrDefault`](#toFloat64OrDefault).
**Syntax**
```sql theme={null}
toFloat64OrZero(x)
```
**Arguments**
* `x` — A string representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a 64-bit Float value if successful, otherwise `0`. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toFloat64OrZero('42.7'),
toFloat64OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toFloat64OrZero('42.7'): 42.7
toFloat64OrZero('abc'): 0
```
toInt128
Introduced in: v1.1.0
Converts an input value to a value of type [Int128](/reference/data-types/int-uint).
Throws an exception in case of an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt128('0xc0fe');`.
If the input value cannot be represented within the bounds of Int128, the result over or under flows.
This is not considered an error.
See also:
* [`toInt128OrZero`](#toInt128OrZero).
* [`toInt128OrNull`](#toInt128OrNull).
* [`toInt128OrDefault`](#toInt128OrDefault).
**Syntax**
```sql theme={null}
toInt128(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 128-bit integer value. [`Int128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt128(-128),
toInt128(-128.8),
toInt128('-128')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt128(-128): -128
toInt128(-128.8): -128
toInt128('-128'): -128
```
toInt128OrDefault
Introduced in: v21.11.0
Like [`toInt128`](#toInt128), this function converts an input value to a value of type [Int128](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt128OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int128`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int128 if successful, otherwise returns the default value if passed, or 0 if not. [`Int128`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt128OrDefault('-128', CAST('-1', 'Int128'))
```
```response title=Response theme={null}
-128
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt128OrDefault('abc', CAST('-1', 'Int128'))
```
```response title=Response theme={null}
-1
```
toInt128OrNull
Introduced in: v20.8.0
Like [`toInt128`](#toInt128), this function converts an input value to a value of type [Int128](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt128OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int128](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt128`](#toInt128).
* [`toInt128OrZero`](#toInt128OrZero).
* [`toInt128OrDefault`](#toInt128OrDefault).
**Syntax**
```sql theme={null}
toInt128OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int128, otherwise `NULL` if the conversion is unsuccessful. [`Int128`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt128OrNull('-128'),
toInt128OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt128OrNull('-128'): -128
toInt128OrNull('abc'): \N
```
toInt128OrZero
Introduced in: v20.8.0
Converts an input value to type [Int128](/reference/data-types/int-uint) but returns `0` in case of an error.
Like [`toInt128`](#toInt128) but returns `0` instead of throwing an exception.
See also:
* [`toInt128`](#toInt128).
* [`toInt128OrNull`](#toInt128OrNull).
* [`toInt128OrDefault`](#toInt128OrDefault).
**Syntax**
```sql theme={null}
toInt128OrZero(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal) or [`(U)Int*`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns the converted input value, otherwise `0` if conversion fails. [`Int128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toInt128OrZero('123')
```
```response title=Response theme={null}
123
```
**Failed conversion returns zero**
```sql title=Query theme={null}
SELECT toInt128OrZero('abc')
```
```response title=Response theme={null}
0
```
toInt16
Introduced in: v1.1.0
Converts an input value to a value of type [`Int16`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt16('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int16](/reference/data-types/int-uint), 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](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toInt16OrZero`](#toInt16OrZero).
* [`toInt16OrNull`](#toInt16OrNull).
* [`toInt16OrDefault`](#toInt16OrDefault).
**Syntax**
```sql theme={null}
toInt16(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 16-bit integer value. [`Int16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt16(-16),
toInt16(-16.16),
toInt16('-16')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt16(-16): -16
toInt16(-16.16): -16
toInt16('-16'): -16
```
toInt16OrDefault
Introduced in: v21.11.0
Like [`toInt16`](#toInt16), this function converts an input value to a value of type [Int16](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt16OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int16`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int16 if successful, otherwise returns the default value if passed, or 0 if not. [`Int16`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt16OrDefault('-16', CAST('-1', 'Int16'))
```
```response title=Response theme={null}
-16
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt16OrDefault('abc', CAST('-1', 'Int16'))
```
```response title=Response theme={null}
-1
```
toInt16OrNull
Introduced in: v1.1.0
Like [`toInt16`](#toInt16), this function converts an input value to a value of type [Int16](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt16OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int16](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt16`](#toInt16).
* [`toInt16OrZero`](#toInt16OrZero).
* [`toInt16OrDefault`](#toInt16OrDefault).
**Syntax**
```sql theme={null}
toInt16OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type `Int16`, otherwise `NULL` if the conversion is unsuccessful. [`Int16`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt16OrNull('-16'),
toInt16OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt16OrNull('-16'): -16
toInt16OrNull('abc'): \N
```
toInt16OrZero
Introduced in: v1.1.0
Like [`toInt16`](#toInt16), this function converts an input value to a value of type [Int16](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt16OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int16](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt16`](#toInt16).
* [`toInt16OrNull`](#toInt16OrNull).
* [`toInt16OrDefault`](#toInt16OrDefault).
**Syntax**
```sql theme={null}
toInt16OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int16, otherwise `0` if the conversion is unsuccessful. [`Int16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt16OrZero('16'),
toInt16OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt16OrZero('16'): 16
toInt16OrZero('abc'): 0
```
toInt256
Introduced in: v1.1.0
Converts an input value to a value of type [Int256](/reference/data-types/int-uint).
Throws an exception in case of an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt256('0xc0fe');`.
If the input value cannot be represented within the bounds of Int256, the result over or under flows.
This is not considered an error.
See also:
* [`toInt256OrZero`](#toInt256OrZero).
* [`toInt256OrNull`](#toInt256OrNull).
* [`toInt256OrDefault`](#toInt256OrDefault).
**Syntax**
```sql theme={null}
toInt256(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 256-bit integer value. [`Int256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt256(-256),
toInt256(-256.256),
toInt256('-256')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt256(-256): -256
toInt256(-256.256): -256
toInt256('-256'): -256
```
toInt256OrDefault
Introduced in: v21.11.0
Like [`toInt256`](#toInt256), this function converts an input value to a value of type [Int256](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt256OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int256`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int256 if successful, otherwise returns the default value if passed, or 0 if not. [`Int256`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt256OrDefault('-256', CAST('-1', 'Int256'))
```
```response title=Response theme={null}
-256
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt256OrDefault('abc', CAST('-1', 'Int256'))
```
```response title=Response theme={null}
-1
```
toInt256OrNull
Introduced in: v20.8.0
Like [`toInt256`](#toInt256), this function converts an input value to a value of type [Int256](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt256OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int256](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt256`](#toInt256).
* [`toInt256OrZero`](#toInt256OrZero).
* [`toInt256OrDefault`](#toInt256OrDefault).
**Syntax**
```sql theme={null}
toInt256OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int256, otherwise `NULL` if the conversion is unsuccessful. [`Int256`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt256OrNull('-256'),
toInt256OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt256OrNull('-256'): -256
toInt256OrNull('abc'): \N
```
toInt256OrZero
Introduced in: v20.8.0
Converts an input value to type [Int256](/reference/data-types/int-uint) but returns `0` in case of an error.
Like [`toInt256`](#toInt256) but returns `0` instead of throwing an exception.
See also:
* [`toInt256`](#toInt256).
* [`toInt256OrNull`](#toInt256OrNull).
* [`toInt256OrDefault`](#toInt256OrDefault).
**Syntax**
```sql theme={null}
toInt256OrZero(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal) or [`(U)Int*`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns the converted input value, otherwise `0` if conversion fails. [`Int256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toInt256OrZero('123')
```
```response title=Response theme={null}
123
```
**Failed conversion returns zero**
```sql title=Query theme={null}
SELECT toInt256OrZero('abc')
```
```response title=Response theme={null}
0
```
toInt32
Introduced in: v1.1.0
Converts an input value to a value of type [`Int32`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt32('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int32](/reference/data-types/int-uint), the result over or under flows.
This is not considered an error.
For example: `SELECT toInt32(2147483648) == -2147483648;`
The function uses [rounding towards zero](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toInt32OrZero`](#toInt32OrZero).
* [`toInt32OrNull`](#toInt32OrNull).
* [`toInt32OrDefault`](#toInt32OrDefault).
**Syntax**
```sql theme={null}
toInt32(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 32-bit integer value. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt32(-32),
toInt32(-32.32),
toInt32('-32')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt32(-32): -32
toInt32(-32.32): -32
toInt32('-32'): -32
```
toInt32OrDefault
Introduced in: v21.11.0
Like [`toInt32`](#toInt32), this function converts an input value to a value of type [Int32](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt32OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int32`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int32 if successful, otherwise returns the default value if passed or 0 if not. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt32OrDefault('-32', CAST('-1', 'Int32'))
```
```response title=Response theme={null}
-32
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt32OrDefault('abc', CAST('-1', 'Int32'))
```
```response title=Response theme={null}
-1
```
toInt32OrNull
Introduced in: v1.1.0
Like [`toInt32`](#toInt32), this function converts an input value to a value of type [Int32](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt32OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int32](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt32`](#toInt32).
* [`toInt32OrZero`](#toInt32OrZero).
* [`toInt32OrDefault`](#toInt32OrDefault).
**Syntax**
```sql theme={null}
toInt32OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int32, otherwise `NULL` if the conversion is unsuccessful. [`Int32`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt32OrNull('-32'),
toInt32OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt32OrNull('-32'): -32
toInt32OrNull('abc'): \N
```
toInt32OrZero
Introduced in: v1.1.0
Like [`toInt32`](#toInt32), this function converts an input value to a value of type [Int32](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt32OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int32](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt32`](#toInt32).
* [`toInt32OrNull`](#toInt32OrNull).
* [`toInt32OrDefault`](#toInt32OrDefault).
**Syntax**
```sql theme={null}
toInt32OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int32, otherwise `0` if the conversion is unsuccessful. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt32OrZero('32'),
toInt32OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt32OrZero('32'): 32
toInt32OrZero('abc'): 0
```
toInt64
Introduced in: v1.1.0
Converts an input value to a value of type [`Int64`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt64('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int64](/reference/data-types/int-uint), the result over or under flows.
This is not considered an error.
For example: `SELECT toInt64(9223372036854775808) == -9223372036854775808;`
The function uses [rounding towards zero](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toInt64OrZero`](#toInt64OrZero).
* [`toInt64OrNull`](#toInt64OrNull).
* [`toInt64OrDefault`](#toInt64OrDefault).
**Syntax**
```sql theme={null}
toInt64(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. Supported: values or string representations of type (U)Int\*, values of type Float\*. Unsupported: string representations of Float\* values including NaN and Inf, string representations of binary and hexadecimal values. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 64-bit integer value. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt64(-64),
toInt64(-64.64),
toInt64('-64')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt64(-64): -64
toInt64(-64.64): -64
toInt64('-64'): -64
```
toInt64OrDefault
Introduced in: v21.11.0
Like [`toInt64`](#toInt64), this function converts an input value to a value of type [Int64](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt64OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int64`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int64 if successful, otherwise returns the default value if passed, or 0 if not. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt64OrDefault('-64', CAST('-1', 'Int64'))
```
```response title=Response theme={null}
-64
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt64OrDefault('abc', CAST('-1', 'Int64'))
```
```response title=Response theme={null}
-1
```
toInt64OrNull
Introduced in: v1.1.0
Like [`toInt64`](#toInt64), this function converts an input value to a value of type [Int64](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt64OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int64](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt64`](#toInt64).
* [`toInt64OrZero`](#toInt64OrZero).
* [`toInt64OrDefault`](#toInt64OrDefault).
**Syntax**
```sql theme={null}
toInt64OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int64, otherwise `NULL` if the conversion is unsuccessful. [`Int64`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt64OrNull('-64'),
toInt64OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt64OrNull('-64'): -64
toInt64OrNull('abc'): \N
```
toInt64OrZero
Introduced in: v1.1.0
Converts an input value to type [Int64](/reference/data-types/int-uint) but returns `0` in case of an error.
Like [`toInt64`](#toInt64) but returns `0` instead of throwing an exception.
See also:
* [`toInt64`](#toInt64).
* [`toInt64OrNull`](#toInt64OrNull).
* [`toInt64OrDefault`](#toInt64OrDefault).
**Syntax**
```sql theme={null}
toInt64OrZero(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal) or [`(U)Int*`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns the converted input value, otherwise `0` if conversion fails. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toInt64OrZero('123')
```
```response title=Response theme={null}
123
```
**Failed conversion returns zero**
```sql title=Query theme={null}
SELECT toInt64OrZero('abc')
```
```response title=Response theme={null}
0
```
toInt8
Introduced in: v1.1.0
Converts an input value to a value of type [`Int8`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt8('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int8](/reference/data-types/int-uint), 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](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toInt8OrZero`](#toInt8OrZero).
* [`toInt8OrNull`](#toInt8OrNull).
* [`toInt8OrDefault`](#toInt8OrDefault).
**Syntax**
```sql theme={null}
toInt8(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns an 8-bit integer value. [`Int8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt8(-8),
toInt8(-8.8),
toInt8('-8')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt8(-8): -8
toInt8(-8.8): -8
toInt8('-8'): -8
```
toInt8OrDefault
Introduced in: v21.11.0
Like [`toInt8`](#toInt8), this function converts an input value to a value of type [Int8](/reference/data-types/int-uint) 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**
```sql theme={null}
toInt8OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`Int8`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type Int8 if successful, otherwise returns the default value if passed, or 0 if not. [`Int8`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toInt8OrDefault('-8', CAST('-1', 'Int8'))
```
```response title=Response theme={null}
-8
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toInt8OrDefault('abc', CAST('-1', 'Int8'))
```
```response title=Response theme={null}
-1
```
toInt8OrNull
Introduced in: v1.1.0
Like [`toInt8`](#toInt8), this function converts an input value to a value of type [Int8](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt8OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int8](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt8`](#toInt8).
* [`toInt8OrZero`](#toInt8OrZero).
* [`toInt8OrDefault`](#toInt8OrDefault).
**Syntax**
```sql theme={null}
toInt8OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int8, otherwise `NULL` if the conversion is unsuccessful. [`Int8`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt8OrNull('-8'),
toInt8OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt8OrNull('-8'): -8
toInt8OrNull('abc'): \N
```
toInt8OrZero
Introduced in: v1.1.0
Like [`toInt8`](#toInt8), this function converts an input value to a value of type [Int8](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toInt8OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [Int8](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toInt8`](#toInt8).
* [`toInt8OrNull`](#toInt8OrNull).
* [`toInt8OrDefault`](#toInt8OrDefault).
**Syntax**
```sql theme={null}
toInt8OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type Int8, otherwise `0` if the conversion is unsuccessful. [`Int8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toInt8OrZero('8'),
toInt8OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toInt8OrZero('8'): 8
toInt8OrZero('abc'): 0
```
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 like `toIntervalSecond`, `toIntervalMinute`, `toIntervalDay`, etc.,
but allows the unit to be specified dynamically as a string parameter.
**Syntax**
```sql theme={null}
toInterval(value, unit)
```
**Arguments**
* `value` — The numeric value representing the number of units. Can be any numeric type. [`Int8`](/reference/data-types/int-uint) or [`Int16`](/reference/data-types/int-uint) or [`Int32`](/reference/data-types/int-uint) or [`Int64`](/reference/data-types/int-uint) or [`UInt8`](/reference/data-types/int-uint) or [`UInt16`](/reference/data-types/int-uint) or [`UInt32`](/reference/data-types/int-uint) or [`UInt64`](/reference/data-types/int-uint) or [`Float32`](/reference/data-types/float) or [`Float64`](/reference/data-types/float)
* `unit` — The unit of time. Must be a constant string. Valid values: 'nanosecond', 'microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'. [`String`](/reference/data-types/string)
**Returned value**
Returns an Interval value of the specified type. The result type depends on the unit: IntervalNanosecond, IntervalMicrosecond, IntervalMillisecond, IntervalSecond, IntervalMinute, IntervalHour, IntervalDay, IntervalWeek, IntervalMonth, IntervalQuarter, or IntervalYear. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Create intervals with different units**
```sql title=Query theme={null}
SELECT
toInterval(5, 'second') AS seconds,
toInterval(3, 'day') AS days,
toInterval(2, 'month') AS months
```
```response title=Response theme={null}
┌─seconds─┬─days─┬─months─┐
│ 5 │ 3 │ 2 │
└─────────┴──────┴────────┘
```
**Use intervals in date arithmetic**
```sql title=Query theme={null}
SELECT
now() AS current_time,
now() + toInterval(1, 'hour') AS one_hour_later,
now() - toInterval(7, 'day') AS week_ago
```
```response title=Response theme={null}
┌─────────current_time─┬──one_hour_later─────┬────────────week_ago─┐
│ 2025-01-04 10:30:00 │ 2025-01-04 11:30:00 │ 2024-12-28 10:30:00 │
└──────────────────────┴─────────────────────┴─────────────────────┘
```
**Dynamic interval creation**
```sql title=Query theme={null}
SELECT toDate('2025-01-01') + toInterval(number, 'day') AS dates
FROM numbers(5)
```
```response title=Response theme={null}
┌──────dates─┐
│ 2025-01-01 │
│ 2025-01-02 │
│ 2025-01-03 │
│ 2025-01-04 │
│ 2025-01-05 │
└────────────┘
```
toIntervalDay
Introduced in: v1.1.0
Returns an interval of `n` days of data type [`IntervalDay`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalDay(n)
```
**Arguments**
* `n` — Number of days. Integer numbers or string representations thereof, and float numbers. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` days. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalDay(5) AS interval_to_days
SELECT date + interval_to_days AS result
```
```response title=Response theme={null}
┌─────result─┐
│ 2025-06-20 │
└────────────┘
```
toIntervalHour
Introduced in: v1.1.0
Returns an interval of `n` hours of data type [`IntervalHour`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalHour(n)
```
**Arguments**
* `n` — Number of hours. Integer numbers or string representations thereof, and float numbers. [`Int*`](/reference/data-types/int-uint) or [`UInt*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` hours. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalHour(12) AS interval_to_hours
SELECT date + interval_to_hours AS result
```
```response title=Response theme={null}
┌──────────────result─┐
│ 2025-06-15 12:00:00 │
└─────────────────────┘
```
toIntervalMicrosecond
Introduced in: v22.6.0
Returns an interval of `n` microseconds of data type [`IntervalMicrosecond`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalMicrosecond(n)
```
**Arguments**
* `n` — Number of microseconds. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` microseconds. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDateTime('2025-06-15') AS date,
toIntervalMicrosecond(30) AS interval_to_microseconds
SELECT date + interval_to_microseconds AS result
```
```response title=Response theme={null}
┌─────────────────────result─┐
│ 2025-06-15 00:00:00.000030 │
└────────────────────────────┘
```
toIntervalMillisecond
Introduced in: v22.6.0
Returns an interval of `n` milliseconds of data type [IntervalMillisecond](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalMillisecond(n)
```
**Arguments**
* `n` — Number of milliseconds. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` milliseconds. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDateTime('2025-06-15') AS date,
toIntervalMillisecond(30) AS interval_to_milliseconds
SELECT date + interval_to_milliseconds AS result
```
```response title=Response theme={null}
┌──────────────────result─┐
│ 2025-06-15 00:00:00.030 │
└─────────────────────────┘
```
toIntervalMinute
Introduced in: v1.1.0
Returns an interval of `n` minutes of data type [`IntervalMinute`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalMinute(n)
```
**Arguments**
* `n` — Number of minutes. Integer numbers or string representations thereof, and float numbers. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` minutes. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalMinute(12) AS interval_to_minutes
SELECT date + interval_to_minutes AS result
```
```response title=Response theme={null}
┌──────────────result─┐
│ 2025-06-15 00:12:00 │
└─────────────────────┘
```
toIntervalMonth
Introduced in: v1.1.0
Returns an interval of `n` months of data type [`IntervalMonth`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalMonth(n)
```
**Arguments**
* `n` — Number of months. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` months. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalMonth(1) AS interval_to_month
SELECT date + interval_to_month AS result
```
```response title=Response theme={null}
┌─────result─┐
│ 2025-07-15 │
└────────────┘
```
toIntervalNanosecond
Introduced in: v22.6.0
Returns an interval of `n` nanoseconds of data type [`IntervalNanosecond`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalNanosecond(n)
```
**Arguments**
* `n` — Number of nanoseconds. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` nanoseconds. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDateTime('2025-06-15') AS date,
toIntervalNanosecond(30) AS interval_to_nanoseconds
SELECT date + interval_to_nanoseconds AS result
```
```response title=Response theme={null}
┌────────────────────────result─┐
│ 2025-06-15 00:00:00.000000030 │
└───────────────────────────────┘
```
toIntervalQuarter
Introduced in: v1.1.0
Returns an interval of `n` quarters of data type [`IntervalQuarter`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalQuarter(n)
```
**Arguments**
* `n` — Number of quarters. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` quarters. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalQuarter(1) AS interval_to_quarter
SELECT date + interval_to_quarter AS result
```
```response title=Response theme={null}
┌─────result─┐
│ 2025-09-15 │
└────────────┘
```
toIntervalSecond
Introduced in: v1.1.0
Returns an interval of `n` seconds of data type [`IntervalSecond`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalSecond(n)
```
**Arguments**
* `n` — Number of seconds. Integer numbers or string representations thereof, and float numbers. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` seconds. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalSecond(30) AS interval_to_seconds
SELECT date + interval_to_seconds AS result
```
```response title=Response theme={null}
┌──────────────result─┐
│ 2025-06-15 00:00:30 │
└─────────────────────┘
```
toIntervalWeek
Introduced in: v1.1.0
Returns an interval of `n` weeks of data type [`IntervalWeek`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalWeek(n)
```
**Arguments**
* `n` — Number of weeks. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` weeks. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2025-06-15') AS date,
toIntervalWeek(1) AS interval_to_week
SELECT date + interval_to_week AS result
```
```response title=Response theme={null}
┌─────result─┐
│ 2025-06-22 │
└────────────┘
```
toIntervalYear
Introduced in: v1.1.0
Returns an interval of `n` years of data type [`IntervalYear`](/reference/data-types/special-data-types/interval).
**Syntax**
```sql theme={null}
toIntervalYear(n)
```
**Arguments**
* `n` — Number of years. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string)
**Returned value**
Returns an interval of `n` years. [`Interval`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH
toDate('2024-06-15') AS date,
toIntervalYear(1) AS interval_to_year
SELECT date + interval_to_year AS result
```
```response title=Response theme={null}
┌─────result─┐
│ 2025-06-15 │
└────────────┘
```
toLowCardinality
Introduced in: v18.12.0
Converts the input argument to the [LowCardinality](/reference/data-types/lowcardinality) version of same data type.
To convert from the `LowCardinality` data type to a regular data type, use the [CAST](#CAST) function.
For example: `CAST(x AS String)`.
**Syntax**
```sql theme={null}
toLowCardinality(expr)
```
**Arguments**
* `expr` — Expression resulting in one of the supported data types. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns the input value converted to the `LowCardinality` data type. [`LowCardinality`](/reference/data-types/lowcardinality)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toLowCardinality('1')
```
```response title=Response theme={null}
┌─toLowCardinality('1')─┐
│ 1 │
└───────────────────────┘
```
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.
**Syntax**
```sql theme={null}
toString(value[, timezone])
```
**Arguments**
* `value` — Value to convert to string. [`Any`](/reference/data-types)
* `timezone` — Optional. Timezone name for DateTime conversion. [`String`](/reference/data-types/string)
**Returned value**
Returns a string representation of the input value. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
now() AS ts,
time_zone,
toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10
```
```response title=Response theme={null}
┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast │ 2023-09-08 20:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘
```
toStringCutToZero
Introduced in: v1.1.0
Accepts a [String](/reference/data-types/string) or [FixedString](/reference/data-types/fixedstring) argument and returns a String that contains a copy of the original string truncated at the first null byte.
Null bytes (\0) are considered as string terminators.
This function is useful for processing C-style strings or binary data where null bytes mark the end of meaningful content.
**Syntax**
```sql theme={null}
toStringCutToZero(s)
```
**Arguments**
* `s` — String or FixedString to process. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns a String containing the characters before the first null byte. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toStringCutToZero('hello'),
toStringCutToZero('hello\0world')
```
```response title=Response theme={null}
┌─toStringCutToZero('hello')─┬─toStringCutToZero('hello\\0world')─┐
│ hello │ hello │
└────────────────────────────┴───────────────────────────────────┘
```
toTime
Introduced in: v1.1.0
Converts an input value to type [Time](/reference/data-types/time).
Supports conversion from String, FixedString, DateTime, or numeric types representing seconds since midnight.
**Syntax**
```sql theme={null}
toTime(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`DateTime`](/reference/data-types/datetime) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns the converted value. [`Time`](/reference/data-types/time)
**Examples**
**String to Time conversion**
```sql title=Query theme={null}
SELECT toTime('14:30:25')
```
```response title=Response theme={null}
14:30:25
```
**DateTime to Time conversion**
```sql title=Query theme={null}
SELECT toTime(toDateTime('2025-04-15 14:30:25'))
```
```response title=Response theme={null}
14:30:25
```
**Integer to Time conversion**
```sql title=Query theme={null}
SELECT toTime(52225)
```
```response title=Response theme={null}
14:30:25
```
toTime64
Introduced in: v25.6.0
Converts an input value to type [Time64](/reference/data-types/time64).
Supports conversion from String, FixedString, DateTime64, or numeric types representing microseconds since midnight.
Provides microsecond precision for time values.
**Syntax**
```sql theme={null}
toTime64(x)
```
**Arguments**
* `x` — Input value to convert. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`DateTime64`](/reference/data-types/datetime64) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns the converted input value with microsecond precision. [`Time64(6)`](/reference/data-types/time64)
**Examples**
**String to Time64 conversion**
```sql title=Query theme={null}
SELECT toTime64('14:30:25.123456')
```
```response title=Response theme={null}
14:30:25.123456
```
**DateTime64 to Time64 conversion**
```sql title=Query theme={null}
SELECT toTime64(toDateTime64('2025-04-15 14:30:25.123456', 6))
```
```response title=Response theme={null}
14:30:25.123456
```
**Integer to Time64 conversion**
```sql title=Query theme={null}
SELECT toTime64(52225123456)
```
```response title=Response theme={null}
14:30:25.123456
```
toTime64OrNull
Introduced in: v25.6.0
Converts an input value to a value of type `Time64` but returns `NULL` in case of an error.
Like [`toTime64`](#toTime64) but returns `NULL` instead of throwing an exception on conversion errors.
See also:
* [`toTime64`](#toTime64)
* [`toTime64OrZero`](#toTime64OrZero)
**Syntax**
```sql theme={null}
toTime64OrNull(x)
```
**Arguments**
* `x` — A string representation of a time with subsecond precision. [`String`](/reference/data-types/string)
**Returned value**
Returns a Time64 value if successful, otherwise `NULL`. [`Time64`](/reference/data-types/time64) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toTime64OrNull('12:30:45.123'), toTime64OrNull('invalid')
```
```response title=Response theme={null}
┌─toTime64OrNull('12:30:45.123')─┬─toTime64OrNull('invalid')─┐
│ 12:30:45.123 │ ᴺᵁᴸᴸ │
└────────────────────────────────┴───────────────────────────┘
```
toTime64OrZero
Introduced in: v25.6.0
Converts an input value to a value of type Time64 but returns `00:00:00.000` in case of an error.
Like [`toTime64`](#toTime64) but returns `00:00:00.000` instead of throwing an exception on conversion errors.
**Syntax**
```sql theme={null}
toTime64OrZero(x)
```
**Arguments**
* `x` — A string representation of a time with subsecond precision. [`String`](/reference/data-types/string)
**Returned value**
Returns a Time64 value if successful, otherwise `00:00:00.000`. [`Time64`](/reference/data-types/time64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toTime64OrZero('12:30:45.123'), toTime64OrZero('invalid')
```
```response title=Response theme={null}
┌─toTime64OrZero('12:30:45.123')─┬─toTime64OrZero('invalid')─┐
│ 12:30:45.123 │ 00:00:00.000 │
└────────────────────────────────┴──────────────────────────┘
```
toTimeOrNull
Introduced in: v1.1.0
Converts an input value to a value of type Time but returns `NULL` in case of an error.
Like [`toTime`](#toTime) but returns `NULL` instead of throwing an exception on conversion errors.
See also:
* [`toTime`](#toTime)
* [`toTimeOrZero`](#toTimeOrZero)
**Syntax**
```sql theme={null}
toTimeOrNull(x)
```
**Arguments**
* `x` — A string representation of a time. [`String`](/reference/data-types/string)
**Returned value**
Returns a Time value if successful, otherwise `NULL`. [`Time`](/reference/data-types/time) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toTimeOrNull('12:30:45'), toTimeOrNull('invalid')
```
```response title=Response theme={null}
┌─toTimeOrNull('12:30:45')─┬─toTimeOrNull('invalid')─┐
│ 12:30:45 │ ᴺᵁᴸᴸ │
└──────────────────────────┴─────────────────────────┘
```
toTimeOrZero
Introduced in: v1.1.0
Converts an input value to a value of type Time but returns `00:00:00` in case of an error.
Like toTime but returns `00:00:00` instead of throwing an exception on conversion errors.
**Syntax**
```sql theme={null}
toTimeOrZero(x)
```
**Arguments**
* `x` — A string representation of a time. [`String`](/reference/data-types/string)
**Returned value**
Returns a Time value if successful, otherwise `00:00:00`. [`Time`](/reference/data-types/time)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toTimeOrZero('12:30:45'), toTimeOrZero('invalid')
```
```response title=Response theme={null}
┌─toTimeOrZero('12:30:45')─┬─toTimeOrZero('invalid')─┐
│ 12:30:45 │ 00:00:00 │
└──────────────────────────┴─────────────────────────┘
```
toUInt128
Introduced in: v1.1.0
Converts an input value to a value of type [`UInt128`](/reference/functions/regular-functions/type-conversion-functions#toUInt128).
Throws an exception in case of an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt128('0xc0fe');`.
If the input value cannot be represented within the bounds of UInt128, the result over or under flows.
This is not considered an error.
See also:
* [`toUInt128OrZero`](#toUInt128OrZero).
* [`toUInt128OrNull`](#toUInt128OrNull).
* [`toUInt128OrDefault`](#toUInt128OrDefault).
**Syntax**
```sql theme={null}
toUInt128(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 128-bit unsigned integer value. [`UInt128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt128(128),
toUInt128(128.8),
toUInt128('128')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt128(128): 128
toUInt128(128.8): 128
toUInt128('128'): 128
```
toUInt128OrDefault
Introduced in: v21.11.0
Like [`toUInt128`](#toUInt128), this function converts an input value to a value of type [`UInt128`](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt128OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt128`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt128 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt128`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt128OrDefault('128', CAST('0', 'UInt128'))
```
```response title=Response theme={null}
128
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt128OrDefault('abc', CAST('0', 'UInt128'))
```
```response title=Response theme={null}
0
```
toUInt128OrNull
Introduced in: v21.6.0
Like [`toUInt128`](#toUInt128), this function converts an input value to a value of type [`UInt128`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt128OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt128`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt128`](#toUInt128).
* [`toUInt128OrZero`](#toUInt128OrZero).
* [`toUInt128OrDefault`](#toUInt128OrDefault).
**Syntax**
```sql theme={null}
toUInt128OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt128, otherwise `NULL` if the conversion is unsuccessful. [`UInt128`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt128OrNull('128'),
toUInt128OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt128OrNull('128'): 128
toUInt128OrNull('abc'): \N
```
toUInt128OrZero
Introduced in: v1.1.0
Like [`toUInt128`](#toUInt128), this function converts an input value to a value of type [`UInt128`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt128OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt128`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt128`](#toUInt128).
* [`toUInt128OrNull`](#toUInt128OrNull).
* [`toUInt128OrDefault`](#toUInt128OrDefault).
**Syntax**
```sql theme={null}
toUInt128OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt128, otherwise `0` if the conversion is unsuccessful. [`UInt128`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt128OrZero('128'),
toUInt128OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt128OrZero('128'): 128
toUInt128OrZero('abc'): 0
```
toUInt16
Introduced in: v1.1.0
Converts an input value to a value of type [`UInt16`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt16('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt16`](/reference/data-types/int-uint), 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](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toUInt16OrZero`](#toUInt16OrZero).
* [`toUInt16OrNull`](#toUInt16OrNull).
* [`toUInt16OrDefault`](#toUInt16OrDefault).
**Syntax**
```sql theme={null}
toUInt16(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 16-bit unsigned integer value. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt16(16),
toUInt16(16.16),
toUInt16('16')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt16(16): 16
toUInt16(16.16): 16
toUInt16('16'): 16
```
toUInt16OrDefault
Introduced in: v21.11.0
Like [`toUInt16`](#toUInt16), this function converts an input value to a value of type [UInt16](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt16OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt16`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt16 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt16OrDefault('16', CAST('0', 'UInt16'))
```
```response title=Response theme={null}
16
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt16OrDefault('abc', CAST('0', 'UInt16'))
```
```response title=Response theme={null}
0
```
toUInt16OrNull
Introduced in: v1.1.0
Like [`toUInt16`](#toUInt16), this function converts an input value to a value of type [`UInt16`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt16OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt16`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt16`](#toUInt16).
* [`toUInt16OrZero`](#toUInt16OrZero).
* [`toUInt16OrDefault`](#toUInt16OrDefault).
**Syntax**
```sql theme={null}
toUInt16OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type `UInt16`, otherwise `NULL` if the conversion is unsuccessful. [`UInt16`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt16OrNull('16'),
toUInt16OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt16OrNull('16'): 16
toUInt16OrNull('abc'): \N
```
toUInt16OrZero
Introduced in: v1.1.0
Like [`toUInt16`](#toUInt16), this function converts an input value to a value of type [`UInt16`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt16OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt16`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt16`](#toUInt16).
* [`toUInt16OrNull`](#toUInt16OrNull).
* [`toUInt16OrDefault`](#toUInt16OrDefault).
**Syntax**
```sql theme={null}
toUInt16OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt16, otherwise `0` if the conversion is unsuccessful. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt16OrZero('16'),
toUInt16OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt16OrZero('16'): 16
toUInt16OrZero('abc'): 0
```
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\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* 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.
See also:
* [`toUInt256OrZero`](#toUInt256OrZero).
* [`toUInt256OrNull`](#toUInt256OrNull).
* [`toUInt256OrDefault`](#toUInt256OrDefault).
**Syntax**
```sql theme={null}
toUInt256(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 256-bit unsigned integer value. [`UInt256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt256(256),
toUInt256(256.256),
toUInt256('256')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt256(256): 256
toUInt256(256.256): 256
toUInt256('256'): 256
```
toUInt256OrDefault
Introduced in: v21.11.0
Like [`toUInt256`](#toUInt256), this function converts an input value to a value of type [UInt256](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt256OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt256`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt256 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt256`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt256OrDefault('-256', CAST('0', 'UInt256'))
```
```response title=Response theme={null}
0
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt256OrDefault('abc', CAST('0', 'UInt256'))
```
```response title=Response theme={null}
0
```
toUInt256OrNull
Introduced in: v20.8.0
Like [`toUInt256`](#toUInt256), this function converts an input value to a value of type [`UInt256`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt256OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt256`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt256`](#toUInt256).
* [`toUInt256OrZero`](#toUInt256OrZero).
* [`toUInt256OrDefault`](#toUInt256OrDefault).
**Syntax**
```sql theme={null}
toUInt256OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt256, otherwise `NULL` if the conversion is unsuccessful. [`UInt256`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt256OrNull('256'),
toUInt256OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt256OrNull('256'): 256
toUInt256OrNull('abc'): \N
```
toUInt256OrZero
Introduced in: v20.8.0
Like [`toUInt256`](#toUInt256), this function converts an input value to a value of type [`UInt256`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt256OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt256`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt256`](#toUInt256).
* [`toUInt256OrNull`](#toUInt256OrNull).
* [`toUInt256OrDefault`](#toUInt256OrDefault).
**Syntax**
```sql theme={null}
toUInt256OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt256, otherwise `0` if the conversion is unsuccessful. [`UInt256`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt256OrZero('256'),
toUInt256OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt256OrZero('256'): 256
toUInt256OrZero('abc'): 0
```
toUInt32
Introduced in: v1.1.0
Converts an input value to a value of type [`UInt32`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt32('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt32`](/reference/data-types/int-uint), the result over or under flows.
This is not considered an error.
For example: `SELECT toUInt32(4294967296) == 0;`
The function uses [rounding towards zero](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toUInt32OrZero`](#toUInt32OrZero).
* [`toUInt32OrNull`](#toUInt32OrNull).
* [`toUInt32OrDefault`](#toUInt32OrDefault).
**Syntax**
```sql theme={null}
toUInt32(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 32-bit unsigned integer value. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt32(32),
toUInt32(32.32),
toUInt32('32')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt32(32): 32
toUInt32(32.32): 32
toUInt32('32'): 32
```
toUInt32OrDefault
Introduced in: v21.11.0
Like [`toUInt32`](#toUInt32), this function converts an input value to a value of type [UInt32](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt32OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt32`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt32 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt32OrDefault('32', CAST('0', 'UInt32'))
```
```response title=Response theme={null}
32
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt32OrDefault('abc', CAST('0', 'UInt32'))
```
```response title=Response theme={null}
0
```
toUInt32OrNull
Introduced in: v1.1.0
Like [`toUInt32`](#toUInt32), this function converts an input value to a value of type [`UInt32`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt32OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt32`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt32`](#toUInt32).
* [`toUInt32OrZero`](#toUInt32OrZero).
* [`toUInt32OrDefault`](#toUInt32OrDefault).
**Syntax**
```sql theme={null}
toUInt32OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type `UInt32`, otherwise `NULL` if the conversion is unsuccessful. [`UInt32`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt32OrNull('32'),
toUInt32OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt32OrNull('32'): 32
toUInt32OrNull('abc'): \N
```
toUInt32OrZero
Introduced in: v1.1.0
Like [`toUInt32`](#toUInt32), this function converts an input value to a value of type [`UInt32`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt32OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt32`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt32`](#toUInt32).
* [`toUInt32OrNull`](#toUInt32OrNull).
* [`toUInt32OrDefault`](#toUInt32OrDefault).
**Syntax**
```sql theme={null}
toUInt32OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt32, otherwise `0` if the conversion is unsuccessful. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt32OrZero('32'),
toUInt32OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt32OrZero('32'): 32
toUInt32OrZero('abc'): 0
```
toUInt64
Introduced in: v1.1.0
Converts an input value to a value of type [`UInt64`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported types:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt64('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt64`](/reference/data-types/int-uint), the result over or under flows.
This is not considered an error.
For example: `SELECT toUInt64(18446744073709551616) == 0;`
The function uses [rounding towards zero](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toUInt64OrZero`](#toUInt64OrZero).
* [`toUInt64OrNull`](#toUInt64OrNull).
* [`toUInt64OrDefault`](#toUInt64OrDefault).
**Syntax**
```sql theme={null}
toUInt64(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns a 64-bit unsigned integer value. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt64(64),
toUInt64(64.64),
toUInt64('64')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt64(64): 64
toUInt64(64.64): 64
toUInt64('64'): 64
```
toUInt64OrDefault
Introduced in: v21.11.0
Like [`toUInt64`](#toUInt64), this function converts an input value to a value of type [UInt64](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt64OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt64 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt64OrDefault('64', CAST('0', 'UInt64'))
```
```response title=Response theme={null}
64
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt64OrDefault('abc', CAST('0', 'UInt64'))
```
```response title=Response theme={null}
0
```
toUInt64OrNull
Introduced in: v1.1.0
Like [`toUInt64`](#toUInt64), this function converts an input value to a value of type [`UInt64`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `NULL`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt64OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt64`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt64`](#toUInt64).
* [`toUInt64OrZero`](#toUInt64OrZero).
* [`toUInt64OrDefault`](#toUInt64OrDefault).
**Syntax**
```sql theme={null}
toUInt64OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt64, otherwise `NULL` if the conversion is unsuccessful. [`UInt64`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt64OrNull('64'),
toUInt64OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt64OrNull('64'): 64
toUInt64OrNull('abc'): \N
```
toUInt64OrZero
Introduced in: v1.1.0
Like [`toUInt64`](#toUInt64), this function converts an input value to a value of type [`UInt64`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int\*.
Unsupported arguments (return `0`):
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt64OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt64`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt64`](#toUInt64).
* [`toUInt64OrNull`](#toUInt64OrNull).
* [`toUInt64OrDefault`](#toUInt64OrDefault).
**Syntax**
```sql theme={null}
toUInt64OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt64, otherwise `0` if the conversion is unsuccessful. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt64OrZero('64'),
toUInt64OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt64OrZero('64'): 64
toUInt64OrZero('abc'): 0
```
toUInt8
Introduced in: v1.1.0
Converts an input value to a value of type [`UInt8`](/reference/data-types/int-uint).
Throws an exception in case of an error.
Supported arguments:
* Values or string representations of type (U)Int\*.
* Values of type Float\*.
Unsupported arguments:
* String representations of Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt8('0xc0fe');`.
If the input value cannot be represented within the bounds of [UInt8](/reference/data-types/int-uint), 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](https://en.wikipedia.org/wiki/Rounding#Rounding_towards_zero), meaning it truncates fractional digits of numbers.
See also:
* [`toUInt8OrZero`](#toUInt8OrZero).
* [`toUInt8OrNull`](#toUInt8OrNull).
* [`toUInt8OrDefault`](#toUInt8OrDefault).
**Syntax**
```sql theme={null}
toUInt8(expr)
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns an 8-bit unsigned integer value. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt8(8),
toUInt8(8.8),
toUInt8('8')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt8(8): 8
toUInt8(8.8): 8
toUInt8('8'): 8
```
toUInt8OrDefault
Introduced in: v21.11.0
Like [`toUInt8`](#toUInt8), this function converts an input value to a value of type [UInt8](/reference/data-types/int-uint) 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**
```sql theme={null}
toUInt8OrDefault(expr[, default])
```
**Arguments**
* `expr` — Expression returning a number or a string representation of a number. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `default` — Optional. The default value to return if parsing is unsuccessful. [`UInt8`](/reference/data-types/int-uint)
**Returned value**
Returns a value of type UInt8 if successful, otherwise returns the default value if passed, or 0 if not. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Successful conversion**
```sql title=Query theme={null}
SELECT toUInt8OrDefault('8', CAST('0', 'UInt8'))
```
```response title=Response theme={null}
8
```
**Failed conversion**
```sql title=Query theme={null}
SELECT toUInt8OrDefault('abc', CAST('0', 'UInt8'))
```
```response title=Response theme={null}
0
```
toUInt8OrNull
Introduced in: v1.1.0
Like [`toUInt8`](#toUInt8), this function converts an input value to a value of type [`UInt8`](/reference/data-types/int-uint) but returns `NULL` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `NULL`):
* String representations of ordinary Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt8OrNull('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt8`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt8`](#toUInt8).
* [`toUInt8OrZero`](#toUInt8OrZero).
* [`toUInt8OrDefault`](#toUInt8OrDefault).
**Syntax**
```sql theme={null}
toUInt8OrNull(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt8, otherwise `NULL` if the conversion is unsuccessful. [`UInt8`](/reference/data-types/int-uint) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt8OrNull('42'),
toUInt8OrNull('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt8OrNull('42'): 42
toUInt8OrNull('abc'): \N
```
toUInt8OrZero
Introduced in: v1.1.0
Like [`toUInt8`](#toUInt8), this function converts an input value to a value of type [`UInt8`](/reference/data-types/int-uint) but returns `0` in case of an error.
Supported arguments:
* String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return `0`):
* String representations of ordinary Float\* values, including `NaN` and `Inf`.
* String representations of binary and hexadecimal values, e.g. `SELECT toUInt8OrZero('0xc0fe');`.
If the input value cannot be represented within the bounds of [`UInt8`](/reference/data-types/int-uint), overflow or underflow of the result occurs.
This is not considered an error.
See also:
* [`toUInt8`](#toUInt8).
* [`toUInt8OrNull`](#toUInt8OrNull).
* [`toUInt8OrDefault`](#toUInt8OrDefault).
**Syntax**
```sql theme={null}
toUInt8OrZero(x)
```
**Arguments**
* `x` — A String representation of a number. [`String`](/reference/data-types/string)
**Returned value**
Returns a value of type UInt8, otherwise `0` if the conversion is unsuccessful. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUInt8OrZero('-8'),
toUInt8OrZero('abc')
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toUInt8OrZero('-8'): 0
toUInt8OrZero('abc'): 0
```
toUUID
Introduced in: v1.1.0
Converts a String value to a UUID value.
**Syntax**
```sql theme={null}
toUUID(string)
```
**Arguments**
* `string` — UUID as a string. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns a UUID from the string representation of the UUID. [`UUID`](/reference/data-types/uuid)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid
```
```response title=Response theme={null}
┌─────────────────────────────────uuid─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │
└──────────────────────────────────────┘
```
toUUIDOrZero
Introduced in: v20.12.0
Converts an input value to a value of type [UUID](/reference/data-types/uuid) but returns zero UUID in case of an error.
Like [`toUUID`](/reference/functions/regular-functions/type-conversion-functions#toUUID) but returns zero UUID (`00000000-0000-0000-0000-000000000000`) instead of throwing an exception on conversion errors.
Supported arguments:
* String representations of UUID in standard format (8-4-4-4-12 hexadecimal digits).
* String representations of UUID without hyphens (32 hexadecimal digits).
Unsupported arguments (return zero UUID):
* Invalid string formats.
* Non-string types.
**Syntax**
```sql theme={null}
toUUIDOrZero(x)
```
**Arguments**
* `x` — A string representation of a UUID. [`String`](/reference/data-types/string)
**Returned value**
Returns a UUID value if successful, otherwise zero UUID (`00000000-0000-0000-0000-000000000000`). [`UUID`](/reference/data-types/uuid)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
toUUIDOrZero('550e8400-e29b-41d4-a716-446655440000') AS valid_uuid,
toUUIDOrZero('invalid-uuid') AS invalid_uuid
```
```response title=Response theme={null}
┌─valid_uuid───────────────────────────┬─invalid_uuid─────────────────────────┐
│ 550e8400-e29b-41d4-a716-446655440000 │ 00000000-0000-0000-0000-000000000000 │
└──────────────────────────────────────┴──────────────────────────────────────┘
```
toUnixTimestamp64Micro
Introduced in: v20.5.0
Converts a [`DateTime64`](/reference/data-types/datetime64) to a [`Int64`](/reference/data-types/int-uint) 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.
**Syntax**
```sql theme={null}
toUnixTimestamp64Micro(value)
```
**Arguments**
* `value` — DateTime64 value with any precision. [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns a Unix timestamp in microseconds. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH toDateTime64('2025-02-13 23:31:31.011123', 6, 'UTC') AS dt64
SELECT toUnixTimestamp64Micro(dt64);
```
```response title=Response theme={null}
┌─toUnixTimestamp64Micro(dt64)─┐
│ 1739489491011123 │
└────────────────────────────────┘
```
toUnixTimestamp64Milli
Introduced in: v20.5.0
Converts a [`DateTime64`](/reference/data-types/datetime64) to a [`Int64`](/reference/data-types/int-uint) 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.
**Syntax**
```sql theme={null}
toUnixTimestamp64Milli(value)
```
**Arguments**
* `value` — DateTime64 value with any precision. [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns a Unix timestamp in milliseconds. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH toDateTime64('2025-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Milli(dt64);
```
```response title=Response theme={null}
┌─toUnixTimestamp64Milli(dt64)─┐
│ 1739489491011 │
└──────────────────────────────┘
```
toUnixTimestamp64Nano
Introduced in: v20.5.0
Converts a [`DateTime64`](/reference/data-types/datetime64) to a [`Int64`](/reference/functions/regular-functions/type-conversion-functions#toInt64) 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.
**Syntax**
```sql theme={null}
toUnixTimestamp64Nano(value)
```
**Arguments**
* `value` — DateTime64 value with any precision. [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns a Unix timestamp in nanoseconds. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH toDateTime64('2025-02-13 23:31:31.011123456', 9, 'UTC') AS dt64
SELECT toUnixTimestamp64Nano(dt64);
```
```response title=Response theme={null}
┌─toUnixTimestamp64Nano(dt64)────┐
│ 1739489491011123456 │
└────────────────────────────────┘
```
toUnixTimestamp64Second
Introduced in: v24.12.0
Converts a [`DateTime64`](/reference/data-types/datetime64) to a [`Int64`](/reference/data-types/int-uint) 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.
**Syntax**
```sql theme={null}
toUnixTimestamp64Second(value)
```
**Arguments**
* `value` — DateTime64 value with any precision. [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns a Unix timestamp in seconds. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
WITH toDateTime64('2025-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Second(dt64);
```
```response title=Response theme={null}
┌─toUnixTimestamp64Second(dt64)─┐
│ 1739489491 │
└───────────────────────────────┘
```