timestamp from oracle into a csv splitting but returns empty

[Anonymous]

Hi all

i have the following dates from a oracle sql table stored in a CSV file. when this is to be split to Transaction Date and Time separately, the below data has problems(empty fields) when your hour clock is single digit. i am thinking this is due to the extra space between 2017 and hour or date formatting issue

20/03/2017  1:23:00 PM,   --> doesnt work

20/03/2017  3:56:00 PM,  --> doesnt work

20/03/2017  3:19:00 PM,  --> doesnt work

20/03/2017  3:18:00 PM,  --> doesnt work

19/03/2017  6:07:00 PM,  --> doesnt work

20/03/2017  9:09:00 AM,  --> doesnt work

20/03/2017 10:09:00 AM,  --> split correctly

20/03/2017 11:54:00 AM,  --> split correctly

date(floor([TRANSACTION DATETIME])) as [Transaction Date],

  time(frac([TRANSACTION DATETIME])) as [Transaction Time],

Can you tell me the ways to fix this pls

Thanks

[sunny_talwar · Accepted solution]

Try this may be

Date(Floor(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Date],

Time(Frac(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Time],

[sunny_talwar]

I think the issue seems to be that there are two spaces between date and time when hour is single digit and purgeChar() with TimeStamp#() might help here

[er_mohit]

Hi

Have a look to Alt Function.

Convert multiple date formats into one date format using Alt() function - QlikView Blog, QlikView Vi...

[Anonymous]

Thanks Sunny that was quick and works like a charm. yes it is the space that troubled a bit