Qlik Cloud not recognizing DATE column from a database via SQl as date field

[Golem]

This problem occurs since we migrated from Qlik Sense to Qlik Cloud.

I have a loading script like:

LIB CONNECT TO 'DWH'; // database connector for database Exasol named "DWH"

LOAD
    ETA_DATUM
;
SQL
SELECT
    ETA_DATUM
FROM
    t
;

eta_date has type "DATE" in the database, for example "2023-12-29".

When I look at this field in a table in Qlik Cloud I get "2023-12-29 00:00:00".
I also can't use any date functions like day(), month(), year() etc. on this field (I get no error but the result is just empty). Seems to be loaded as a string with a funny detour through TIMESTAMP territory for whatever reason.

Workarounds I tried without working:

LOAD
    ETA_DATUM                                AS eta_datum0
  , date(ETA_DATUM)                          AS eta_datum1
  , date(floor(ETA_DATUM))                   AS eta_datum2
  , DATE#(ETA_DATUM, 'YYYY/MM/DD')           AS eta_datum3
  , DATE#(ETA_DATUM, 'YYYY-MM-DD')           AS eta_datum4
  , DATE#(left(ETA_DATUM, 10), 'YYYY/MM/DD') AS eta_datum5
  , DATE#(left(ETA_DATUM, 10), 'YYYY-MM-DD') AS eta_datum6

Results are either strings (= month() function does not work) or just empty - see attached screenshot.

Since Qlik seems unable to get their shit together ... how can I fix this?

[vincent_ardiet_]

Try this maybe:

DATE#(left(eta_date, 10), 'YYYY-MM-DD')

[Golem]

Thanks for the suggestion.

Sadly this does return a value (just like eta_datum5) but it's a string as well.
Updated the list of things I tried so far.

[vincent_ardiet_ · Accepted solution]

But according to your screenshot, eta_datum0 is formatted in DD.MM.YYYY HH:MM:SS, so the syntax should be:

DATE#(left(eta_date, 10), 'DD.MM.YYYY')

[Golem]

> But according to your screenshot, eta_datum0 is formatted in DD.MM.YYYY HH:MM:SS

ETA_DATUM is a DATE in a database = ISO 8601 format = "2023-10-18". It does not contain any time information by definition.
Why it does have the format DD.MM.YYYY HH:MM:SS and comes out as  string when loading a DATE into Qlik Cloud is something that is bugged on Qliks side.

But your solution does work. ETA_DATUM is now a date and month() returns a valid result.

Thank you very much.

[vincent_ardiet_]

On some Oracle DB we have, before performing loads, we are forcing the format in our script. So we are sure that whichever settings is done by the DBA or the developers, we will always receive the same thing.
For example, for Oracle:

SQL alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

[Golem]

In our database (Exasol) this is not neccessary and the format returned is always 'YYYY-MM-DD' for a DATE as specified by our DBA.