Type conversion functions

**Returned value**

Returns a `DateTime` object parsed from the string, or zero date (`1970-01-01 00:00:00`) if the parsing fails. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT
    parseDateTime32BestEffortOrZero('23/10/2025 12:12:57') AS valid,
    parseDateTime32BestEffortOrZero('invalid date') AS invalid
```

```
┌─valid───────────────┬─invalid─────────────┐
│ 2025-10-23 12:12:57 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
```

## parseDateTime64[​](#parseDateTime64 "Direct link to 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`](/docs/sql-reference/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**

```
parseDateTime64(time_string, format[, timezone])
```

**Arguments**

- `time_string` — String to be parsed into DateTime64. [`String`](/docs/sql-reference/data-types/string)
- `format` — Format string specifying how to parse time\_string. [`String`](/docs/sql-reference/data-types/string)
- `timezone` — Optional. Timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a DateTime64 parsed from the input string according to the MySQL style format string. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')
```

```
┌─parseDateTime64('2025-01-04 23:00:00.123', '%Y-%m-%d %H:%i:%s.%f')─┐
│                                       2025-01-04 23:00:00.123       │
└─────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

Same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) function but also parse milliseconds and microseconds and returns [`DateTime64`](/docs/sql-reference/data-types/datetime64) data type.

**Syntax**

```
parseDateTime64BestEffort(time_string[, precision[, time_zone]])
```

**Arguments**

- `time_string` — String containing a date or date with time to convert. [`String`](/docs/sql-reference/data-types/string)
- `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` converted to the [`DateTime64`](/docs/sql-reference/data-types/datetime64) data type. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

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

```
┌──────────────────────────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[​](#parseDateTime64BestEffortOrNull "Direct link to 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**

```
parseDateTime64BestEffortOrNull(time_string[, precision[, time_zone]])
```

**Arguments**

- `time_string` — String containing a date or date with time to convert. [`String`](/docs/sql-reference/data-types/string)
- `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` converted to [`DateTime64`](/docs/sql-reference/data-types/datetime64), or `NULL` if the input cannot be parsed. [`DateTime64`](/docs/sql-reference/data-types/datetime64) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT parseDateTime64BestEffortOrNull('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrNull('invalid') AS invalid
```

```
┌─valid───────────────────┬─invalid─┐
│ 2025-01-01 01:01:00.123 │    ᴺᵁᴸᴸ │
└─────────────────────────┴─────────┘
```

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

```
parseDateTime64BestEffortOrZero(time_string[, precision[, time_zone]])
```

**Arguments**

- `time_string` — String containing a date or date with time to convert. [`String`](/docs/sql-reference/data-types/string)
- `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` converted to [`DateTime64`](/docs/sql-reference/data-types/datetime64), or zero date/datetime (`1970-01-01 00:00:00.000`) if the input cannot be parsed. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT parseDateTime64BestEffortOrZero('2025-01-01 01:01:00.123') AS valid,
       parseDateTime64BestEffortOrZero('invalid') AS invalid
```

```
┌─valid───────────────────┬─invalid─────────────────┐
│ 2025-01-01 01:01:00.123 │ 1970-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘
```

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

```
parseDateTime64BestEffortUS(time_string [, precision [, time_zone]])
```

**Arguments**

- `time_string` — String containing a date or date with time to convert. [`String`](/docs/sql-reference/data-types/string)
- `precision` — Optional. Required precision. `3` for milliseconds, `6` for microseconds. Default: `3`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `time_zone` — Optional. Timezone. The function parses `time_string` according to the timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` converted to [`DateTime64`](/docs/sql-reference/data-types/datetime64) using US date format preference for ambiguous cases. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT parseDateTime64BestEffortUS('02/10/2025 12:30:45.123') AS us_format,
       parseDateTime64BestEffortUS('15/08/2025 10:15:30.456') AS fallback_to_standard
```

```
┌─us_format───────────────┬─fallback_to_standard────┐
│ 2025-02-10 12:30:45.123 │ 2025-08-15 10:15:30.456 │
└─────────────────────────┴─────────────────────────┘
```

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