Functions for working with dates and times

**Returned value**

Returns the week number according to the specified mode. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get week numbers with different modes**

```
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
```

```
┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘
```

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

Introduced in: v1.1.0

Converts a date or date with time to a `UInt32` number containing the year and month number (YYYY \* 100 + MM).
Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

This function is the opposite of function `YYYYMMDDToDate()`.

**Syntax**

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

**Arguments**

- `datetime` — A date or date with time to convert. [`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)
- `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a UInt32 number containing the year and month number (YYYY \* 100 + MM). [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Convert current date to YYYYMM format**

```
SELECT toYYYYMM(now(), 'US/Eastern')
```

```
┌─toYYYYMM(now(), 'US/Eastern')─┐
│                        202303 │
└───────────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a date or date with time to a `UInt32` number containing the year and month number (YYYY \* 10000 + MM \* 100 + DD). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

**Syntax**

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

**Arguments**

- `datetime` — A date or date with time to convert. [`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)
- `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `UInt32` number containing the year, month and day (YYYY \* 10000 + MM \* 100 + DD). [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Convert current date to YYYYMMDD format**

```
SELECT toYYYYMMDD(now(), 'US/Eastern')
```

```
┌─toYYYYMMDD(now(), 'US/Eastern')─┐
│                        20230302 │
└─────────────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a date or date with time to a `UInt64` number containing the year and month number (YYYY \* 10000000000 + MM \* 100000000 + DD \* 1000000 + hh \* 10000 + mm \* 100 + ss).
Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

**Syntax**

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

**Arguments**

- `datetime` — Date or date with time to convert. [`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)
- `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `UInt64` number containing the year, month, day, hour, minute and second (YYYY \* 10000000000 + MM \* 100000000 + DD \* 1000000 + hh \* 10000 + mm \* 100 + ss). [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Convert current date and time to YYYYMMDDhhmmss format**

```
SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern')
```

```
┌─toYYYYMMDDhhmmss(now(), 'US/Eastern')─┐
│                        20230302112209 │
└───────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the year component (AD) of a `Date` or `DateTime` value.

**Syntax**

```
toYear(datetime)
```

**Aliases**: `YEAR`

**Arguments**

- `datetime` — Date or date with time to get the year 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)

**Returned value**

Returns the year of the given Date or DateTime [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

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

Introduced in: v25.3.0

Returns amount of years passed from year 1970

**Syntax**

```
toYearNumSinceEpoch(date)
```

**Arguments**

- `date` — A date or date with time to convert. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Positive integer

**Examples**

**Example**

```
SELECT toYearNumSinceEpoch(toDate('2024-10-01'))
```

```
54
```

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

Introduced in: v20.1.0

Returns the year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year.

The mode argument works like the mode argument of [`toWeek()`](/docs/sql-reference/functions/date-time-functions#toWeek).

Warning: The week number returned by `toYearWeek()` can be different from what the `toWeek()` returns. `toWeek()` always returns week number in the context of the given year, and in case `toWeek()` returns `0`, `toYearWeek()` returns the value corresponding to the last week of previous year. See `prev_yearWeek` in example below.

The first argument can also be specified as [`String`](/docs/sql-reference/data-types/string) in a format supported by [`parseDateTime64BestEffort()`](/docs/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffort). Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.

**Syntax**

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

**Aliases**: `yearweek`

**Arguments**

- `datetime` — Date or date with time to get the year and week of. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `mode` — Optional. A mode `0` to `9` determines the first day of the week and the range of the week number. Default `0`. - `timezone` — Optional. Time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns year and week number as a combined integer value. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get year-week combinations with different modes**

```
SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9, toYearWeek(toDate('2022-01-01')) AS prev_yearWeek
```

```
┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┬─prev_yearWeek─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │        202152 │
└────────────┴───────────┴───────────┴───────────┴───────────────┘
```

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

Introduced in: v1.1.0

Returns the current date at moment of query analysis. Same as `toDate(now())`.

**Syntax**

```
today()
```

**Aliases**: `curdate`, `current_date`

**Arguments**