Type conversion functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/type-conversion-functions.md)

## Common issues with data conversion[​](#common-issues-with-data-conversion "Direct link to Common issues with data conversion")

ClickHouse generally uses the [same behavior as C++ programs](https://en.cppreference.com/w/cpp/language/implicit_conversion).

`to<type>` functions and [cast](#CAST) behave differently in some cases, for example in case of [LowCardinality](/docs/sql-reference/data-types/lowcardinality): [cast](#CAST) removes [LowCardinality](/docs/sql-reference/data-types/lowcardinality) trait `to<type>` functions don't. The same with [Nullable](/docs/sql-reference/data-types/nullable), this behaviour is not compatible with SQL standard, and it can be changed using [cast\_keep\_nullable](/docs/operations/settings/settings#cast_keep_nullable) setting.

Note

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:

```
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[​](#to-string-functions "Direct link to to-string-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[​](#to-date-and-date-time-functions "Direct link to to-date-and-date-time-functions")

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

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

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

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

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

**Example**

Query:

```
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
```

Result:

```
┌──────────────────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`](/docs/sql-reference/functions/date-time-functions#toUnixTimestamp) function.

## CAST[​](#CAST "Direct link to CAST")

Introduced in: v1.1.0

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

**Syntax**

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

**Arguments**

- `x` — A value of any type. [`Any`](/docs/sql-reference/data-types)
- `T` — The target data type. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the converted value with the target data type. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Basic usage**

```
SELECT CAST(42, 'String')
```

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

**Using AS syntax**

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

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

**Using :: syntax**

```
SELECT '123'::UInt32
```

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

## DATE[​](#DATE "Direct link to DATE")

Introduced in: v21.2.0

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

**Syntax**

```
DATE(expr)
```

**Arguments**

- `expr` — The value to convert. [`String`](/docs/sql-reference/data-types/string) or [`UInt32`](/docs/sql-reference/data-types/int-uint) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Returns a Date value. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Basic usage**

```
SELECT DATE('2023-01-01')
```

```
2023-01-01
```

## accurateCast[​](#accurateCast "Direct link to 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**

```
accurateCast(x, T)
```

**Arguments**

- `x` — A value to convert. [`Any`](/docs/sql-reference/data-types)
- `T` — The target data type name. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the converted value with the target data type. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Successful conversion**

```
SELECT accurateCast(42, 'UInt16')
```

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

**String to number**

```
SELECT accurateCast('123.45', 'Float64')
```