Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

EXTRACT(HOUR FROM Create_Date) AS Hour

467579Aug 24 2006 — edited Nov 22 2006
I'm using Oracle 9i 9.2.0.7. I'm attempting to extract the hours, minutes, seconds from a DATE column in a table. It works fine extracting the year, month, and day but not the elements I need. I'm pasting my test SQL and a few of the results below for clarity. I've commented out the extract of hour at this point.

SELECT TO_CHAR(Create_Date,'YYYY-MM-DD HH24:MI:SS') AS Create_Date
,EXTRACT(YEAR FROM Create_Date) AS Year
,EXTRACT(MONTH FROM Create_Date) AS Month
,EXTRACT(DAY FROM Create_Date) AS Day
-- ,EXTRACT(HOUR FROM Create_Date) AS Hour
FROM Test.Trans;

CREATE_DATE YEAR MONTH DAY
------------------- ---------- ---------- ----------
2005-01-12 10:43:06 2005 1 12
2005-01-12 10:43:21 2005 1 12
2005-01-12 10:43:35 2005 1 12
2005-01-12 10:43:49 2005 1 12
2005-01-12 10:46:48 2005 1 12

As soon as I uncomment the line where I try to extract the hour I get the following error message and I fail to understand why. The column in the table is of type DATE and from my Oracle novice perspective it looks to me like they have a valid hour that could be extracted. Could one of you help shed some light on this? What am I missing?

ORA-30076: invalid extract field for extract source
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 20 2006
Added on Aug 24 2006
9 comments
34,771 views