topic Re: Not a valid month error in App Development

Not a valid month error

ipiola — Thu, 05 May 2022 14:17:07 GMT

Hello,

I´m working with an Oracle SQL Developer querry, the querry runs ok in the SQL Developer side, but when I run it in Qlik Sense Iím getting the ORA-01843: not a valid month error.

I´d tried all sorts of combinations, including all the previous posts in the community, and nothing seems to work.

In the querry I have 2 statements with dates as follows:

WHERE c.fechasistema < '01/01/2022'

AND f.fechbaja > '31/12/2021

This two conditions work fine in SQL Developer, but doesn´t work in Qlik Sense. I tried to_date(), to_number(), setting a variable in the Qlik Sense load editor, nothing worked.

Any ideas?

Thank you.

Re: Not a valid month error

ipiola — Thu, 05 May 2022 18:52:12 GMT

Thank you for the answer.

I cant´t find the NLS_DATE_FORMAT, but checking the data types in the tables with the values I can see that one is in Date type in this format 'DD/MM/YY' and the other is in TIMESTAMP. I tried to convert the formats with a lot of combinations to_date(), to_number(), and leaving the fields exactly how they came from the database.

Nothing is working.

Regards.

Re: Not a valid month error

vinieme12 — Fri, 06 May 2022 18:34:40 GMT

try below for testing

Lib Connect to YOURCONNECTION;

TEST:

SQL

ALTER session set NLS_DATE_FORMAT='DD/MM/YYYY';

Select top 10 *

from YourTableName

Where c.fechasistema < '01/01/2022';

Re: Not a valid month error

ipiola — Tue, 10 May 2022 11:26:53 GMT

Tanks for your answer vinieme12,

I tried your statement but the following error pops up:

The following error occurred:

Connector reply error: Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.

The error occurred here:

SQL ALTER session set NLS_DATE_FORMAT='DD/MM/YYYY'

Re: Not a valid month error

durgesh22 — Tue, 10 May 2022 11:39:24 GMT

@ipiola

In order to execute non Select queries you have to do some changes ,please refer below post.

https://community.qlik.com/t5/Knowledge/Connector-reply-error-Executing-non-SELECT-queries-is-disabled/ta-p/1716322

Re: Not a valid month error

ipiola — Tue, 10 May 2022 12:49:33 GMT

There´s no key for that entry, these are the config keys:

  <add key="InstallationMode" value=
  <add key="LogSQLStatements" value=
  <add key="QlikEngineLocation" value=
  <add key="mysql-reading-strategy" value=
  <add key="oracle-reading-strategy" value=
  <add key="postgres-reading-strategy" value=
  <add key="sybase-reading-strategy" value=
  <add key="teradata-reading-strategy" value=
  <add key="gbq-reading-strategy" value=
  <add key="redshift-reading-strategy" value=
  <add key="apache-hive-reading-strategy" value=
  <add key="impala-reading-strategy" value=
  <add key="db2-reading-strategy" value=
  <add key="mssql-reading-strategy" value=
  <add key="presto-reading-strategy" value=
  <add key="mongo-reading-strategy" value=
  <add key="drill-reading-strategy" value=
  <add key="azure_sql-reading-strategy" value=
  <add key="apache-phoenix-reading-strategy" value=
  <add key="apache-spark-reading-strategy" value=
  <add key="simulate-engine-terminate" value=