SQL > SQL Date Functions > 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:

EXTRACT (component_part FROM expression)

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,

SELECT EXTRACT(YEAR FROM '2022-01-01');

produces the following result:

2022

Example 2

The SQL statement,

SELECT EXTRACT(MONTH FROM '2022-01-01 15:12:35');

produces the following result:

1

Example 3

The SQL statement,

SELECT EXTRACT(HOUR, '2022-01-01 15:12:35');

produces the following result:

15

List of SQL Date Functions

Function NameDescription
 DATEADD  Adds an interval to a date value in SQL Server. 
 DATEDIFF  Calculates the difference between two dates in MySQL and SQL Server. 
 DATEPART  Extracts a specific part of a date/time value in SQL Server. 
 GETDATE  Retrieves database time in SQL Server. 
 SYSDATE  Retrieves database time in Oracle and MySQL. 
 EXTRACT  Retrieves a certain component of a date or timestamp value. 

Next: Data Definition Language (DDL)

This page was last updated on October 12, 2024.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact