> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://ocient.ferndocs.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server.

# Date and Time Functions

> Explore date and time functions in Ocient, essential for managing temporal data in analytics.

# Basic Date and Time Functions

## ADD_MONTHS

Adds the specified number of months to the date. Equivalent to using a date and adding a `MONTH(value)`.

**Syntax**

```sql
ADD_MONTHS(time, int)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                     |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function modifies by adding a specified number of months. |
| `int`        | `INT`                 | The number of months to add to the `time` value.                                                                    |

**Example**

```sql
SELECT ADD_MONTHS('2022-11-15 04:18:00',2);
```

*Output*: `2023-01-15`

## CENTURY

Returns the number of centuries.

**Syntax**

```sql
CENTURY(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                    |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes to return the number of its centuries. |

**Example**

In this example, the current date is `2023-01-03`.

```sql
SELECT CENTURY(CURRENT_DATE());
```

*Output*: `21`

## CURDATE

Alias for CURRENT_DATE.

## CURRENT_DATE

Alias for CURDATE.

Returns the current date in the format `YYYY-MM-DD`.

**Syntax**

```sql
CURRENT_DATE
```

**Example**

```sql
SELECT CURRENT_DATE;
```

*Output*: `2023-01-03`

## CURRENT_TIME

Returns the current time as a TIME value (e.g., `hh:mm:ss.mm`).

**Syntax**

```sql
CURRENT_TIME
```

**Example**

```sql
SELECT CURRENT_TIME;
```

*Output:* `19:40:04`

## CURRENT_TIMESTAMP

Alias for [**NOW**](#now).

Returns the current date and time as a TIMESTAMP value (e.g., `YYYY-MM-DD hh:mm:ss.mmm`).

**Syntax**

```sql
CURRENT_TIMESTAMP
```

**Example**

```sql
SELECT CURRENT_TIMESTAMP;
```

*Output*: `2023-01-03 11:00:03.316674397`

## DATE_PART

Alias for [**EXTRACT**](#extract).

## DATE_TRUNC

Returns the date or timestamp entered, truncated to the specified precision.

**Syntax**

```sql
DATE_TRUNC(precision, time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                                                                                                                                   |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `precision`  | `CHAR`                | The timestamp unit used to truncate the returned value. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM` |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function truncates to the specified precision. The `time` input supports precision values DAY or larger.                                                                                |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT DATE_TRUNC('NANOSECOND', CURRENT_TIMESTAMP());
```

*Output*: `2023-01-03 11:01:05.280975906`

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-01 11:00:59.114058181`.

```sql
SELECT DATE_TRUNC('MONTH', CURRENT_TIMESTAMP());
```

*Output*: `2023-01-01 00:00:00.000000000`

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-01 11:00:59.114058181`.

```sql
SELECT DATE_TRUNC('CENTURY', CURRENT_TIMESTAMP());
```

*Output*:  `2001-01-01 00:00:00.000000000`

## DAY

Alias for [**DAY_OF_MONTH**](#dayofmonth).

## DAY_OF_WEEK

Returns an integer, in the range of 1 to 7, that represents the day of the week. The value 1 is Sunday, and 7 is Saturday.

**Syntax**

```sql
DAY_OF_WEEK(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                      |
| ------------ | --------------------- | ---------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its day of the week. |

**Example**

```sql
SELECT DAY_OF_WEEK(CURRENT_DATE());
```

*Output*: `3`

## DAY_OF_YEAR

Alias for DOY.

Returns an integer in the range 1 to 366 that represents the day of the year.

**Syntax**

```sql
DAY_OF_YEAR(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                  |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its day of year. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT DAY_OF_YEAR(CURRENT_TIMESTAMP());
```

*Output*: `3`

## DAY_OF_MONTH

Alias for DAY.

Extracts the day-of-month portion of a timestamp or date as an integer.

**Syntax**

```sql
DAY_OF_MONTH(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                       |
| ------------ | --------------------- | ----------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its day of the month. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT DAY(CURRENT_TIMESTAMP());
```

*Output*: `3`

## DECADE

The decade is the year divided by 10.

**Syntax**

```sql
DECADE(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                             |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its decade. |

**Example**

This example uses `CURRENT_DATE` set to `2023-01-03`.

```sql
SELECT DECADE(CURRENT_DATE());
```

*Output*: `202`

## DOW

Alias for [**DAY_OF_WEEK**](#dayofweek).

## DOY

Alias for [**DAY_OF_YEAR**](#dayofyear).

## EOMONTH

Returns the last day of the month using the specified timestamp or date. If you specify both arguments, this function returns the last day of the month for the resulting timestamp or date after the system adds the specified number of months to the first argument value.

**Syntax**

```sql
EOMONTH(time [, int])
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                      |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for the last day of the month.                           |
| `int`        | `INT`                 | Optional. An integer value that represents the number of months to add to the `time` value. If unspecified, the value defaults to 0. |

**Examples**

This example returns the last day of the month for the current timestamp.

```sql
SELECT EOMONTH(CURRENT_TIMESTAMP(),0);
```

*Output*: `2023-02-27`

This example returns the last day of the month in November in 2022 by using the EOMONTH function to add one month to the last day of October in 2022.

```sql
SELECT EOMONTH(DATE('2022-10-31'), 1);
```

*Output*: `2022-11-30`

## EPOCH

The number of seconds after 1970-01-01 00:00:00 UTC.

**Syntax**

```sql
EPOCH(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                      |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for the number of seconds after 1970-01-01 00:00:00 UTC. |

**Example**

```sql
SELECT EPOCH(CURRENT_TIMESTAMP());
```

*Output*: `1672743901`

## EXTRACT

Extract a component from a timestamp or date.

**Syntax**

```sql
EXTRACT(precision FROM time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                                                                                                               |
| ------------ | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for the component to be extracted.                                                                                                                |
| `precision`  | `CHAR`                | The timestamp unit to be extracted. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM` |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP());
```

*Output*: `11.0`

## HOUR

Extracts the hour portion of a timestamp as an integer.

**Syntax**

```sql
HOUR(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                 |
| ------------ | --------------------- | ----------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its hour value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT HOUR(CURRENT_TIMESTAMP());
```

*Output*: `11`

## ISODOW

Extracts the day of the week based on ISO 8601, which ranges from Monday (1) to Sunday (7).

**Syntax**

```sql
ISODOW(timestamp or date)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                        |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its day-of-week value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-01-03 11:00:59.114058182`.

```sql
SELECT ISODOW(CURRENT_TIMESTAMP());
```

*Output*: `2`

## ISDATE

Returns `TRUE` if the input argument can be successfully cast to a date, `FALSE` otherwise.

**Syntax**

```sql
ISDATE(char)
```

| **Argument** | **Data** **Type** | **Description**               |
| ------------ | ----------------- | ----------------------------- |
| `char`       | `CHAR`            | A string to cast as a `DATE`. |

**Example**

```sql
SELECT ISDATE('2023-01-03');
```

*Output*: `TRUE`

## MAKEDATETIME

Returns a timestamp consisting of the specified date and time.

**Syntax**

```sql
MAKEDATETIME(date, time)
```

| **Argument** | **Data** **Type** | **Description**                       |
| ------------ | ----------------- | ------------------------------------- |
| `date`       | `CHAR`            | A date string for timestamp creation. |
| `time`       | `CHAR`            | A time string for timestamp creation. |

**Example**

```sql
SELECT MAKEDATETIME('2022-11-02','01:24:58');
```

*Output*: `2022-11-02 01:24:58.000000000`

## MILLISECOND

Extracts the millisecond portion of a timestamp as an integer.

**Syntax**

```sql
MILLISECOND(timestring)
```

| **Argument** | **Data** **Type** | **Description**                                                                                        |
| ------------ | ----------------- | ------------------------------------------------------------------------------------------------------ |
| `timestring` | `TIMESTAMP`       | A time value, specified as a timestamp or date, which the function extracts for the millisecond value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-22 13:59:25.286`.

```sql
SELECT MILLISECOND(CURRENT_TIMESTAMP());
```

*Output*: `286`

## MINUTE

Extracts the minute portion of a timestamp or date as an integer.

**Syntax**

```sql
MINUTE(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                   |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function extracts for the minute value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-22 14:00:40.764`.

```sql
SELECT MINUTE(CURRENT_TIMESTAMP());
```

*Output*: `0`

## MONTH_NAME

Returns the calendar name in English of the month for the specified date.

Syntax

```sql
MONTH_NAME(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                             |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes its month name. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-22 13:59:25.286`.

```sql
SELECT MONTH_NAME(CURRENT_TIMESTAMP());
```

*Output*: `February`

## MONTHS_BETWEEN

Returns the difference between the two dates or timestamps in months as a `DOUBLE`. The fractional months component is based on a 31-day month. If the two dates have the same day or are both the last day of the month, this function returns a whole number instead.

**Syntax**

```sql
MONTHS_BETWEEN(time1, time2)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                      |
| ------------ | --------------------- | -------------------------------------------------------------------------------------------------------------------- |
| `time1`      | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function compares to `time2` for the difference in months. |
| `time2`      | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function compares to `time1` for the difference in months. |

**Example**

```sql
SELECT MONTHS_BETWEEN(CURRENT_TIMESTAMP(), ADD_MONTHS(CURRENT_TIMESTAMP(),11));
```

*Output*: `-11`

## MONTH

Extracts the month portion of a timestamp or date as an integer.

**Syntax**

```sql
MONTH(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                  |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes for its month value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-22 13:59:25.286`.

```sql
SELECT MONTH(CURRENT_TIMESTAMP());
```

*Output*: `2`

## MSECS

The seconds field, including fractional parts. This is the same as `EXTRACT(MILLISECONDS FROM time)`.

**Syntax**

```sql
MSECS(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                 |
| ------------ | --------------------- | --------------------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date. The function multiplies the seconds part of the value by 1,000. |

**Example**

```sql
SELECT MSECS(CURRENT_TIMESTAMP());
```

*Output*: `37637.648`

## NANOS_TO_TIMESTAMP

Convert a number of nanoseconds into a timestamp equivalent to the duration after the epoch time, 1970-01-01 00:00:00 UTC.

**Syntax**

```sql
NANOS_TO_TIMESTAMP(time)
```

| **Argument** | **Data** **Type** | **Description**                                                                                                                                 |
| ------------ | ----------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| `ns`         | `INT`             | A number of nanoseconds, specified as an integer, which the function converts into a timestamp equivalent to the duration after the epoch time. |

**Example**

```sql
SELECT NANOS_TO_TIMESTAMP(10000000);
```

*Output*: `1969-12-31 16:00:00.010`

## NEXT_DAY

Returns the closest date after a specified date that lies on a specific day of the week.

**Syntax**

```sql
NEXT_DAY(date, character)
```

| **Argument** | **Data** **Type**              | **Description**                                                                                                                                                                                                                           |
| ------------ | ------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `date`       | `DATE`, `TIMESTAMP`, or `CHAR` | A time value, specified as a date, timestamp, or string, which the function uses to return the closest date for a specific day of the week. Can be any type that can be cast to `DATE`.                                                   |
| `character`  | `CHAR`                         | A string representing a day of the week. The string must match the first three characters (case insensitive) of the English name of any day of the week. If this string does not match the prefix of any day, this function returns NULL. |

**Example**

```sql
SELECT NEXT_DAY('2023-02-27', 'sat');
```

*Output*: `2023-03-04`

## NOW

Alias for [**CURRENT_TIMESTAMP**](#currenttimestamp).

## QUARTER

Returns an integer between 1 and 4 representing the quarter of the year in which the specified date falls.

**Syntax**

```sql
QUARTER(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                |
| ------------ | --------------------- | -------------------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function computes to return the quarter of the year. |

**Example**

```sql
SELECT QUARTER(DATE('2023-02-08 09:00:00.000'));
```

*Output*: `1`

## ROUND

Returns the specified date or timestamp, rounded to the specified precision. The precision argument behaves like [**DATE_TRUNC**](#datetrunc). The function rounds up values to the specified precision.

**Syntax**

```sql
ROUND(time, precision)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                                                                                                                                |
| ------------ | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `precision`  | `CHAR`                | The timestamp unit used to round the returned value. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM` |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function rounds to the specified precision.                                                                                                                                          |

**Example**

```sql
SELECT ROUND(CURRENT_TIMESTAMP() ,'HOUR');
```

*Output*: `2023-02-08 09:00:00.000`

## SECOND

Extracts the seconds portion of a timestamp as an integer.

**Syntax**

```sql
SECOND(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                      |
| ------------ | --------------------- | ---------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `TIME` | A time value, specified as a timestamp or date, which the function uses to return the seconds value. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-28 13:17:30.243`.

```sql
SELECT SECOND(CURRENT_TIMESTAMP());
```

*Output*: `30`

## TIMESTAMP_TO_NANOS

Convert timestamp into nanoseconds after epoch as `BIGINT`.

**Syntax**

```sql
TIMESTAMP_TO_NANOS(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                    |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------ |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function uses to return the nanoseconds after the epoch. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-28 13:20:12.373`.

```sql
SELECT TIMESTAMP_TO_NANOS(CURRENT_TIMESTAMP());
```

*Output*: `1677619212373708904`

## TO_TIMESTAMP

For information on using the TO_TIMESTAMP conversion function, see the [**TO_TIMESTAMP**](/guides/sql-reference/formatting-functions) page.

## USECS

Returns the seconds part of a time value, including fractional parts, as an integer. Same as `EXTRACT(MICROSECONDS FROM time)`.

**Syntax**

```sql
USECS(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                     |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date. The function multiplies the seconds part of the value by 1,000,000. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-28 13:32:17.438`.

```sql
SELECT USECS(CURRENT_TIMESTAMP());
```

*Output*: `17438041`

## WEEK

Returns the ISO-8601 week number, as an integer, of the specified timestamp or date value. The week starts on Monday, and the first week of a year contains January 4 of that year.

For details, see the [**ISO week date**](https://en.wikipedia.org/wiki/ISO_week_date) definition.

**Syntax**

```sql
WEEK(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                    |
| ------------ | --------------------- | -------------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function uses to return the week number. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-28 13:37:37.128`.

```sql
SELECT WEEK(CURRENT_TIMESTAMP());
```

*Output*: `9`

## YEAR

Extracts the year portion of a timestamp or date as an integer.

**Syntax**

```sql
YEAR(time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                              |
| ------------ | --------------------- | -------------------------------------------------------------------------------------------- |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function uses to extract the year. |

**Example**

This example uses `CURRENT_TIMESTAMP` set to `2023-02-28 13:37:37.128`.

```sql
SELECT YEAR(CURRENT_TIMESTAMP());
```

*Output*: `2023`

# Advanced Date and Time Functions

## DATEADD

This function adds a specified number value (as a signed integer) to a specified `datepart` of an input date value, and then returns that modified value.

The data type of the returned value for this function is dynamic. The return type depends on the argument supplied for `date`. If the value for `date` is a string literal `date`, `DATEADD` returns a `datetime` value. If another valid input data type is supplied for `date`, `DATEADD` returns the same data type. `DATEADD` raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part.

**Syntax**

```sql
DATEADD(datepart, number, date)
```

| **Argument** | **Data** **Type**           | **Description**                                                                                                                                                                                                                                                                                           |
| ------------ | --------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `datepart`   | `CHAR`                      | The part of the date to which DATEADD adds an integer number. See the Datepart Values table for a list of supported arguments.                                                                                                                                                                            |
| `number`     | `INT`                       | An expression that can resolve to an `INT` that `DATEADD` adds to the specified `datepart` value of `date`. `DATEADD` accepts user-defined variable values for number. `DATEADD` truncates a specified `number` value that has a decimal fraction. In this situation, it does not round the number value. |
| `date`       | `TIME`, `TIMESTAMP`, `DATE` | A column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a date or timestamp. Use four-digit years to avoid ambiguity issues.                                                                                                                    |

| ***datepart*** | **Abbreviations** |
| -------------- | ----------------- |
| `year`         | yy, yyyy          |
| `quarter`      | qq, q             |
| `month`        | mm, m             |
| `dayofyear`    | dy, y             |
| `day`          | dd, d             |
| `week`         | wk, ww            |
| `weekday`      | dw, w             |
| `hour`         | hh                |
| `minute`       | mi, n             |
| `second`       | ss, s             |
| `millisecond`  | ms                |
| `microsecond`  | mcs               |
| `nanosecond`   | ns                |

> **Note**
>
> `DATEADD` does not accept user-defined variable equivalents for the `datepart` arguments.

**Examples**

This example shows each result from incrementing different `datepart` values by 1.

```sql
SELECT 'year', DATEADD(year,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'quarter', DATEADD(quarter,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'month', DATEADD(month,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'dayofyear', DATEADD(dayofyear,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'day', DATEADD(day,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'week', DATEADD(week,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'weekday', DATEADD(weekday,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'hour', DATEADD(hour,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'minute', DATEADD(minute,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'second', DATEADD(second,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'millisecond', DATEADD(millisecond,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'microsecond', DATEADD(microsecond,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'nanosecond', DATEADD(nanosecond,1, '2007-01-01 13:10:10.1111111');
```

*Output*

```Text
week                                         2007-01-08 13:10:10.111111100
millisecond                                  2007-01-01 13:10:10.112111100
second                                       2007-01-01 13:10:11.111111100
day                                          2007-01-02 13:10:10.111111100
minute                                       2007-01-01 13:11:10.111111100
dayofyear                                    2007-01-02 13:10:10.111111100
quarter                                      2007-04-01 13:10:10.111111100
month                                        2007-02-01 13:10:10.111111100
hour                                         2007-01-01 14:10:10.111111100
weekday                                      2007-01-02 13:10:10.111111100
year                                         2008-01-01 13:10:10.111111100
nanosecond                                   2007-01-01 13:10:10.111111101
microsecond                                  2007-01-01 13:10:10.111112100

Fetched 13 rows
```

In this example, each statement increments `datepart` by a number large enough to increment the next higher unit of date or time.

```sql
SELECT 'quarter', DATEADD(quarter,4,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'month', DATEADD(month,13,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'dayofyear', DATEADD(dayofyear,365,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'day', DATEADD(day,365,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'week', DATEADD(week,5,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'weekday', DATEADD(weekday,31,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'hour', DATEADD(hour,23,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'minute', DATEADD(minute,59,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'second', DATEADD(second,59,'2007-01-01 01:01:01.1111111');
```

*Output*

```none
day                                          2008-01-01 01:01:01.111111100
weekday                                      2007-02-01 01:01:01.111111100
second                                       2007-01-01 01:02:00.111111100
month                                        2008-02-01 01:01:01.111111100
quarter                                      2008-01-01 01:01:01.111111100
dayofyear                                    2008-01-01 01:01:01.111111100
week                                         2007-02-05 01:01:01.111111100
hour                                         2007-01-02 00:01:01.111111100
minute                                       2007-01-01 02:00:01.111111100

Fetched 9 rows
```

## DATEDIFF

This function returns an `INT` representing the difference between two date or time values, in a specified date or time unit.

If only a time value is assigned to a date data type variable, `DATEDIFF` sets the value of the missing `datepart` to the default value: `1900-01-01`. If only a date value is assigned to a variable of a time or date data type, `DATEDIFF` sets the value of the missing time part to the default value: `00:00:00`. If either `startdate` or `enddate` have only a time part and the other only a `datepart`, `DATEDIFF` sets the missing time and `datepart` to the default values.

If `startdate` and `enddate` have different date data types, and one has more time parts or fractional seconds precision than the other, `DATEDIFF` sets the missing parts of the other to 0.

**Syntax**

```sql
DATEDIFF(datepart, startdate, enddate)
```

| **Argument** | **Data** **Type**           | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| ------------ | --------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `datepart`   | `CHAR`                      | The units in which the function returns the difference between the `startdate` and `enddate`. Commonly used `datepart` units include `month` or `second`. The `datepart` value cannot be specified in a variable, or in a quoted string like `'month'`. If `datepart` has a DATE value, and `startdate` and `enddate` are both assigned a TIME value, then the function returns `0`. See the Datepart Values table for a list of supported arguments. |
| `startdate`  | `TIME`, `TIMESTAMP`, `DATE` | A starting date or time value to determine the difference from the `enddate` value. If only a time value is assigned, `DATEDIFF` sets the value of the missing `datepart` to the default value: `1900-01-01`                                                                                                                                                                                                                                          |
| `enddate`    | `TIME`, `TIMESTAMP`, `DATE` | An ending date or time value to determine the difference from the `startdate` value. If only a time value is assigned, `DATEDIFF` sets the value of the missing `datepart` to the default value: `1900-01-01`                                                                                                                                                                                                                                         |

| ***datepart*** | **Abbreviations** |
| -------------- | ----------------- |
| `year`         | yy, yyyy          |
| `quarter`      | qq, q             |
| `month`        | mm, m             |
| `dayofyear`    | dy, y             |
| `day`          | dd, d             |
| `week`         | wk, ww            |
| `weekday`      | dw, w             |
| `hour`         | hh                |
| `minute`       | mi, n             |
| `second`       | ss, s             |
| `millisecond`  | ms                |
| `microsecond`  | mcs               |
| `nanosecond`   | ns                |

> **Note**
>
> For `millisecond`, the maximum difference between `startdate` and `enddate` is 24 days, 20 hours, 31 minutes, and 23.647 seconds.
>
> For `second`, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds.

> **Note**
>
> Each specific `datepart` name and abbreviations for that `datepart` name return the same value.

**Example**

The example shows how `DATEDIFF` calculates the difference between the two values based on the various `datepart` values.

```sql
SELECT 'year', DATEDIFF(year, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'quarter', DATEDIFF(quarter, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'month', DATEDIFF(month, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'dayofyear', DATEDIFF(dayofyear, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'day', DATEDIFF(day, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'week', DATEDIFF(week, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'hour', DATEDIFF(hour, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'minute', DATEDIFF(minute, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'second', DATEDIFF(second, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
```

*Output*

```none
day	        580
quarter	    7
year	    2
dayofyear	580
month	    20
hour	    13897
week	    83
minute      833761
second	    50025601
```

# Related Links

[**Time Zone Functions**](/guides/sql-reference/time-zone-functions)

[**Formatting Functions**](/guides/sql-reference/formatting-functions)