Oracle :Compare dates only by the Hour

Question

We have a table which has a DATE column d.

I'd like to get all the rows where the d column is greater / lower than some value, regardless of the date.

For example

|       d          |
-------------------
|2009/11/1 15:55:23|
--------------------
|2009/11/2 15:55:23|
--------------------
|2009/11/3 15:55:23|
--------------------
|2009/11/3 17:55:23|
--------------------

For example, If I want all the records marked after 5 PM:

select d 
 from my_table 
where extract( hour from d ) > TO_DATE ('17:00:00','HH24:MI:SS') 

This should return only one record

|2009/11/3 17:55:23|

I don't know if this is the best way to do it, but I get an error on the extract function:

ORA-30076: invalid extract field for extract source 
Cause: The extract source does not contain the specified extract field.

Is there a better way to do this? Whats up with that error? extract only available for sysdate, as in all examples i've found?

Thanks in advance

Answer 1

Don't have an Oracle database to test with at the moment but I think the following should work:

SELECT *
  FROM MY_TABLE t
  WHERE EXTRACT(HOUR FROM CAST(t.D AS TIMESTAMP)) > 16;

But I don't understand why the CAST would be needed as this page says that HOUR is a valid field in a DATE.

Share and enjoy.