Differents Date and Time format in the same field

[YD]

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

[disqr_rm]

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);

[YD]

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

[YD · Accepted solution]

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