Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello and Welcome,
I am facing a problem with a formatting date and time , I am fetching this column form local html page and it gives me a column with this format : Friday, May 1, 2020 1:46:06 AM
I tried to do this :
Timestamp#("Last connection", 'WWWW, MMMM d, yyyy h:mm:ss TT') ,it shows on qlik table but I can apply filter year , month and day on this.
please help 😞
Thank you very much for replying and supporting, I tried but it didn't work.
I figured out something and works with me . I separated them individually using subfield and I used the timpstamp to rejoin them. and it works .
in load I add a new column as the following:
Timestamp#(subfield(subfield("Last connection", ',', 2), ' ', 3) &'-' & subfield(subfield("Last connection", ',', 2), ' ', 2) &'-'& subfield(subfield("Last connection", ',', 3) , ' ', 2)
&' '& subfield(subfield("Last connection", ',', 3) , ' ', 3) &' '& subfield(subfield("Last connection", ',', 3), ' ', 4),'dd-MMM-yyyy hh:mm:ss tt') as date_and_time.
Hi,
I think the main problem is that D for days and YYYY for years need to be specified in upper case. Lower case letters are used for hours, minutes, and seconds:
Timestamp#("Last connection", 'WWWW, MMMM D, YYYY h:mm:ss TT')
Also, check the data for April, to make sure that you need the full month name (MMMM) and not just a 3-letter month (MMM).
Cheers,
Thank you very much for replying and supporting, I tried but it didn't work.
I figured out something and works with me . I separated them individually using subfield and I used the timpstamp to rejoin them. and it works .
in load I add a new column as the following:
Timestamp#(subfield(subfield("Last connection", ',', 2), ' ', 3) &'-' & subfield(subfield("Last connection", ',', 2), ' ', 2) &'-'& subfield(subfield("Last connection", ',', 3) , ' ', 2)
&' '& subfield(subfield("Last connection", ',', 3) , ' ', 3) &' '& subfield(subfield("Last connection", ',', 3), ' ', 4),'dd-MMM-yyyy hh:mm:ss tt') as date_and_time.