Need Expression Help

[rittermd]

I need to calculate the length of time between 2 date/timefields in my script

Timestamp(Timestamp#([At Destination], 'M/D/YYYY h:mm') ) AS [At Destination],

Timestamp(Timestamp#([Pickup Time], 'M/D/YYYY h:mm') ) AS [Pickup Time],

These 2 fields are being loaded from an excel spread sheet in addition to others.

I want to know the time lapse between the Pickup Time and At Destination. Basically how long it took them to leave and get to the site.

I have tried a few things and nothing has worked so far.

[swuehl]

Have you tried using Interval() function?

LOAD *,

     Interval( [At Destination] - [Pickup Time] ) AS Interval;

LOAD

     Timestamp(Timestamp#([At Destination], 'M/D/YYYY h:mm') ) AS [At Destination],

     Timestamp(Timestamp#([Pickup Time], 'M/D/YYYY h:mm') ) AS [Pickup Time],

     ...

I am using a preceding LOAD to be able to use the correctly interpreted fields, you can also use something like

Interval(

     Timestamp#([At Destination], 'M/D/YYYY h:mm') - Timestamp#([Pickup Time], 'M/D/YYYY h:mm')

     )  AS Interval

[rittermd]

Thank you.  That almost worked.  I got the correct number results this time.  Not sure what I did wrong.

2 Things.

1.  The result is displaying the seconds even though the formatting in your expression does not have seconds.

2.  The result is displaying AM at the end of the number. How do I get rid of that?

[swuehl · Accepted solution]

Add a format code to the interval() function (details are described in the HELP), like

Interval( .... , 'h:mm')