Type conversion functions

**Returned value**

Returns DateTime64 parsed from input string, or zero DateTime64 if parsing fails. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')
```

```
┌─parseDateTime64InJodaSyntaxOrZero('2025-01-04 23:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS')─┐
│                                                              2025-01-04 23:00:00.123     │
└──────────────────────────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v24.11.0

Same as [`parseDateTime64`](#parseDateTime64) but returns `NULL` when it encounters an unparsable date format.

**Syntax**

```
parseDateTime64OrNull(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 DateTime64 parsed from input string, or NULL if parsing fails. [`Nullable(DateTime64)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Usage example**

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

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

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

Introduced in: v24.11.0

Same as [`parseDateTime64`](#parseDateTime64) but returns zero date when it encounters an unparsable date format.

**Syntax**

```
parseDateTime64OrZero(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 DateTime64 parsed from input string, or zero DateTime64 if parsing fails. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

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

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

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

Introduced in: v1.1.0

Converts a date and time in the String representation to DateTime data type.
The function parses [ISO 8601](https://www.iso.org/iso-8601-date-and-time-format.html), [RFC 1123 - 5.2.14 RFC-822](https://datatracker.ietf.org/doc/html/rfc822) Date and Time Specification, ClickHouse's and some other date and time formats.

Supported non-standard formats:

- A string containing 9..10 digit unix timestamp.
- A string with a date and a time component: `YYYYMMDDhhmmss`, `DD/MM/YYYY hh:mm:ss`, `DD-MM-YY hh:mm`, `YYYY-MM-DD hh:mm:ss`, etc.
- A string with a date, but no time component: `YYYY`, `YYYYMM`, `YYYY*MM`, `DD/MM/YYYY`, `DD-MM-YY` etc.
- A string with a day and time: `DD`, `DD hh`, `DD hh:mm`. In this case `MM` is substituted by `01`.
- A string that includes the date and time along with time zone offset information: `YYYY-MM-DD hh:mm:ss ±h:mm`, etc.
- A syslog timestamp: `Mmm dd hh:mm:ss`. For example, `Jun 9 14:20:32`.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name.
If the year is not specified, it is considered to be equal to the current year.

**Syntax**

```
parseDateTimeBestEffort(time_string[, time_zone])
```

**Arguments**

- `time_string` — String containing a date and time to convert. [`String`](/docs/sql-reference/data-types/string)
- `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` as a `DateTime`. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT parseDateTimeBestEffort('23/10/2025 12:12:57') AS parseDateTimeBestEffort
```

```
┌─parseDateTimeBestEffort─┐
│     2025-10-23 12:12:57 │
└─────────────────────────┘
```

**With timezone**

```
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2025 07:22:16 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffort
```

```
┌─parseDateTimeBestEffort─┐
│     2025-08-18 10:22:16 │
└─────────────────────────┘
```

**Unix timestamp**

```
SELECT parseDateTimeBestEffort('1735689600') AS parseDateTimeBestEffort
```

```
┌─parseDateTimeBestEffort─┐
│     2025-01-01 00:00:00 │
└─────────────────────────┘
```

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

Introduced in: v1.1.0

The same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) except that it returns `NULL` when it encounters a date format that cannot be processed.
The function parses [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601), [RFC 1123 - 5.2.14 RFC-822 Date and Time Specification](https://tools.ietf.org/html/rfc1123#page-55), ClickHouse's and some other date and time formats.

Supported non-standard formats:

- A string containing 9..10 digit unix timestamp.
- A string with a date and a time component: `YYYYMMDDhhmmss`, `DD/MM/YYYY hh:mm:ss`, `DD-MM-YY hh:mm`, `YYYY-MM-DD hh:mm:ss`, etc.
- A string with a date, but no time component: `YYYY`, `YYYYMM`, `YYYY*MM`, `DD/MM/YYYY`, `DD-MM-YY` etc.
- A string with a day and time: `DD`, `DD hh`, `DD hh:mm`. In this case `MM` is substituted by `01`.
- A string that includes the date and time along with time zone offset information: `YYYY-MM-DD hh:mm:ss ±h:mm`, etc.
- A syslog timestamp: `Mmm dd hh:mm:ss`. For example, `Jun 9 14:20:32`.

For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name.
If the year is not specified, it is considered to be equal to the current year.

**Syntax**

```
parseDateTimeBestEffortOrNull(time_string[, time_zone])
```

**Arguments**

- `time_string` — String containing a date and time to convert. [`String`](/docs/sql-reference/data-types/string)
- `time_zone` — Optional. Time zone according to which `time_string` is parsed. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `time_string` as a DateTime, or `NULL` if the input cannot be parsed. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

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

```
┌─valid───────────────┬─invalid─┐
│ 2025-10-23 12:12:57 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘
```

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

Introduced in: v1.1.0

Same as [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) except that it returns a zero date or a zero date time when it encounters a date format that cannot be processed.
The function parses [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601), [RFC 1123 - 5.2.14 RFC-822 Date and Time Specification](https://tools.ietf.org/html/rfc1123#page-55), ClickHouse's and some other date and time formats.

Supported non-standard formats: