## 8.4. Date and Time Functions

### 8.4.1. `DATEADD()`

Adds or subtracts datetime units from a datetime value

Result type`DATE`, `TIME` or `TIMESTAMP`

Syntax

```   |`DATEADD (<args>)`
|` `
|`<args> ::=`
|`    <amount> <unit> TO <datetime>`
|`  | <unit>, <amount>, <datetime>`
|` `
|`<amount> ::= an integer expression (negative to subtract)`
|`<unit> ::=`
|`    YEAR | MONTH | WEEK | DAY`
|`  | HOUR | MINUTE | SECOND | MILLISECOND`
|`<datetime> ::= a DATE, TIME or TIMESTAMP expression`
```

Table 8.56`DATEADD` Function Parameters
ParameterDescription

amount

An integer expression of the `SMALLINT`, `INTEGER` or `BIGINT` type. For unit `MILLISECOND`, the type is `NUMERIC(18, 1)`. A negative value is subtracted.

unit

Date/time unit

datetime

An expression of the `DATE`, `TIME` or `TIMESTAMP` type

Adds the specified number of years, months, weeks, days, hours, minutes, seconds or milliseconds to a date/time value.

• The result type is determined by the third argument.

• With `TIMESTAMP` and `DATE` arguments, all units can be used.

• With `TIME` arguments, only `HOUR`, `MINUTE`, `SECOND` and `MILLISECOND` can be used.

#### 8.4.1.1. Examples of `DATEADD`

```  |`dateadd (28 day to current_date)`
|`dateadd (-6 hour to current_time)`
|`dateadd (month, 9, DateOfConception)`
|`dateadd (-38 week to DateOfBirth)`
|`dateadd (minute, 90, cast('now' as time))`
|`dateadd (? year to date '11-Sep-1973')`
```
```  |`select`
|`  cast(dateadd(-1 * extract(millisecond from ts) millisecond to ts) as varchar(30)) as t,`
|`  extract(millisecond from ts) as ms`
|`from (`
|`  select timestamp '2014-06-09 13:50:17.4971' as ts`
|`  from rdb\$database`
|`) a`
```
```  |`T                        MS`
|`------------------------ ------`
|`2014-06-09 13:50:17.0000  497.1`
```

### 8.4.2. `DATEDIFF()`

Difference between two datetime values in a datetime unit

Result type`BIGINT`, or `NUMERIC(18,1)` for `MILLISECOND`

Syntax

```   |`DATEDIFF (<args>)`
|` `
|`<args> ::=`
|`    <unit> FROM <moment1> TO <moment2>`
|`  | <unit>, <moment1>, <moment2>`
|` `
|`<unit> ::=`
|`    YEAR | MONTH | WEEK | DAY`
|`  | HOUR | MINUTE | SECOND | MILLISECOND`
|`<momentN> ::= a DATE, TIME or TIMESTAMP expression`
```

Table 8.57`DATEDIFF` Function Parameters
ParameterDescription

unit

Date/time unit

moment1

An expression of the `DATE`, `TIME` or `TIMESTAMP` type

moment2

An expression of the `DATE`, `TIME` or `TIMESTAMP` type

Returns the number of years, months, weeks, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.

• `DATE` and `TIMESTAMP` arguments can be combined. No other mixes are allowed.

• With `TIMESTAMP` and `DATE` arguments, all units can be used.

• With `TIME` arguments, only `HOUR`, `MINUTE`, `SECOND` and `MILLISECOND` can be used.

Computation
• `DATEDIFF` doesn’t look at any smaller units than the one specified in the first argument. As a result,

• `datediff (year, date '1-Jan-2009', date '31-Dec-2009')` returns 0, but

• `datediff (year, date '31-Dec-2009', date '1-Jan-2010')` returns 1

• It does, however, look at all the bigger units. So:

• `datediff (day, date '26-Jun-1908', date '11-Sep-1973')` returns 23818

• A negative result value indicates that moment2 lies before moment1.

#### 8.4.2.1. `DATEDIFF` Examples

```  |`datediff (hour from current_timestamp to timestamp '12-Jun-2059 06:00')`
|`datediff (minute from time '0:00' to current_time)`
|`datediff (month, current_date, date '1-1-1900')`
|`datediff (day from current_date to cast(? as date))`
```

### 8.4.3. `EXTRACT()`

Extracts a datetime unit from a datetime value

Result type`SMALLINT` or `NUMERIC`

Syntax

```  |`EXTRACT (<part> FROM <datetime>)`
|` `
|`<part> ::=`
|`    YEAR | MONTH | QUARTER | WEEK`
|`  | DAY | WEEKDAY | YEARDAY`
|`  | HOUR | MINUTE | SECOND | MILLISECOND`
|`  | TIMEZONE_HOUR | TIMEZONE_MINUTE`
|`<datetime> ::= a DATE, TIME or TIMESTAMP expression`
```

Table 8.58`EXTRACT` Function Parameters
ParameterDescription

part

Date/time unit

datetime

An expression of the `DATE`, `TIME` or `TIMESTAMP` type

Extracts and returns an element from a `DATE`, `TIME` or `TIMESTAMP` expression.

