EXTRACT

The EXTRACT function extracts a single field from a TIMESTAMP or INTERVAL value.

Syntax

EXTRACT (<field_name> FROM <value>)

The field_name argument is SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, or YEAR. The value argument is of type TIMESTAMP or INTERVAL.

Remarks

•

The data type of the output is an exact NUMERIC with a precision equal to the leading precision of value and a scale of zero. When the field name is a SECOND, the precision is equal to the sum of the leading precision and the seconds precision of value and a scale equal to the SECOND’s precision.

•

When value is a negative INTERVAL, the result is a negative value.

•

If value is NULL, the result is also NULL.

EXTRACT (With INTERVAL)

SELECT orders.OrderDate,

EXTRACT (SECOND FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),

EXTRACT (MINUTE FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),

EXTRACT (HOUR FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),

EXTRACT (DAY FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),

EXTRACT (MONTH FROM INTERVAL '500' MONTH(3))

EXTRACT (YEAR FROM INTERVAL '499-11' YEAR(3) TO MONTH),

FROM /shared/examples/ds_orders/orders

Results of the EXTRACT functions:

EXTRACT (SECOND FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 19.124

EXTRACT (MINUTE FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 51

EXTRACT (HOUR FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 23

EXTRACT (DAY FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 2

EXTRACT (MONTH FROM INTERVAL '500' MONTH(3)) = 500

EXTRACT (YEAR FROM INTERVAL '499-11' YEAR(3) TO MONTH) = 499

EXTRACT (Without INTERVAL)

SELECT orders.ShipName, 

	orders.OrderID, 

	orders.OrderDate, 

EXTRACT (DAY FROM orders.OrderDate) "day", 

EXTRACT (MONTH FROM orders.OrderDate) "month" 

EXTRACT (QUARTER FROM orders.OrderDate) "quarter"

FROM /shared/examples/ds_orders/orders orders