EXTRACT(HOUR FROM Create_Date) AS Hour
467579Aug 24 2006 — edited Nov 22 2006I'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