String to hh:mm

[Anonymous]

Hello Friends,

We are storing time in our sql database as a string eg 0715. How can I change it to the correct format to read it as 07:15?

I tried the below but none formatted it right.

Time(Time#(TimeColumn,'hhmmTT'),'hh:mm')

Or

Time(Time#(Mid(TimeColumn,1,4),'hh:mm'))

In addition, please also explain where I need to enter the expression or the formatting code since I am new to the QlikView.

Thank you.

[MK_QSL · Accepted solution]

Load

     *,

     Time(Time#(TimeColumn,'hhmm'),'hh:mm') as TimeColumnNew;

SQL SELECT

     Field1,

     Field2,

     TimeColumn,

     Field4

From BlahBlah;

[Anonymous]

It worked. I wasn't including the alias and didn't do proper selection in Load and Select.

I get the structure now. Thank you.

[Anonymous]

This formatted time looks fine when I have CustomerID in a separate List Box and formatted time field in a separate List Box. However, when I try to see CustomerID and this formatted time field together in a Table Box, all the times get displayed as 00:00. Can you please help so that all the CustomerID displays all the correct times in Table Box instead of 00:00?