Manipulating string as timestamp

[#1]

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]

Try

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

or

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

[Gysbert_Wassenaar]

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

[Clever_Anjos]

LOAD

  F1,

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

LOAD

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

LOAD * INLINE [

    F1

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

];

[#5 · Accepted solution]

Sorry,

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

Regards