SQL in a Nutshell, 2nd Edition

Name

EXTRACT

Synopsis

The EXTRACT function is supported by Oracle, PostgreSQL, and MySQL. The other platforms support a separate command to accomplish the same functionality.

SQL2003 Syntax

EXTRACT( date_part FROM expression )

The SQL2003 scalar function for extracting parts from a date is EXTRACT. The SQL2003 EXTRACT function takes a date_part and an expression that evaluates to a date time value.

DB2

DB2 provides unique functions that provide the same behavior as EXTRACT. Those functions are DAY, DAYNAME, DAYOFWEEK, DAYOFWEEK_ISO, DAYOFYEAR, DAYS, HOUR, JULIAN_DAY, MICROSECOND, MIDNIGHT_SECONDS, MINUTE, MONTH, MONTHNAME, SECOND, TIME, WEEK, WEEK_ISO, and YEAR. Look to the list shown later in this chapter in Section 4.5.1 for examples on how to use these functions. As with Oracle, DB2 also offers a TO_CHAR function that can be used to format a string from a date value.

Tip

When extracting parts of a date value on DB2 into an integer, such as the year or minute, use the special functions provided by DB2 instead of the TO_CHAR function. Doing so will provide the highest performance, since a conversion from a result string into an integer won’t be required.

MySQL

MySQL’s implementation is extended somewhat beyond the ANSI standard. The ANSI standard does not have a provision for returning multiple fields from the same call to EXTRACT( ) (e.g., DAY_HOUR). The MySQL extensions try to accomplish what the combination DATE_TRUNC( ) and DATE_PART( ) do in PostgreSQL. MySQL supports the dateparts ...