Concat and date format issue: Garbage after expression: '00'

[eikenberg]

Hello,

i cannot concate a date for using it in a set analysis. I get the error:

I already trunced the time data with "trunc(belegdatum, 'DD')" to remove the time part but it looks it is added afterwards.

Looks like concat cannot handle "space"!?

How can i slove this?

[MayilVahanan]

Can you send the link reg. ">=" outdated in set analysis? so that,I can know it..
Its in help.

Set modifiers with dollar-sign expansions | Qlik Sense on Windows Help

[eikenberg]

I searched the forum for "set analysis greater than" and found this Using Greater Than in Set Analysis - Qlik Community - 353310

Didnt found the example in the help.

[MayilVahanan]

its very old thread. so ignore that.. now, set analysis is much advance 😀

[henrikalmen]

---

@eikenberg wrote:

In oracle date is always with time, it is not possible to cut it off. As soon as you convert something to date it has also time.

It's good practice to remove the timestamp part when loading the data into Qlik, if the timestamp part is unnecessary (and it seems to be only zero-values in your case and therefore probably unnecessary to keep in the data model).

[eikenberg]

I tried to remove the timestamp part, but it is not working.
Oracle to_date gives always date with time back.

And if i convert it to char qlik does not regocnize it as date anymore, even if its fit the given DateFormat.

SET DateFormat='DD.MM.YYYY';

to_char(BELEGDATUM, 'dd.mm.yyyy') as BELEGDATUM -> "$ascii $text"

Is there a way to convert the date field after the load?

[henrikalmen · Accepted solution]

Convert in qlik script, not in sql. You can for example do it with preceding load, something like this example:

mytable:
LOAD date#(left(BELEGDATUM, 8), 'DD.MM.YY') as BELEGDATUM, otherdata;
SQL select BELEGDATUM, otherdata from sqltable;