EXTRACT()

Description of extract_datetime.eps follows

Syntax:

EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)

  • EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression
  • expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
    • YEAR or MONTH or DAY from DATETIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH OR INTERVAL DAY TO SECOND FOR DAY.
    • IF EXTRACT needs HOURMINUTE, or SECOND is requestedm DATE Cannot be used to exrract from.
  •  VARCHAR2 is returned from EXTRACTS from TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation
  • All other extracts return integers.

Example, return the year.

--- RETURN THE YEAR FROM AN EXPRESSION
SELECT EXTRACT(YEAR FROM DATE '2023-11-9') AS THE_YEAR
FROM DUAL;

Extract Month

--- EXTRACT MONTH
SELECT EXTRACT(MONTH FROM MYDATE) AS THE_MONTH
FROM MYTESTTABLE;

The Day

--- EXTRACT DAY
SELECT EXTRACT(DAY FROM MYDATE) AS THE_DAY
FROM MYTESTTABLE;

To extract hour, minute and second, the timestand will have to be used when dates are inserted.

For example,

--- EXTRACT SECOND
SELECT EXTRACT(SECOND FROM MYDATE) AS THE_DAY
FROM MYTESTTABLE;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top