Problems with Datetime Format

[heid_f]

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!

[BrunPierre]

As below.

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

[heid_f]

Sadly, your solution doesn't work.

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

(Always with a comma)

[TimothyB99]

Try this:

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

[heid_f]

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

[Or]

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.

[heid_f]

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,

[Or]

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.

[heid_f]

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.