SQL EXTRACT (MySQL Function)

The SQL EXTRACT() function is used to get a value (year, month, day, hour, minute, etc.) from a date/time expression. It’s supported by MySQL and Oracle, but the syntax is different. In this post we’ll discuss it’s syntax and example in MySQL.

SQL EXTRACT (MySQL Function) Syntax

EXTRACT(unit FROM date)

date is a valid date expression.
unit can be one of the following:

Unit Value Example (suppose the date is ’2011-11-07 14:16:36.019473′)
MICROSECOND 19473
SECOND 36
MINUTE 16
HOUR 14
DAY 7
WEEK 45
MONTH 11
QUARTER 4
YEAR 2011
SECOND_MICROSECOND 36019473
MINUTE_MICROSECOND 1636019473
MINUTE_SECOND 1636
HOUR_MICROSECOND 141636019473
HOUR_SECOND 141636
HOUR_MINUTE 1416
DAY_MICROSECOND 7141636019473
DAY_SECOND 7141636
DAY_MINUTE 71416
DAY_HOUR 714
YEAR_MONTH 201111

SQL EXTRACT (MySQL Function) Example

SELECT EXTRACT(YEAR  FROM '2010-08-07') AS StartYear, 
          EXTRACT(MONTH FROM '2010-08-07') AS StartMonth, 
          EXTRACT(DAY FROM '2010-08-07') AS StartDay 

The result will look like:

StartYear StartMonth StartDay
2010 8 7

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>