Functions for working with dates and times

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

Introduced in: v23.9.0

For a given date, returns the number of days which have passed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero) in the
[proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).

The calculation is the same as in MySQL's [`TO_DAYS`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_to-days) function.

**Syntax**

```
toDaysSinceYearZero(date[, time_zone])
```

**Aliases**: `TO_DAYS`

**Arguments**

- `date` — The date or date with time for which to calculate the number of days since year zero from. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `time_zone` — Time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the number of days passed since date `0000-01-01`. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Calculate days since year zero**

```
SELECT toDaysSinceYearZero(toDate('2023-09-08'))
```

```
┌─toDaysSinceYearZero(toDate('2023-09-08')))─┐
│                                     713569 │
└────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the hour component (0-23) of a `DateTime` or `DateTime64` value.

**Syntax**

```
toHour(datetime)
```

**Aliases**: `HOUR`

**Arguments**

- `datetime` — Date with time to get the hour from. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the hour (0-23) of `datetime`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT toHour(toDateTime('2023-04-21 10:20:30'))
```

```
┌─toHour(toDateTime('2023-04-21 10:20:30'))─┐
│                                        10 │
└───────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

Returns the ISO week number of a date or date with time.

This is a compatibility function that is equivalent to `toWeek(date, 3)`.
ISO weeks start on Monday and the first week of the year contains January 4th.
According to ISO 8601, week numbers are in the range from 1 to 53.

Note that dates near the beginning or end of a year may return a week number from the previous or next year. For example,
December 29, 2025 returns week 1 because it falls in the first week that contains January 4, 2026.

**Syntax**

```
toISOWeek(datetime[, timezone])
```

**Arguments**

- `datetime` — Date or date with time to get the ISO week number from. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the ISO week number according to ISO 8601 standard. Returns a number between 1 and 53. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get ISO week numbers**

```
SELECT toDate('2016-12-27') AS date, toISOWeek(date) AS isoWeek
```

```
┌───────date─┬─isoWeek─┐
│ 2016-12-27 │      52 │
└────────────┴─────────┘
```

**ISO week can belong to different year**

```
SELECT toDate('2025-12-29') AS date, toISOWeek(date) AS isoWeek, toYear(date) AS year
```

```
┌───────date─┬─isoWeek─┬─year─┐
│ 2025-12-29 │       1 │ 2025 │
└────────────┴─────────┴──────┘
```

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

Introduced in: v18.4.0

Converts a date or date with time to the ISO year number.

**Syntax**

```
toISOYear(datetime)
```

**Arguments**

- `datetime` — The value with date or date with time. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the input value converted to an ISO year number. [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get ISO year from date values**

```
SELECT
toISOYear(toDate('2024/10/02')) as year1,
toISOYear(toDateTime('2024-10-02 01:30:00')) as year2
```

```
┌─week1─┬─week2─┐
│    40 │    40 │
└───────┴───────┘
```

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

Introduced in: v1.1.0

Rounds up a date or date with time to the last day of the month.

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toLastDayOfMonth(value)
```

**Aliases**: `LAST_DAY`

**Arguments**

- `value` — The date or date with time to round up to the last day of the month. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the date of the last day of the month for the given date or date with time. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Round up to the last day of the month**

```
SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))
```

```
┌─toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-04-30 │
└─────────────────────────────────────────────────────┘
```

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

Introduced in: v23.5.0

Rounds a date or date with time up to the nearest Saturday or Sunday.

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toLastDayOfWeek(datetime[, mode[, timezone]])
```

**Arguments**

- `datetime` — A date or date with time to convert. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `mode` — Determines the first day of the week as described in the `toWeek()` function. Default `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. The timezone to use for the conversion. If not specified, the server's timezone is used. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the date of the nearest Saturday or Sunday, on or after the given date, depending on the mode. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Round up to the nearest Saturday or Sunday**

```
SELECT
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
    toLastDayOfWeek(toDate('2023-04-23')), /* a Sunday */
    toLastDayOfWeek(toDate('2023-04-23'), 1) /* a Sunday */
FORMAT Vertical
```

```
Row 1:
──────
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-23
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-22
toLastDayOfWeek(toDate('2023-04-23')):                   2023-04-23
toLastDayOfWeek(toDate('2023-04-23'), 1):                2023-04-23
```

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

Introduced in: v24.2.0

Returns the millisecond component (0-999) of a `DateTime` or `DateTime64` value.

**Syntax**

```
toMillisecond(datetime)
```

**Aliases**: `MILLISECOND`

**Arguments**

- `datetime` — Date with time to get the millisecond from. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**