O'Reilly logo

SQL in a Nutshell, 2nd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required