NSQL "Invalid Month" error only in Clarity, not in Query tool

[Lowell Wetzel]

I have a query that I am modifying from "Month" to "Week". I was able to replace the Month references and change them to week.

I can run the query in Toad. When I run it in Clarity, I get Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month

What is the best way to find the cause?

I don't see the data element listed in the logs that is causing the error.

[Paul Schofield]

Hi there,

Can you paste the query into this discussion?

Paul

[David Morton]

The 'problem' is that TOAD and Clarity will likely have different default date formats (set at environment level).

To make sure that SQL in TOAD and Clarity will run exactly the same (and without errors) you would normally code it to explicitly handle the date formats in the SQL (with TO_DATE, TO_CHAR as needed) rather than relying upon an assumed (default) date format.