topic Re: Problems with Datetime Format in QlikView

Problems with Datetime Format

heid_f — Fri, 02 Dec 2022 11:06:16 GMT

Dear Community,

we have a QliKSense App, which is connected to our CRM-Database.

Now, we got the problem to transform the date format of CRM (example: 20211112075518828) to our standard date format (DD.MM.YYYY).

We tried different codings, but nothing works perfectly, yet.

The best code is following:

LOAD ID, Upd as Upd_raw, Date(Date#(Left(Upd, 9), 'Y,YYYMMDD')) As Upd ; [CRM_KM]: SELECT ID, Upd FROM CRMU9.dbo."CRM_KM";

But this code gets us as example this date: 12.11.0021 (Here's missing the leading 2 of the year!)

Does anybody know this problem and a solution?

Thanks!

Re: Problems with Datetime Format

BrunPierre — Fri, 02 Dec 2022 11:53:00 GMT

As below.

Date(Date#(Left(Upd,8),'YYYYMMDD'),'DD.MM.YYYY') as Upd

Re: Problems with Datetime Format

heid_f — Fri, 02 Dec 2022 13:10:46 GMT

Sadly, your solution doesn't work.

The function Left(Upd,8) brings following value: 2,0211112

(Always with a comma)

Re: Problems with Datetime Format

TimothyB99 — Sun, 04 Dec 2022 17:01:24 GMT

Try this:

date#(timestamp#(20211112075518828, 'YYYYMMDDHHMMSSSSS'), 'DD.MM.YYYY') as Date

Re: Problems with Datetime Format

heid_f — Mon, 05 Dec 2022 10:16:27 GMT

Thanks, but Sadly this doesn't work, too.
The functions return the same string as input: 20211112075518828

Re: Problems with Datetime Format

Or — Mon, 05 Dec 2022 10:27:39 GMT

Date(DayName(Timestamp#('20211112075518828','YYYYMMDDHHmmssfff')),'DD.MM.YYYY')

If this doesn't work on your end, your input is probably different from what you described, since the syntax itself is correct for the example you provided.

Re: Problems with Datetime Format

heid_f — Mon, 05 Dec 2022 10:32:29 GMT

Hey, thanks!
Your example works with a fix string '20211112075518828', but not if I put in my database field.

I think the problem is, the database field is being interpreted as number and I'm not able to convert it to a string. Following code, doesn't work, too.

Date(DayName(Timestamp#(Text(Upd),'YYYYMMDDHHmmssfff')),'DD.MM.YYYY') as test2,

Re: Problems with Datetime Format

Or — Mon, 05 Dec 2022 10:42:27 GMT

If for some reason this is being interpreted as a number, you're going to have issues because the length exceeds Qlik's allowed precision for numeric values. That said, if this was the case, Text() should do the trick. You may want to load Text(Upd) and see what comes out, and then adjust the expression based on that.

Re: Problems with Datetime Format

heid_f — Mon, 05 Dec 2022 11:12:18 GMT

Yeah, as I said before, I tried the Text() function... But it doesn't works.

In Diagrams, this code works perfectly: =Date(Date#(Left(Upd_raw, 8), 'YYYYMMDD'))

But not in the Script. But I need it here to work, because it's a KEY field.