Type conversion functions

**Syntax**

```
toDateTime32(x[, timezone])
```

**Arguments**

- `x` — Input value to convert. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) or [`UInt*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone for the returned `DateTime` value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the converted input value. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**The value is within the range**

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

```
┌───────────────────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**

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

```
┌───────────────────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**

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

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

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

Introduced in: v20.1.0

Converts an input value to a value of type [`DateTime64`](/docs/sql-reference/data-types/datetime64).

**Syntax**

```
toDateTime64(expr, scale[, timezone])
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `scale` — Tick size (precision): 10^(-scale) seconds. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Time zone for the specified `DateTime64` object. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a calendar date and time of day, with sub-second precision. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**The value is within the range**

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

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

**As decimal with precision**

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

```
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘
```

**With timezone**

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

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

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

Introduced in: v21.11.0

Like [toDateTime64](#toDateTime64), this function converts an input value to a value of type [DateTime64](/docs/sql-reference/data-types/datetime64),
but returns either the default value of [DateTime64](/docs/sql-reference/data-types/datetime64)
or the provided default if an invalid argument is received.

**Syntax**

```
toDateTime64OrDefault(expr, scale[, timezone, default])
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `scale` — Tick size (precision): 10^-precision seconds. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Time zone. [`String`](/docs/sql-reference/data-types/string)
- `default` — Optional. The default value to return if parsing is unsuccessful. [`DateTime64`](/docs/sql-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`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Successful conversion**

```
SELECT toDateTime64OrDefault('1976-10-18 00:00:00.30', 3)
```

```
1976-10-18 00:00:00.300
```

**Failed conversion**

```
SELECT toDateTime64OrDefault('1976-10-18 00:00:00 30', 3, 'UTC', toDateTime64('2001-01-01 00:00:00.00',3))
```

```
2000-12-31 23:00:00.000
```

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

```
toDateTime64OrNull(x)
```

**Arguments**

- `x` — A string representation of a date with time and subsecond precision. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a DateTime64 value if successful, otherwise `NULL`. [`DateTime64`](/docs/sql-reference/data-types/datetime64) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT toDateTime64OrNull('2025-12-30 13:44:17.123'), toDateTime64OrNull('invalid')
```

```
┌─toDateTime64OrNull('2025-12-30 13:44:17.123')─┬─toDateTime64OrNull('invalid')─┐
│                         2025-12-30 13:44:17.123 │                          ᴺᵁᴸᴸ │
└─────────────────────────────────────────────────┴───────────────────────────────┘
```

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

Introduced in: v20.1.0

Converts an input value to a value of type [DateTime64](/docs/sql-reference/data-types/datetime64) but returns the lower boundary of [DateTime64](/docs/sql-reference/data-types/datetime64) if an invalid argument is received.
The same as [toDateTime64](#toDateTime64) but returns lower boundary of [DateTime64](/docs/sql-reference/data-types/datetime64) if an invalid argument is received.

See also:

- [toDateTime64](#toDateTime64).
- [toDateTime64OrNull](#toDateTime64OrNull).
- [toDateTime64OrDefault](#toDateTime64OrDefault).

**Syntax**

```
toDateTime64OrZero(x)
```

**Arguments**

- `x` — A string representation of a date with time and subsecond precision. [`String`](/docs/sql-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`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT toDateTime64OrZero('2025-12-30 13:44:17.123'), toDateTime64OrZero('invalid')
```