Alex K. created TEIID-2522:
------------------------------
Summary: Let Extract(<Oracle Date field>) extract time components
Key: TEIID-2522
URL:
https://issues.jboss.org/browse/TEIID-2522
Project: Teiid
Issue Type: Feature Request
Reporter: Alex K.
Assignee: Steven Hawkins
According to the Oracle's docs:
"The DATE datatype stores point-in-time values (dates and times) in a table. The DATE
datatype stores the year (including the century), the month, the day, the hours, the
minutes, and the seconds (after midnight)."
I expect to be able to extract the time parts from a date field, but when I am trying to
extract the hour from an oracle's date field like:
{code:sql}
SELECT EXTRACT(HOUR FROM a.modifieddate)
FROM oracle_db.salestaxrate a
WHERE a.salestaxrateid = 1
{code}
and get a TEIID30504 exception. When I look deeper in Teiid's log, i see the
following Oracle's exception:
Caused by: java.sql.SQLException: Remote java.sql.SQLException: ORA-30076: invalid extract
field for extract source
That is an odd Oracle limitation -
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm
"If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of
datatype TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL
DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE
datatype, which has no time fields."
*please enable extracting of time components from oracle's Date Fields* despite the
Oracle's limitation
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see:
http://www.atlassian.com/software/jira