help with timestamp

[hopkinsc]

Hi All,

I have a field with a timestamp, the values of this field are formatted like this...

1306052300376

This is broken up as follows..

13 = Year

06 = Month

05 = Day

23 = Hour

00 = Mins

37 = Secs

6 = Extract counter (I want to ignore this)

How can i reformat these in the script to be a seperate date field (DD/MM/YYYY) and a seperate time field (hh:mm)

Any help would be great

[rbecher · Accepted solution]

Hi,

you can use:

date(date#(left(1306052300376, 6), 'YYMMDD'), 'DD/MM/YYYY')    ..for date

=time(time#(mid(1306052300376, 7, 4), 'hhmm'), 'hh:mm')    ..for time

- Ralf

[eduardo_sommer]

MakeDate('20'&Mid(Field,1,2),Mid(Field,3,2),Mid(Field,5,2)) as NewDate

MakeTime(Mid(Field, 7,2), Mid(Field, 9,2), Mid(Field,11,2)) as NewTime

Where Field is the name of the field with the timestamp (substitute for your field)

Eduardo

[its_anandrjs]

In load script load a table like below

LOAD *,

Date(Date#(Left(OldDate,6),'YYMMDD'),'DD/MM/YYYY') as NewDate,

Time(Time#(Mid(OldDate,7,6),'hhmmss'),'hh:mm:ss') as NewTime;

LOAD * Inline

[

OldDate

1306052300376

];

Or in front end try with this in any text object

For Date

Date(Date#(Left(1306052300376,6),'YYMMDD'),'DD/MM/YYYY')

For Time

Time(Time#(Mid(1306052300376,7,6),'hhmmss'),'hh:mm:ss')