How to use EXTRACT function with date and time columns

Using Extract function with any date field is easy. Let’s try this out with system date and system time commands. To see the current date we use

SELECT DATE;

This would give output as “11/06/2007″. Suppose you want to EXTRACT the month from the date then use this query

SELECT EXTRACT (MONTH FROM date);

This would give “11” as the output. Similarly for YEAR and DAY use the following commands

SELECT EXTRACT (YEAR FROM date);
SELECT EXTRACT (DAY FROM date);


But for HOUR MINUTE and SECONDS extractions, we can not use date command, we have to use time instead. To see time use the following command;

SELECT TIME;

This would return “15:59:19″ as the output and to extract HOUR MINUTE and SECONDS from this, use the following commands

SELECT EXTRACT (MINUTE FROM time);
SELECT EXTRACT (HOUR FROM time) ;
SELECT EXTRACT (SECOND FROM time) ;

If you use anything else than the expected inputs for the EXTRACT function you would end up getting 3707:  Syntax error.

4 thoughts on “How to use EXTRACT function with date and time columns

  1. Material that you are providing is very easy to learn. But it is much better than this , if you ll give the full material of this at ONE LINK.

    Thank you
    Suresh.

  2. Hi. Can anyone tell me how to extract time from timestamp?
    For Example
    timestamp=’2008-03-28 14:11:04′
    i want time ’14:11:04′ from timestamp through query.
    Thanks in advance.

  3. Hi Vaideesh,

    If you need only time, you can probably try the following;

    SELECT time;

    To answer your question you should do something like this.

    SELECT CAST(VaideeshTime as time(0)) from timestamp;

    VaideeshTime
    ——–
    14:11:04

    Hope that helps.

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>