Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
YD
Partner - Contributor II
Partner - Contributor II

Differents Date and Time format in the same field

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

 

 

1 Solution

Accepted Solutions
YD
Partner - Contributor II
Partner - Contributor II
Author

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

View solution in original post

3 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

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