Split a date and time field

[PaulK]

Hi

I'm new to Qlik Sense and I'm struggling to split a date/time field into separate date and time fields. The date/time column in a csv file has the following data

date/time

6 Jan 2022 14:33:52 UTC

7 Jan 2022 12:17:21 UTC

7 Jan 2022 13:39:24 UTC

7 Jan 2022 13:40:23 UTC

7 Jan 2022 14:22:31 UTC

8 Jan 2022 15:51:51 UTC

9 Jan 2022 06:15:16 UTC

10 Jan 2022 10:17:09 UTC

10 Jan 2022 10:22:35 UTC

10 Jan 2022 10:23:51 UTC

10 Jan 2022 10:25:41 UTC

Is it possible to split the field and have a date field in the following format DD/MM/YYYY and another field with the time in hh:mm:ss?

Thanks

Paul

[BrunPierre]

Hi, try this.

Date(Floor(Timestamp#(Left([date/time],20),'DD MMM YYYY hh:mm:ss')),'DD/MM/YYYY') as Date
Time(Frac(Timestamp#(Left([date/time],20),'DD MMM YYYY hh:mm:ss')),'hh:mm:ss') as Time

[marcus_sommer · Accepted solution]

I'm not sure if the UTC mark could be directly fetched from the timestamp# converting - if not replacing it may the most simple approach. After a converting a round/split function would be needed to get real dates/times and not timestamps which look like dates/times. Therefore your expression may look like:

date(floor(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'DD/MM/YYYY')

time(frac(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'hh:mm:ss')

[PaulK]

Thank you Marcus, your code worked perfectly.