Type conversion functions

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

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

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

```
accurateCastOrDefault(x, T[, default_value])
```

**Arguments**

- `x` — A value to convert. [`Any`](/docs/sql-reference/data-types)
- `T` — The target data type name. [`const String`](/docs/sql-reference/data-types/string)
- `default_value` — Optional. Default value to return if conversion fails. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the converted value with the target data type, or the default value if conversion is not possible. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Successful conversion**

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

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

**Failed conversion with explicit default**

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

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

**Failed conversion with implicit default**

```
SELECT accurateCastOrDefault('abc', 'UInt32')
```

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

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

```
accurateCastOrNull(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, or `NULL` if conversion is not possible. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Successful conversion**

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

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

**Failed conversion returns NULL**

```
SELECT accurateCastOrNull('abc', 'UInt32')
```

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

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

Introduced in: v20.7.0

Converts arbitrary expressions into a string via given format.

Note

If the format contains a suffix/prefix, it will be written in each row.
Only row-based formats are supported in this function.

**Syntax**

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

**Arguments**

- `format` — Text format. For example, CSV, TSV. [`String`](/docs/sql-reference/data-types/string)
- `x, y, ...` — Expressions. [`Any`](/docs/sql-reference/data-types)

**Returned value**

A formatted string. (for text formats it's usually terminated with the new line character). [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage**

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

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

**With custom format**

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

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

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

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

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

**Arguments**

- `format` — Text format. For example, CSV, TSV. [`String`](/docs/sql-reference/data-types/string)
- `x, y, ...` — Expressions. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a formatted string with newlines removed. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage**

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

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

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

Introduced in: v20.5.0

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

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

**Syntax**

```
fromUnixTimestamp64Micro(value[, timezone])
```

**Arguments**

- `value` — Unix timestamp in microseconds. [`Int64`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Timezone for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `DateTime64` value with microsecond precision. [`DateTime64(6)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT fromUnixTimestamp64Micro(1640995200123456)
```

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

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

Introduced in: v20.5.0

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

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

**Syntax**

```
fromUnixTimestamp64Milli(value[, timezone])
```

**Arguments**

- `value` — Unix timestamp in milliseconds. [`Int64`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Timezone for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

A `DateTime64` value with millisecond precision. [`DateTime64(3)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT fromUnixTimestamp64Milli(1640995200123)
```

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