# Type Conversion Functions

# toInt

Converts an input value to a value the Int data type. This function family includes:

toInt8(expr) — Converts to a value of data type Int8.
toInt16(expr) — Converts to a value of data type Int16.
toInt32(expr) — Converts to a value of data type Int32.
toInt64(expr) — Converts to a value of data type Int64.
toInt128(expr) — Converts to a value of data type Int128.
toInt256(expr) — Converts to a value of data type Int256.

Arguments

expr — Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.

Returned value

Integer value in the Int8, Int16, Int32, Int64, Int128 or Int256 data type.
Functions use rounding towards zero, meaning they truncate fractional digits of numbers.
The behavior of functions for the NaN and Inf arguments is undefined. Remember about numeric conversions issues, when using the functions.

Example

SELECT toInt32(32), toInt16('16'), toInt8(8.8);

┌─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│          32 │            16 │           8 │
└─────────────┴───────────────┴─────────────┘

# toUInt

Converts an input value to the UInt data type. This function family includes:

toUInt8(expr) — Converts to a value of data type UInt8.
toUInt16(expr) — Converts to a value of data type UInt16.
toUInt32(expr) — Converts to a value of data type UInt32.
toUInt64(expr) — Converts to a value of data type UInt64.
toUInt256(expr) — Converts to a value of data type UInt256.

Arguments

expr — Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.

Returned value

Integer value in the UInt8, UInt16, UInt32, UInt64 or UInt256 data type.
Functions use rounding towards zero, meaning they truncate fractional digits of numbers.
The behavior of functions for negative arguments and for the NaN and Inf arguments is undefined. If you pass a string with a negative number, for example '-32', ClickHouse raises an exception. Remember about numeric conversions issues, when using the functions.

Example

SELECT toUInt32(32), toUInt16('16'), toUInt8(8.8);

┌─toUInt32(32)─┬─toUInt16('16')─┬─toUInt8(8.8)─┐
│           32 │             16 │            8 │
└──────────────┴────────────────┴──────────────┘

# toFloat

Converts an input value to the Float data type. This function family includes:

toFloat32(expr) — Converts to a value of data type Float32.
toFloat64(expr) — Converts to a value of data type Float64.

Arguments

expr — Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.

Returned value

Float value in the Float32 or Float64 data type.
Functions use rounding towards zero, meaning they truncate fractional digits of numbers.
The behavior of functions for the NaN and Inf arguments is undefined. Remember about numeric conversions issues, when using the functions.

Example

SELECT toFloat32('16'), toFloat64(8.8);

┌─toFloat32('16')─┬─toFloat64(8.8)─┐
│              16 │            8.8 │
└─────────────────┴────────────────┘

# toDate

Converts the argument to Date data type. If the argument is DateTime or DateTime64, it truncates it and leaves the date component of the DateTime:

SELECT now() AS x, toDate(x)

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

Examples

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

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

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

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

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

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

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

Row 1:
──────
current_time:     2023-11-14 10:37:49
ts:               1699929469
time_Amsterdam:   2023-11-14 10:37:49
time_Samoa:       2023-11-14 15:37:49
date_Amsterdam:   2023-11-14
date_Samoa:       2023-11-14
date_Amsterdam_2: 2023-11-14
date_Samoa_2:     2023-11-14

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

If the argument is a number and it is smaller than 65536, it is interpreted as the number of days since 1970-01-01 (the first UNIX day) and converted to Date. It corresponds to the internal numeric representation of the Date data type. Example:
```
SELECT toDate(12345)
```
```
┌─toDate(12345)─┐
│ 2003-10-20    │
└───────────────┘
```

This conversion does not depend on timezones.

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

SELECT toDate(10000000000.)

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

The function toDate can be also written in alternative forms:

SELECT now() AS time, toDate(time)

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

# toDateTime64

Converts the argument to the DateTime64 data type.

toDateTime64(expr, scale, [timezone])

Arguments

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

Returned value

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

Example

The value is within the range:

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

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

As decimal with precision:

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

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

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

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

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2299-12-31 23:59:59.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘

With timezone:

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

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

# toDecimal

Converts value to the Decimal (opens new window) data type with precision of S. The value can be a number or a string. The S (scale) parameter specifies the number of decimal places.

toDecimal32(value, S)
toDecimal64(value, S)
toDecimal128(value, S)
toDecimal256(value, S)

Arguments

expr — Expression, returns a value in the String data type. ClickHouse expects the textual representation of the decimal number. For example, '1.111'.
S — Scale, the number of decimal places in the resulting value.

Returned value

A value in the (Decimal(P,S)) data type. The value contains:
Number with S decimal places, if ClickHouse interprets the input string as a number.

Examples

SELECT toDecimal32(toString(1.111), 2) AS val, toTypeName(val);

┌──val─┬─toTypeName(toDecimal32(toString(1.111), 2))─┐
│ 1.11 │ Decimal(9, 2)                               │
└──────┴─────────────────────────────────────────────┘

# toString

Functions for converting between numbers, strings (but not fixed strings), dates, and dates with times. All 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 date and date-with-time formats for the toDate/toDateTime functions are defined as follows:
- YYYY-MM-DDYYYY-MM-DD hh:mm:ss
As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date. This allows support for the common occurrence of writing toDate(unix_timestamp), which otherwise would be an error and would require writing the more cumbersome toDate(toDateTime(unix_timestamp)).
Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.
Conversion between numeric types uses the same rules as assignments between different numeric types in C++.

Example

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

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-11-14 10:42:16 │ Europe/Amsterdam  │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Andorra    │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Astrakhan  │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Athens     │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Belfast    │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Belgrade   │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Berlin     │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Bratislava │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Brussels   │ 2023-11-14 10:42:16 │
│ 2023-11-14 10:42:16 │ Europe/Bucharest  │ 2023-11-14 10:42:16 │
└─────────────────────┴───────────────────┴─────────────────────┘

# parseDateTime()

Converts a String to DateTime according to a MySQL format string. This function is the opposite operation of function formatDateTime.

parseDateTime(str, format[, timezone])

Arguments

str — the String to be parsed
format — the format string
timezone — Timezone. Optional.

Returned value(s)

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

Supported format specifiers

All format specifiers listed in formatDateTime except:
%Q: Quarter (1-4)

Example

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

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