EXTRACT (Datetime) Function - Oracle to MySQL Migration

In Oracle and MySQL, the EXTRACT function returns the value of a specified datetime field (year, month, day etc.) from a datetime.

Oracle:

  -- Get the day of December 24, 2025
  SELECT EXTRACT(DAY FROM DATE '2025-12-24') FROM dual;
  /* 24 */

MySQL:

  -- Get the day of December 24, 2025
  SELECT EXTRACT(DAY FROM DATE '2025-12-24');
  /* 24 */

Summary information:

Syntax	EXTRACT(datetime_unit FROM datetime_expression)
Datetime Units	YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
Time Units	Although Oracle DATE contains time part, HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- SYSDATE returns the current date and time (!)
  SELECT SYSDATE FROM dual;
  /* 2025-12-24 11:31:29 */
 
  -- But you cannot use HOUR for EXTRACT from SYSDATE
  SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;
  /* ORA-30076: invalid extract field for extract source */

For more information, see Oracle to MySQL Migration.