Categories:: Date & Time Functions

EXTRACT¶

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

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.

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.