Type conversion functions

**Returned value**

Value of type Date32 if successful, otherwise returns the default value if passed or 1900-01-01 if not. [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Successful conversion**

```
SELECT toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))
```

```
1930-01-01
```

**Failed conversion**

```
SELECT toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))
```

```
2020-01-01
```

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

Introduced in: v21.9.0

Converts an input value to a value of type Date32 but returns `NULL` if an invalid argument is received.
The same as [`toDate32`](#toDate32) but returns `NULL` if an invalid argument is received.

**Syntax**

```
toDate32OrNull(x)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```
SELECT toDate32OrNull('2025-01-01'), toDate32OrNull('invalid')
```

```
┌─toDate32OrNull('2025-01-01')─┬─toDate32OrNull('invalid')─┐
│                   2025-01-01 │                      ᴺᵁᴸᴸ │
└──────────────────────────────┴───────────────────────────┘
```

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

Introduced in: v21.9.0

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

See also:

- [`toDate32`](#toDate32)
- [`toDate32OrNull`](#toDate32OrNull)
- [`toDate32OrDefault`](#toDate32OrDefault)

**Syntax**

```
toDate32OrZero(x)
```

**Arguments**

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

**Returned value**

Returns a Date32 value if successful, otherwise the lower boundary of Date32 (`1900-01-01`). [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Usage example**

```
SELECT toDate32OrZero('2025-01-01'), toDate32OrZero('')
```

```
┌─toDate32OrZero('2025-01-01')─┬─toDate32OrZero('')─┐
│                   2025-01-01 │         1900-01-01 │
└──────────────────────────────┴────────────────────┘
```

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

Introduced in: v21.11.0

Like [toDate](#toDate) but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of [Date](/docs/sql-reference/data-types/date).

**Syntax**

```
toDateOrDefault(expr[, 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)
- `default` — Optional. The default value to return if parsing is unsuccessful. [`Date`](/docs/sql-reference/data-types/date)

**Returned value**

Value of type Date if successful, otherwise returns the default value if passed or 1970-01-01 if not. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Successful conversion**

```
SELECT toDateOrDefault('2022-12-30')
```

```
2022-12-30
```

**Failed conversion**

```
SELECT toDateOrDefault('', CAST('2023-01-01', 'Date'))
```

```
2023-01-01
```

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

Introduced in: v1.1.0

Converts an input value to a value of type `Date` but returns `NULL` if an invalid argument is received.
The same as [`toDate`](#toDate) but returns `NULL` if an invalid argument is received.

**Syntax**

```
toDateOrNull(x)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```
SELECT toDateOrNull('2025-12-30'), toDateOrNull('invalid')
```

```
┌─toDateOrNull('2025-12-30')─┬─toDateOrNull('invalid')─┐
│                 2025-12-30 │                   ᴺᵁᴸᴸ │
└────────────────────────────┴────────────────────────┘
```

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

Introduced in: v1.1.0

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

See also:

- [`toDate`](#toDate)
- [`toDateOrNull`](#toDateOrNull)
- [`toDateOrDefault`](#toDateOrDefault)

**Syntax**

```
toDateOrZero(x)
```

**Arguments**

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

**Returned value**

Returns a Date value if successful, otherwise the lower boundary of Date (`1970-01-01`). [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Usage example**

```
SELECT toDateOrZero('2025-12-30'), toDateOrZero('')
```

```
┌─toDateOrZero('2025-12-30')─┬─toDateOrZero('')─┐
│                 2025-12-30 │       1970-01-01 │
└────────────────────────────┴──────────────────┘
```

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

Introduced in: v1.1.0

Converts an input value to type [DateTime](/docs/sql-reference/data-types/datetime).

Note

If `expr` is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp).
If `expr` is a [String](/docs/sql-reference/data-types/string), it may be interpreted as a Unix timestamp or as a string representation of date / date with time.
Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string `'1999'` may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.

**Syntax**

```
toDateTime(expr[, time_zone])
```

**Arguments**

- `expr` — The value. [`String`](/docs/sql-reference/data-types/string) or [`Int`](/docs/sql-reference/data-types/int-uint) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `time_zone` — Time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Usage example**

```
SELECT toDateTime('2025-01-01 00:00:00'), toDateTime(1735689600, 'UTC')
FORMAT Vertical
```

```
Row 1:
──────
toDateTime('2025-01-01 00:00:00'): 2025-01-01 00:00:00
toDateTime(1735689600, 'UTC'):     2025-01-01 00:00:00
```

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

Introduced in: v20.9.0

Converts an input value to type `DateTime`.
Supports conversion from `String`, `FixedString`, `Date`, `Date32`, `DateTime`, or numeric types (`(U)Int*`, `Float*`, `Decimal`).
DateTime32 provides extended range compared to `DateTime`, supporting dates from `1900-01-01` to `2299-12-31`.

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