Functions for working with dates and times

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

Returns the millisecond in the minute (0 - 59) of `datetime`. [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3));
```

```
┌──toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3))─┐
│                                                        456 │
└────────────────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the minute component (0-59) of a `Date` or `DateTime` value.

**Syntax**

```
toMinute(datetime)
```

**Aliases**: `MINUTE`

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v21.1.0

Converts a [Proleptic Gregorian calendar](https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar) date in text form `YYYY-MM-DD` to a [Modified Julian Day](https://en.wikipedia.org/wiki/Julian_day#Variants) number in `Int32`. This function supports date from `0000-01-01` to `9999-12-31`. It raises an exception if the argument cannot be parsed as a date, or the date is invalid.

**Syntax**

```
toModifiedJulianDay(date)
```

**Arguments**

- `date` — The date in String form. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns Modified Julian Day number. [`Int32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Convert date to Modified Julian Day**

```
SELECT toModifiedJulianDay('2020-01-01')
```

```
┌─toModifiedJulianDay('2020-01-01')─┐
│                             58849 │
└───────────────────────────────────┘
```

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

Introduced in: v21.1.0

Similar to [`toModifiedJulianDay()`](#toModifiedJulianDay), but instead of raising exceptions it returns `NULL`.

**Syntax**

```
toModifiedJulianDayOrNull(date)
```

**Arguments**

- `date` — Date in text form. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns the modified Julian day number for valid `date`, otherwise `null`. [`Nullable(Int32)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Convert date to Modified Julian Day with null handling**

```
SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT toModifiedJulianDayOrNull('0000-00-00'); -- invalid date, returns NULL
```

```
┌─toModifiedJu⋯020-01-01')─┐
│                    58849 │
└──────────────────────────┘
┌─toModifiedJu⋯000-00-00')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Rounds down a date or date with time to the Monday of the same week. Returns the date.

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

```
toMonday(value)
```

**Arguments**

- `value` — Date or date with time to round down to the Monday of the week. [`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 Monday of the same week for the given date or date with time. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Round down to the Monday of the week**

```
SELECT
toMonday(toDateTime('2023-04-21 10:20:30')), -- A Friday
toMonday(toDate('2023-04-24'));              -- Already a Monday
```

```
┌─toMonday(toDateTime('2023-04-21 10:20:30'))─┬─toMonday(toDate('2023-04-24'))─┐
│                                  2023-04-17 │                     2023-04-24 │
└─────────────────────────────────────────────┴────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the month component (1-12) of a `Date` or `DateTime` value.

**Syntax**

```
toMonth(datetime)
```

**Aliases**: `MONTH`

**Arguments**

- `datetime` — Date or date with time to get the month 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 month of the given date/time [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

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

Introduced in: v25.3.0

Returns amount of months passed from year 1970

**Syntax**

```
toMonthNumSinceEpoch(date)
```

**Arguments**

- `date` — A date or date with time. [`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 toMonthNumSinceEpoch(toDate('2024-10-01'))
```

```
657
```

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

Introduced in: v1.1.0

Returns the quarter of the year (1-4) for a given `Date` or `DateTime` value.

**Syntax**

```
toQuarter(datetime)
```

**Aliases**: `QUARTER`

**Arguments**

- `datetime` — Date or date with time to get the quarter of 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 quarter of the year for the given date/time [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

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

Introduced in: v1.1.0

Converts a date or date with time to the number of days elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in days between two dates or dates with time, e.g., `toRelativeDayNum(dt1) - toRelativeDayNum(dt2)`.

**Syntax**

```
toRelativeDayNum(date)
```

**Arguments**