Type conversion functions

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

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

```
parseDateTimeBestEffortOrZero(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 zero date/datetime (`1970-01-01` or `1970-01-01 00:00:00`) if the input cannot be parsed. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

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

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

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

Introduced in: v1.1.0

This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, e.g. `YYYY-MM-DD hh:mm:ss`, and other date formats where the month and date components can be unambiguously extracted, e.g. `YYYYMMDDhhmmss`, `YYYY-MM`, `DD hh`, or `YYYY-MM-DD hh:mm:ss ±h:mm`.
If the month and the date components cannot be unambiguously extracted, e.g. `MM/DD/YYYY`, `MM-DD-YYYY`, or `MM-DD-YY`, it prefers the US date format instead of `DD/MM/YYYY`, `DD-MM-YYYY`, or `DD-MM-YY`.
As an exception to the previous statement, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of [`parseDateTimeBestEffort`](#parseDateTimeBestEffort), e.g. `15/08/2020` is parsed as `2020-08-15`.

**Syntax**

```
parseDateTimeBestEffortUS(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` using US date format preference for ambiguous cases. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT parseDateTimeBestEffortUS('02/10/2025') AS us_format,
       parseDateTimeBestEffortUS('15/08/2025') AS fallback_to_standard
```

```
┌─us_format───────────┬─fallback_to_standard─┐
│ 2025-02-10 00:00:00 │  2025-08-15 00:00:00 │
└─────────────────────┴──────────────────────┘
```

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

Introduced in: v1.1.0

Same as [`parseDateTimeBestEffortUS`](#parseDateTimeBestEffortUS) function except that it returns `NULL` when it encounters a date format that cannot be processed.

This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, but prefers the US date format for ambiguous cases, with `NULL` return on parsing errors.

**Syntax**

```
parseDateTimeBestEffortUSOrNull(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 using US format preference, 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 parseDateTimeBestEffortUSOrNull('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrNull('invalid') AS invalid
```

```
┌─valid_us────────────┬─invalid─┐
│ 2025-02-10 00:00:00 │    ᴺᵁᴸᴸ │
└─────────────────────┴─────────┘
```

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

Introduced in: v1.1.0

Same as [`parseDateTimeBestEffortUS`](#parseDateTimeBestEffortUS) function except that it returns zero date (`1970-01-01`) or zero date with time (`1970-01-01 00:00:00`) when it encounters a date format that cannot be processed.

This function behaves like [`parseDateTimeBestEffort`](#parseDateTimeBestEffort) for ISO date formats, but prefers the US date format for ambiguous cases, with zero return on parsing errors.

**Syntax**

```
parseDateTimeBestEffortUSOrZero(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` using US format preference, or zero date/datetime (`1970-01-01` or `1970-01-01 00:00:00`) if the input cannot be parsed. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT parseDateTimeBestEffortUSOrZero('02/10/2025') AS valid_us,
       parseDateTimeBestEffortUSOrZero('invalid') AS invalid
```

```
┌─valid_us────────────┬─invalid─────────────┐
│ 2025-02-10 00:00:00 │ 1970-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
```

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

Introduced in: v23.3.0

Parses a date and time string according to a Joda date format string.

This function is the inverse of [`formatDateTimeInJodaSyntax`](/docs/sql-reference/functions/date-time-functions#formatDateTimeInJodaSyntax).
It parses a String argument using a Joda-style format String. Returns a DateTime type.

Refer to [Joda Time documentation](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) for the format patterns.

**Syntax**

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

**Arguments**

- `time_string` — String to be parsed into DateTime. [`String`](/docs/sql-reference/data-types/string)
- `format` — Format string in Joda syntax 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 DateTime parsed from the input string according to the Joda style format string. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Usage example**

```
SELECT parseDateTimeInJodaSyntax('2025-01-04 23:00:00', 'yyyy-MM-dd HH:mm:ss')
```