Timestamp subtraction

[#1]

Hi Guys,

I am loading from excel workbook

I have the following data

Start Time     Start Date          End Time  End Date

   9:00:00     01/01/2012          10:00:00    02/01/2012

  10:00:00    01/01/2012          10:00:00     05/01/2012

My Formats are as follows :

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

I wish to load add a field called Turnaround which id the dfferent in Hours and Minutes between the 2

Start Time     Start Date          End Time  End Date             Turnaround

   9:00:00     01/01/2012          10:00:00    02/01/2012          25:00

  10:00:00    01/01/2012          10:30:00     05/01/2012         96:30

I have tried a few different versions on the cript but all to no avail

Can anyone advise please ?

Thanks

Anne

[swuehl · Accepted solution]

I assume your Date and Time fields will be correctly read in, since your DateFormat and TimeFormat seems to match.

Then you could use interval() function in your load like this:

LOAD

*,

interval( (EndDate+EndTime) - (StartDate+StartTime) ) as Turnaround;

LOAD

...

StartTime,

StartDate,

EndTime,

EndDate,

..

from ExcelTable.xls;

[#3]

Perfect , you make it look so easy Swuehl,

Thanks