SQL EXTRACT Function

The EXTRACT function is used to retrieve a specific component of a date or a timestamp. This function is available in several databases such as MySQL, Oracle, DB2, PostgreSQL, and Google BigQuery. Note that this function is not available in SQL Server and Hive.

Syntax

The syntax for the EXTRACT function is as follows:

where the data type of <expression> is some type of date, time, or datetime. <number> is an integer (can be positive or negative). Common <component_datepart> can be one of the following:

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND

Different databases may offer other component_part types. For example, in Google BigQuery, you can specify DAYOFWEEK to find out the day of the week. This is not available in Oracle or MySQL.

Please note that component_part needs to align with the data type of expression. For example, if you try to extract the hour from a DATE expression, you will get an error.

Examples

Below we show several examples of the output from applying the EXTRACT function. Please note that we assume that expression is already in the correct data type (either DATE, DATETIME, or TIMESTAMP). In addition, in Oracle you will need to add "FROM DUAL" at the end of the statement.

Example 1

The SQL statement,

produces the following result:

Example 2

The SQL statement,

produces the following result:

Example 3

The SQL statement,

produces the following result:

List of SQL Date Functions