Type conversion functions

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

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 and returns `NULL` when it encounters a date format that cannot be processed.

**Syntax**

```
parseDateTime64BestEffortUSOrNull(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 format preference, 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 parseDateTime64BestEffortUSOrNull('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrNull('invalid') AS invalid
```

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

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

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 and returns zero date or zero date time when it encounters a date format that cannot be processed.

**Syntax**

```
parseDateTime64BestEffortUSOrZero(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 format preference, 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 parseDateTime64BestEffortUSOrZero('02/10/2025 12:30:45.123') AS valid_us,
       parseDateTime64BestEffortUSOrZero('invalid') AS invalid
```

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

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

Introduced in: v24.10.0

Parses a date and time string with sub-second precision according to a Joda date format string.

This function is the inverse of [`formatDateTimeInJodaSyntax`](/docs/sql-reference/functions/date-time-functions#formatDateTimeInJodaSyntax) for DateTime64.
It parses a String argument using a Joda-style format String. Returns a DateTime64 type which can represent dates from 1900 to 2299 with sub-second precision.

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

**Syntax**

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

**Arguments**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v24.10.0

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

**Syntax**

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

**Arguments**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v24.10.0

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

**Syntax**

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

**Arguments**

- `time_string` — String to be parsed into DateTime64. [`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 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