Categories:

Date & time functions

EXTRACT¶

Extracts the specified date or time part from a date, time, or timestamp.

Tip

To extract the date from a timestamp, use the TO_DATE function.

Alternatives:

DATE_PART , HOUR / MINUTE / SECOND , YEAR* / DAY* / WEEK* / MONTH / QUARTER

Syntax¶

EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )
Copy
EXTRACT( <date_or_time_part> , <date_or_timestamp_expr> )
Copy

Arguments¶

date_or_time_part

The unit of time. Must be one of the values listed in Supported date and time parts (e.g. month). The value can be a string literal or can be unquoted (e.g. 'month' or month).

  • When date_or_time_part is week (or any of its variations), the output is controlled by the WEEK_START session parameter.

  • When date_or_time_part is dayofweek or yearofweek (or any of their variations), the output is controlled by the WEEK_OF_YEAR_POLICY and WEEK_START session parameters.

For more details, including examples, see Calendar weeks and weekdays.

date_or_timestamp_expr

A date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp.

Returns¶

Returns a value of NUMBER data type.

Usage notes¶

Currently, when date_or_timestamp_expr is a DATE value, the following date_or_time_part values are not supported:

  • epoch_millisecond

  • epoch_microsecond

  • epoch_nanosecond

Other date and time parts (including epoch_second) are supported.

Examples¶

Specify the year part to extract the year from a timestamp:

SELECT EXTRACT(year FROM TO_TIMESTAMP('2024-04-10T23:39:20.123-07:00')) AS YEAR;
Copy
+------+
| YEAR |
|------|
| 2024 |
+------+

Use EXTRACT with the DECODE function and the dayofweek part to return the full name of the current day of the week:

SELECT DECODE(EXTRACT(dayofweek FROM SYSTIMESTAMP()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday') AS DAYOFWEEK;
Copy
+-----------+
| DAYOFWEEK |
|-----------|
| Thursday  |
+-----------+

Note

The output depends on the value returned by the SYSTIMESTAMP function when you run the query. Also, you can use the DAYNAME function to extract the three-letter day-of-week name from the specified date or timestamp.

Language: English