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:
- Bye Bye 2006, Welcome 2007
- Cool SQL Analytic Function: RATIO_TO_REPORT
- Cool SQL Analytic Function: NTILE
- Cool Undocumented SQL Function: REVERSE
- Oracle REF CURSOR and ColdFusion
Tagged function, sql | Post a Comment


















For an unknown reason you cannot extract Hour, minute or second from a date. It works only on Timestamp.
December 28th, 2005, at 5:36 am #Francois
interesting is extracting nanoseconds from timestamp :
SQL> create table t as select timestamp ‘2000-01-01 00:00:42.123456789′ t from dual;
Table created.
SQL> select mod(extract(second from t),1)*1000000000 ns from t;
NS
123456789
December 28th, 2005, at 6:36 am #You can also get this information with the To_Char() function.
SELECT to_char(SYSTIMESTAMP,’FF’) FROM dual
December 28th, 2005, at 7:12 am #you cannot extract Hour, minute or second from a date
Yes, the documentation says: The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value.
I’m not sure exactly why you cannot get to the hour/minute/second from a DATE using EXTRACT, since a DATE datatype contains a time portion as well (but not fractional seconds).
It’ll be interesting to know what the reason behind this limitation is.
December 28th, 2005, at 9:57 am #Yes, I just used this the other day. I wrapped it in a DECODE to get a text representation of the month.
December 29th, 2005, at 11:35 am #Well, this isn’t so useful if you want the name of the day instead of a number. Say, “MON” instead of 2. Back to TO_CHAR!
September 18th, 2007, at 7:55 pm #You can cast a date to a timestamp and then extract the hour.
March 19th, 2008, at 1:41 pm #@Gary: Good idea. Here is an example
select extract(hour from cast(sysdate as timestamp)) hh, extract(minute from cast(sysdate as timestamp)) mi, extract(second from cast(sysdate as timestamp))ss from dualThanks.
March 20th, 2008, at 11:30 am #