topic Re: Oracle format date - Qliksense erro in App Development

Oracle format date - Qliksense erro

ALEX1977ALEX — Fri, 01 Apr 2022 12:21:19 GMT

hello qlik community

I' facing with an issue about loading some table with ODBC Oracle.

invalid datetime format

it seems the date before 1900?

i can loading the firt 44322 lines but the next line it is a date too old... 1901

there is a modification to do on the variable of the script on qliksense

Thank

Re: Oracle format date - Qliksense erro

hic — Fri, 01 Apr 2022 13:08:56 GMT

This looks like an ODBC error, i.e. an error in the database. If so, it has nothing to do with Qlik Sense.

Try a script with just the SELECT statement - no preceding "Load". If you still get an error, then it is a DB error. (The SELECT statement is evaluated by the ODBC driver, and not by the Qlik engine.)

Re: Oracle format date - Qliksense erro

ALEX1977ALEX — Fri, 01 Apr 2022 13:25:41 GMT

thank for your return Henric

Before change date 1901 to 2019 we try to make this SELECT on our ORACLE =>no problem at all

However we change the date 1901 for 2019 everything is good (SENSE LOADING)

before change

the oracle format date is 24/10/01 00:00:00,0000000000

After Change

change on the forms 24/10/19 00:00:00,0000000000

we don't have information about the century ?

Re: Oracle format date - Qliksense erro

hic — Mon, 04 Apr 2022 07:18:30 GMT

There are two questions here:

1) Do you get an ORACLE error, like "[ORACLE][ODBC] Invalid datetime format"?
If so, you have an error in your ORACLE database. It is not a Qlik Sense error.

2a) Are you loading from the ORACLE DATETIME data type?
If so, it should work correctly between '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. The century information should be there (and should get loaded via ODBC into Qlik Sense), but not be visible if you use 'DD/MM/YY'.
See https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/datetime.html

2b) Are you loading from a text string '24/10/19 00:00:00,0000000000'?
If so, the century is indeed missing. The Qlik engine will use 1972 as the cut-off year: '24/10/72' will be interpreted as 1972 and '24/10/71' will be interpreted as 2071. So, all dates with two-digit years will be interpreted as being between 1972 and 2071.

Re: Oracle format date - Qliksense erro

ALEX1977ALEX — Mon, 04 Jul 2022 13:09:15 GMT

Hello Henric

sorry for my late late response

But by the way i only change the sql select with data format informations , i had to change the date format directly on the sql

Alex