Cool SQL function: EXTRACT

I have always used the SQL function TO_CHAR to extract parts of a date, like the month or the year. But, there is another way to extract parts of a date: the function EXTRACT. Here is an example:

Using TO_CHAR:

HR@XE> select
  2  to_char(to_date('05-18-2005','mm-dd-yyyy'),'mm') mm
  3  from dual
  4  /

MM
--
05

Of course, the datatype of the returned value is character.

Using EXTRACT:

HR@XE> select
  2  extract(month from to_date('05-18-2005','mm-dd-yyyy')) mm
  3  from dual
  4  /

        MM
----------
         5

Here, the datatype of the returned value is number.

The syntax of the EXTRACT function is:

EXTRACT( { { YEAR
           | MONTH
           | DAY
           | HOUR
           | MINUTE
           | SECOND
           }
         | { TIMEZONE_HOUR
           | TIMEZONE_MINUTE
           }
         | { TIMEZONE_REGION
           | TIMEZONE_ABBR
           }
         }
         FROM { datetime_value_expression
              | interval_value_expression
              }
       )

EXTRACT is supported in Oracle DB versions 9i and 10g. Moreover, even though I could not find a mention of this function in the 8i documentation, I did test the above query in 8.1.7 and it worked. Does this make EXTRACT an undocumented function in 8i? or does it mean that I did not look hard enough in the 8i documentation?

Possibly related:

Tagged , | Post a Comment