Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Perfect , you make it look so easy Swuehl,
Thanks