Example: Returning the Year From the Current Date
The following example returns the year, as an integer, from the current date.
SELECT EXTRACT (YEAR FROM CURRENT_DATE);
Example: Adding 90 days to PurchaseDate
Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.
SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;
Example: Returning an Integer
The following returns 12 as an integer.
SELECT EXTRACT (DAY FROM '1996-12-12');
Example: Character Literal Does Not Evaluate to a Valid Date
This example returns an error because the character literal does not evaluate to a valid date.
SELECT EXTRACT (DAY FROM '1996-02-30');
Example: Character String Literal Does Not Match the ANSI SQL:2011 Date Format
The following returns an error because the character string literal does not match the ANSI SQL:2011 date format.
SELECT EXTRACT (DAY FROM '96-02-15');
If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.
Example: Non-ANSI DateTime Definitions
The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.
SELECT EXTRACT (MINUTE FROM '23:59:17.3');
Example: Returning the Hour From the Current Time
This example returns the hour, as an integer, from the current time.
SELECT EXTRACT (HOUR FROM CURRENT_TIME);
Current time is retrieved as the system value TIME, to the indicated precision.
Example: Returning the Seconds as DECIMAL
The following example returns the seconds as DECIMAL(8,2). This is based on the fractional seconds precision of 2 for CURRENT_TIME.
SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));