Alteryx Connection Oracle SQL Error ORA-01722 Invalid Number

Question

Hello!

I have a big Query connecting to Oracle DB.

When running on PL/SQL or SQL Developer it runs ok.

But when comes to Alteryx Server it sometimes (yes.. Sometimes..)  returns the error ORA-01722 Invalid Number.

I've tested a lot, and is probably a Date Conversion Issue. Any other ideas?

I have the following Date Filter (tested all three below) and sometimes work.. other not..

-- to_char(xcv.receive_date, 'MON-YYYY') >= ('JUL-2018') --:p_period_name

-- to_char(xcv.receive_date, 'YYYY') >= ('2018') --:p_period_name
-- xcv.receive_date between '01-JAN-2018' AND '31-DEC-2100' --:p_period_name
-- gp.period_year >= 2018

I'm trying this one.. but it's not working:

xcv.receive_date between To_Date('01-AUG-2019', 'DD-MON-YYYY') AND

To_Date('31-DEC-2100', 'DD-MON-YYYY')

Any help?

Kind Regards,

Tanai

Reddy4 · Answer

Try to change it to Char

TO_CHAR(TO_DATE('01-AUG-2019', 'DD-MON-YYYY'))

Roby64 · Answer

Try with

TO_DATE('31-DEC-2020', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = English')

Tanai_Goncalves · Answer

Hey my friend.

Thanks a lot for reply.

Unfortunately still did not works.

😕