Time format (hhmmss) shown wrong

[jamesehunt]

Hi all,

I have a matrix which looks like this:

Carton ID	Scan Date	Scan Time
1769001105112	20180511	81958

In the  "Data load editor" I added the following

The result for the Date is correct, but unfortunately the time is wrong. It should be 08:19:58 (hh:mm:ss)

Can someone please help.

Much obliged

[sunny_talwar]

Try this (use single h instead of 2)

Time(Time#(TPROA, 'hmmss')) as [Time-scan-return-carton]

[jamesehunt]

Hi Sunny,

unfortunately scan time can be

81920 = 08:19:20 as well as

182130 = 19:21:30 (which are now not shown correctly)

[sunilkumarqv]

please share sample

[dsharmaqv]

Try this, it should work

=Time(Timestamp#(81920,'hmmss'))

[sunilkumarqv]

can you try this

Time(Time#(If(len(TPROA)>5, 'hhmmss','hmmss'))) as [Time-scan-return-carton]

[sunny_talwar · Accepted solution]

I think sunilkumarqv‌ might have meant this...

Table:

LOAD *,

Time(Time#(TPROA, If(Len(Trim(TPROA)) = 5, 'hmmss', 'hhmmss'))) as [Time-scan-return-carton];

LOAD * Inline [

TPROA

81920

182130

];

The above seems to work

[jamesehunt]

Hi Sunil,

I like your approach, but it didn't work.

here is the sample

Anything else?

[sunny_talwar]

Did you try this?

Table:

LOAD *,

Time(Time#(TPROA, If(Len(Trim(TPROA)) = 5, 'hmmss', 'hhmmss'))) as [Time-scan-return-carton];

LOAD * Inline [

TPROA

81920

182130

];