to_date functions in odbc connector stage

[datastagedw]

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

[yabhinav]

try this
select to_char(to_date(sysdate),'yyyy-mm-dd') from dual;

it should work

[yabhinav]

try this
select to_char(to_date(sysdate),'yyyy-mm-dd') from dual;

it should work

actually u dont need the to_date conversion thr..

select to_char(sysdate,'yyyy-mm-dd') from dual; will also return the same result

[chulett]

Exactly. You use TO_DATE() to put data into a DATE field and TO_CHAR() to pull it back out again.

[datastagedw]

Exactly. You use TO_DATE() to put data into a DATE field and TO_CHAR() to pull it back out again. ...

Thansk for the quick response.

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.

[chulett]

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));

Then you should have put that in the first post, not your little snippet. Your "simple" snippet wastes our time.

[datastagedw]

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));

Then you should have put that in the first post, not your little snippet. Your "simple" snippet wastes our time.

Sorry for the miscommunication.

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.

[chulett]

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.