> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Date and Time Functions

## 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 SQL theme={null}
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 SQL theme={null}
SELECT ADD_MONTHS('2022-11-15 04:18:00',2);
```

*Output*: `2023-01-15`

### CENTURY

Returns the number of centuries.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
CURRENT_DATE
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
CURRENT_TIME
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
CURRENT_TIMESTAMP
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
DATE_TRUNC(precision, time)
```

| **Argument** | **Data** **Type**     | **Description**                                                                                                                                                                                                                                                                                                               |
| ------------ | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `precision`  | `CHAR`                | The timestamp unit used to truncate the returned value. <br />For the `precision` input, acceptable values are:<br />`NANOSECOND`<br />`MICROSECOND`<br />`MILLISECOND`<br />`SECOND`<br />`MINUTE`<br />`HOUR`<br />`DAY`<br />`WEEK`<br />`MONTH`<br />`QUARTER`<br />`YEAR`<br />`DECADE`<br />`CENTURY`<br />`MILLENNIUM` |
| `time`       | `TIMESTAMP` or `DATE` | A time value, specified as a timestamp or date, which the function truncates to the specified precision. <br />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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT DATE_TRUNC('CENTURY', CURRENT_TIMESTAMP());
```

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

### DAY

Alias for [DAY\_OF\_MONTH](#day_of_month).

### 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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT DAY(CURRENT_TIMESTAMP());
```

*Output*: `3`

### DECADE

The decade is the year divided by 10.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
SELECT DECADE(CURRENT_DATE());
```

*Output*: `202`

### DOW

Alias for [DAY\_OF\_WEEK](#day_of_week).

### DOY

Alias for [DAY\_OF\_YEAR](#day_of_year).

### 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 SQL theme={null}
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. <br />If unspecified, the value defaults to 0. |

**Examples**

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

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT EPOCH(CURRENT_TIMESTAMP());
```

*Output*: `1672743901`

### EXTRACT

Extract a component from a timestamp or date.

**Syntax**

```sql SQL theme={null}
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. <br />For the `precision` input, acceptable values are:<br />`NANOSECOND`<br />`MICROSECOND`<br />`MILLISECOND`<br />`SECOND`<br />`MINUTE`<br />`HOUR`<br />`DAY`<br />`WEEK`<br />`MONTH`<br />`QUARTER`<br />`YEAR`<br />`DECADE`<br />`CENTURY`<br />`MILLENNIUM` |

**Example**

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

```sql SQL theme={null}
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP());
```

*Output*: `11.0`

### HOUR

Extracts the hour portion of a timestamp as an integer.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT ISODOW(CURRENT_TIMESTAMP());
```

*Output*: `2`

### ISDATE

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

**Syntax**

```sql SQL theme={null}
ISDATE(char)
```

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

**Example**

```sql SQL theme={null}
SELECT ISDATE('2023-01-03');
```

*Output*: `TRUE`

### MAKEDATETIME

Returns a timestamp consisting of the specified date and time.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT MILLISECOND(CURRENT_TIMESTAMP());
```

*Output*: `286`

### MINUTE

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

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
SELECT MINUTE(CURRENT_TIMESTAMP());
```

*Output*: `0`

### MONTH\_NAME

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

Syntax

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT MONTH(CURRENT_TIMESTAMP());
```

*Output*: `2`

### MSECS

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

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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. <br />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 SQL theme={null}
SELECT NEXT_DAY('2023-02-27', 'sat');
```

*Output*: `2023-03-04`

### NOW

Alias for [CURRENT\_TIMESTAMP](#current_timestamp).

### QUARTER

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

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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](#date_trunc). The function rounds up values to the specified precision.

**Syntax**

```sql SQL theme={null}
ROUND(time, precision)
```

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

**Example**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT SECOND(CURRENT_TIMESTAMP());
```

*Output*: `30`

### TIMESTAMP\_TO\_NANOS

Convert timestamp into nanoseconds after epoch as `BIGINT`.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
SELECT TIMESTAMP_TO_NANOS(CURRENT_TIMESTAMP());
```

*Output*: `1677619212373708904`

### TO\_TIMESTAMP

For information on using the TO\_TIMESTAMP conversion function, see the [TO\_TIMESTAMP](/formatting-functions#to_timestamp) page.

### USECS

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

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT WEEK(CURRENT_TIMESTAMP());
```

*Output*: `9`

### YEAR

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

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
DATEADD(datepart, number, date)
```

| **Argument** | **Data** **Type**           | **Description**                                                                                                                                                                                                                                                                                                       |
| ------------ | --------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `datepart`   | `CHAR`                      | The part of the date to which DATEADD adds an integer number. <br />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`. <br />`DATEADD` accepts user-defined variable values for number. <br />`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. <br />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                |

<Info>
  `DATEADD` does not accept user-defined variable equivalents for the `datepart` arguments.
</Info>

**Examples**

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

```sql SQL theme={null}
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 Text theme={null}
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 SQL theme={null}
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 Text theme={null}
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 SQL theme={null}
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`.<br />The `datepart` value cannot be specified in a variable, or in a quoted string like `'month'`.<br />If `datepart` has a DATE value, and `startdate` and `enddate` are both assigned a TIME value, then the function returns `0`.<br />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. <br />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. <br />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                |

<Info>
  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.
</Info>

<Info>
  Each specific `datepart` name and abbreviations for that `datepart` name return the same value.
</Info>

**Example**

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

```sql SQL theme={null}
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 Text theme={null}
day	        580
quarter	    7
year	    2
dayofyear	580
month	    20
hour	    13897
week	    83
minute      833761
second	    50025601
```

## Related Links

[Time Zone Functions](/time-zone-functions)

[Formatting Functions](/formatting-functions)