I have a problem with converting format date and time

[alsahly2017]

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 😞

[Oleg_Troyansky]

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,

[alsahly2017 · Accepted solution]

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.