Alteryx Connection Oracle SQL Error ORA-01722 Invalid Number

[Tanai_Goncalves]

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

[simon]

I would agree with Date conversion. Have you tried using 'YYYY-MM-DD'? I'd bet it's tripping over MON format

Simon

[Tanai_Goncalves]

Hey my friend.

Thanks a lot for reply.

Unfortunately still did not works.

😕

[Roby64]

Try with 

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

[Reddy4]

Try to change it to Char

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