I am trying a query by using to_date function using odbc connector stage(to connect to oracle) but it gives following error
"Unexpected procedure reference found"
The format of date fields seems to be different from what we have for other database stages like Oracle enterprise stage or odbc enterprise stage.
for e.g i am able to view data for the following query through odbc stage:
select to_date(sysdate,'yyyy-mm-dd') from dual;
This gives the date in the format defined.
but odbc connector displays date in a different format (with GMT). Is it because of this that we are not able convert the date fields by using to_date functions?
Does the odbc connector stage does not support to_date functions?
Please help
to_date functions in odbc connector stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
to_date functions in odbc connector stage
ETL DEVELOPER
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
odbc connectivity stage
Thansk for the quick response.chulett wrote:Exactly. You use TO_DATE() to put data into a DATE field and TO_CHAR() to pull it back out again. ...
Yes that's true. However, the query is not that simple. I am actually trying to pull last month's data based on a date field from the table. It looks like this:
select col1, clo2 from tablename where
to_date(col1,'yyyy-mm-dd hh24:mi:ss')) = to_date(to_char(last_day(add_months(sysdate,'yyyy-mm-dd') ||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),-1));
This query works fine for odbc enterprise stage but not for odbc connectivity stage. (I understand that the query would be much simpler provided its oracle enterprise stage.)
Also with the solution provided we have to change the datatype of the column to char instead of timestamp. Is there any other alternative apart from this solution.
thanks in advance.
ETL DEVELOPER
Re: odbc connectivity stage
Then you should have put that in the first post, not your little snippet. Your "simple" snippet wastes our time.datastagedw wrote:However, the query is not that simple. I am actually trying to pull last month's data based on a date field from the table. It looks like this:
select col1, clo2 from tablename where
to_date(col1,'yyyy-mm-dd hh24:mi:ss')) = to_date(to_char(last_day(add_months(sysdate,'yyyy-mm-dd') ||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),-1));
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
Re: odbc connectivity stage
Sorry for the miscommunication.chulett wrote:Then you should have put that in the first post, not your little snippet. Your "simple" snippet wastes our time.datastagedw wrote:However, the query is not that simple. I am actually trying to pull last month's data based on a date field from the table. It looks like this:
select col1, clo2 from tablename where
to_date(col1,'yyyy-mm-dd hh24:mi:ss')) = to_date(to_char(last_day(add_months(sysdate,'yyyy-mm-dd') ||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),-1));
My main concern is about the conversion functions. I somehow feel that the coracle conversion fucntions are not supported in the odbc connector stage. I need help on this because the client is insisting on odbc connector stage only.
Please let me know in case any more details required.
ETL DEVELOPER
OK, first off SQL is SQL and valid SQL will work in any tool as long as you handle the data types correctly. Conversely, some tools may let you get away with invalid SQL and what you've got there is potentially invalid depending on the datatype of your fake column "col1". What is it in your table - DATE or VARCHAR2? That answer will drive whether you are using the 'conversion functions' correctly or not.
ps. None of those date function require a full timestamp value to function. And everything on the right side of your example is already a date so all those to_date / to_char / to_date shenanigans are completely unnecessary. But answer the first question first and we'll go from there.
ps. None of those date function require a full timestamp value to function. And everything on the right side of your example is already a date so all those to_date / to_char / to_date shenanigans are completely unnecessary. But answer the first question first and we'll go from there.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers