topic Manipulating string as timestamp in QlikView

Manipulating string as timestamp

Tue, 08 Oct 2013 13:36:29 GMT

Hi,

I have loaded data from Excel. I have a field CREATION_DATE, containing info like

20/05/13 15:32:13,986000000

I want to load as a timestamp and after this, I want to substract 6 hours to get the result in other field,

I tried several ways:

timestamp#(CREATION_DATE, 'DD/MM/YY hh:mm:ss') loads data, but any manipulation gives no result.

I tried using floor, substracting 0.25 and converting to timestamp, but no result.

Also substracting with Interval, and no result again.

Regards.

swuehl — Tue, 08 Oct 2013 13:39:23 GMT

Try

=timestamp(timestamp#('20/05/13 15:32:13,986000000', 'DD/MM/YY hh:mm:ss,ffff') - MakeTime(6))

=timestamp(timestamp#(CREATION_DATE, 'DD/MM/YY hh:mm:ss,ffff') - MakeTime(6))

Gysbert_Wassenaar — Tue, 08 Oct 2013 13:44:04 GMT

Try timestamp#(CREATION_DATE, 'DD/MM/YY hh:mm:ss,fff') or first cut off the fraction of the seconds.

Clever_Anjos — Tue, 08 Oct 2013 13:53:01 GMT

LOAD

F1,

Timestamp(F1 - MakeTime(6)) as F2;

LOAD

timestamp#(F1,'DD/MM/YYYY hh:mm:ss[,ffffffff]') as F1;

LOAD * INLINE [

"20/05/13 15:32:13,986000000"

];

Tue, 08 Oct 2013 14:01:21 GMT

Sorry,

I find solution. The problema were decimals after seconds. I used Left(CREATION_DATE,17) and then it worked fine as expected.

Regards