#### 8.4.3.1. Returned Data Types and Ranges

The returned data types and possible ranges are shown in the table below. If you try to extract a part that isn’t present in the date/time argument (e.g. `SECOND` from a `DATE` or `YEAR` from a `TIME`), an error occurs.

Table 8.59Types and ranges of `EXTRACT` results
PartTypeRangeComment

`YEAR`

`SMALLINT`

1-9999

`MONTH`

`SMALLINT`

1-12

`QUARTER`

`SMALLINT`

1-4

`WEEK`

`SMALLINT`

1-53

`DAY`

`SMALLINT`

1-31

`WEEKDAY`

`SMALLINT`

0-6

0 = Sunday

`YEARDAY`

`SMALLINT`

0-365

0 = January 1

`HOUR`

`SMALLINT`

0-23

`MINUTE`

`SMALLINT`

0-59

`SECOND`

`NUMERIC(9,4)`

0.0000-59.9999

includes millisecond as fraction

`MILLISECOND`

`NUMERIC(9,1)`

0.0-999.9

`TIMEZONE_HOUR`

`SMALLINT`

-23 - +23

`TIMEZONE_MINUTE`

`SMALLINT`

-59 - +59

##### 8.4.3.1.1. `MILLISECOND`

Extracts the millisecond value from a `TIME` or `TIMESTAMP`. The data type returned is `NUMERIC(9,1)`.

Note

If you extract the millisecond from Section 12.4, “`CURRENT_TIME`, be aware that this variable defaults to seconds precision, so the result will always be 0. Extract from `CURRENT_TIME(3)` or Section 12.5, “`CURRENT_TIMESTAMP` to get milliseconds precision.

##### 8.4.3.1.2. `WEEK`

Extracts the ISO-8601 week number from a `DATE` or `TIMESTAMP`. ISO-8601 weeks start on a Monday and always have the full seven days. Week 1 is the first week that has a majority (at least 4) of its days in the new year. The first 1-3 days of the year may belong to the last week (52 or 53) of the previous year. Likewise, a year’s final 1-3 days may belong to week 1 of the following year.

Caution

Be careful when combining `WEEK` and `YEAR` results. For instance, 30 December 2008 lies in week 1 of 2009, so `extract(week from date '30 Dec 2008')` returns 1. However, extracting `YEAR` always gives the calendar year, which is 2008. In this case, `WEEK` and `YEAR` are at odds with each other. The same happens when the first days of January belong to the last week of the previous year.

Please also notice that `WEEKDAY` is not ISO-8601 compliant: it returns 0 for Sunday, whereas ISO-8601 specifies 7.

### 8.4.4. `FIRST_DAY()`

Returns the first day of a time period containing a datetime value

Result Type`DATE`, `TIMESTAMP` (with or without time zone)

Syntax

```  |`FIRST_DAY(OF <period> FROM date_or_timestamp)`
|` `
|`<period> ::= YEAR | MONTH | QUARTER | WEEK`
```

Table 8.60`FIRST_DAY` Function Parameters
ParameterDescription

date_or_timestamp

Expression of type `DATE`, `TIMESTAMP WITHOUT TIME ZONE` or `TIMESTAMP WITH TIME ZONE`

`FIRST_DAY` returns a date or timestamp (same as the type of date_or_timestamp) with the first day of the year, month or week of a given date or timestamp value.

Note
• The first day of the week is considered as Sunday, following the same rules as for Section 8.4.3, “`EXTRACT()` with `WEEKDAY`.

• When a timestamp is passed, the return value preserves the time part.

#### 8.4.4.1. Examples of `FIRST_DAY`

```  |`select`
|`  first_day(of month from current_date),`
|`  first_day(of year from current_timestamp),`
|`  first_day(of week from date '2017-11-01'),`
|`  first_day(of quarter from date '2017-11-01')`
|`from rdb\$database;`
```

### 8.4.5. `LAST_DAY()`

Returns the last day of a time period containing a datetime value

Result Type`DATE`, `TIMESTAMP` (with or without time zone)

Syntax

```  |`LAST_DAY(OF <period> FROM date_or_timestamp)`
|` `
|`<period> ::= YEAR | MONTH | QUARTER | WEEK`
```

Table 8.61`LAST_DAY` Function Parameters
ParameterDescription

date_or_timestamp

Expression of type `DATE`, `TIMESTAMP WITHOUT TIME ZONE` or `TIMESTAMP WITH TIME ZONE`

`LAST_DAY` returns a date or timestamp (same as the type of date_or_timestamp) with the last day of the year, month or week of a given date or timestamp value.

Note
• The last day of the week is considered as Saturday, following the same rules as for Section 8.4.3, “`EXTRACT()` with `WEEKDAY`.

• When a timestamp is passed, the return value preserves the time part.

#### 8.4.5.1. Examples of `LAST_DAY`

```  |`select`
|`  last_day(of month from current_date),`
|`  last_day(of year from current_timestamp),`
|`  last_day(of week from date '2017-11-01'),`
|`  last_day(of quarter from date '2017-11-01')`
|`from rdb\$database;`
```