topic Re: Differents Date and Time format in the same field in QlikView

Differents Date and Time format in the same field

YD — Sat, 08 Jun 2019 21:34:07 GMT

I am still a new user of QV and here is another issue I am dealing with since 5 days

I have 2 differents date and time format in the same field and I would to convert it into an unique one

There is a format with AM and PM, this one the month it is in one number format (example May = 5 and not 05) this one got secondes (ss) and another format, there is no seconds

I have these formats:

M/DD/YYYY hh:mm:ss

MM/DD/YYYY hh:mm

And here is the output format I would like to get DD/MM/YYYY hh:mm:ss

I should get at the end 860 lines....

Could you help me please

Thank you in advance

Re: Differents Date and Time format in the same field

disqr_rm — Sat, 08 Jun 2019 23:18:52 GMT

Just use timestamp() function and and that should do the magic.

LOAD Uuid,
timestamp(TimeStamp) as TimeStamp_New,
TimeStamp
FROM
[Classeur1.xlsx]
(ooxml, embedded labels, table is Feuil1);

Re: Differents Date and Time format in the same field

YD — Sun, 09 Jun 2019 20:36:35 GMT

I have tried this before

TIMESTAMP(TIMESTAMP#(TimeStamp, 'M/DD/YYYY h:mm:ss') AS NEW_DATE,

It doesn't work for all the lines, just those in this format not the others

And when I use what you suggest, the field is empty, it doesn't show up

Any other suggestion?

Thank you, best regards

Re: Differents Date and Time format in the same field

YD — Sun, 09 Jun 2019 22:08:59 GMT

I finally found a solution myself, here it is

ALT(TIMESTAMP(TIMESTAMP#(TimeStamp, 'M/DD/YYYY h:mm:ss TT'), 'DD/MM/YYYY hh:mm:ss'), DATE(DATE#(TimeStamp, 'MM/DD/YYYY hh:mm'), 'DD/MM/YYYY hh:mm:ss')) AS %DATE,

Thanks