Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
29,167 views