DISQUS

François · 3 years ago

For an unknown reason you cannot extract Hour, minute or second from a date. It works only on Timestamp.
Francois

Laurent Schneider · 3 years ago

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

François · 3 years ago

You can also get this information with the To_Char() function.

SELECT to_char(SYSTIMESTAMP,'FF') FROM dual

Eddie Awad · 3 years ago

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.

Rob Vollman · 3 years ago

Yes, I just used this the other day. I wrapped it in a DECODE to get a text representation of the month.

Glenn · 2 years ago

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!

Gary Menchen · 1 year ago

You can cast a date to a timestamp and then extract the hour.

Eddie Awad · 1 year ago

@Gary: Good idea. Here is an example

<pre>
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 dual
</pre>
Thanks.