time formula feild

[tazibc]

I have a string feild which I have converted into time called PTA(planned time of arrival) and another string feild also converted into a time feild called ATA(actual time of arrival).I have put these feild son my report and they appear fine as time feilds. I want to work out the mins early when I subtract PTA from ATA to try and get mins early it comes up as seconds when I convert this into time, time({mins early})this gives me 00:00:00 which is incorrect as I know there are some vehcicles that have come early pls help anyone

 

[CoSpringsGuy]

could you show your formulas for converting your string values to time? PTA and ATA


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

I think you can just use:

datediff("n",datetime(currentdate,{@time1}),datetime(currentdate,{@time2}))

Then if you want, you can convert the results back into a string.

-LB

 

[tazibc]

hi my formulas for PTA are:-

TIME({Weekday_Hub_Data.Arrivals, Hub Planned Arrival Time})

my formula for AAT is:-

TIME({Weekday_Hub_Data.Arrivals, Hub Actual Arrival Time})

this is driving me mad pls help anyone

 

[tazibc]

For example my PTA (planned time of arrival) may have the value 22:35:00 and my AAT (actual arrival time) has the value 23:20:00.

Using common sense to get my mins early formula I use:-
PTA - AAT using the values above this returns a value of 83,700.00 which is incorrect I want to get the answer in a time format. So the answer for the above should be 01:15:00 but when I convert mins early value of 83,700.00 using:- time({mins early}) this returns 00:00:00.

I dont understand whats going on??

 

[lbass]

I guess you didn't try my suggestion. If you want to display the seconds (even as "00") in your results, try:

datediff("s",datetime(currentdate,{@ATT}),datetime(currentdate,{@PTA}))

You can convert to a string by using faq767-3543:

whileprintingrecords;
numberVar dur := datediff("s",datetime(currentdate,{@ATT}),datetime(currentdate,{@PTA}));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");
hhmmss

-LB

 

[tazibc]

thanks Ibass its sort of works but what it doesnt do is take into consideration the 24hour clock for example if i have a PTA time of 23:55:00 and a AAT time of 00:20:00 this gives me using the formula above -23:-35:00 which is incorrect is there anyway to overcome this. The actual answer should be {pta}-{aat} 00:25:00

 

[lbass]

Are you saying the times can be on different days?

-LB

 

[lbass]

You need a date field then that corresponds to the times. You would use the date field instead of "currentdate" in the formula. If the date is a string, please show us how it displays.

-LB

 

[tazibc]

essentially each time is for a different vehicle i.e

Vehicle ID PTA AAT Mins Early

HARLOW -02T 23:55:00 00:25:00 uses formula
BASINGSTOKE -3T 22:10:00 22:20:00

so on each day the above vehicles could run on differnt times as the report is run over a week parameter. The mins early column uses your formula so if we take the vehicle harlow the mins early should be 00:30:00 mins but the formula given does not take the 24hour clock into consideration can this be overcome

 

[tazibc]

The date feild I am using is {hub.date feild} its is a date time feild

 

[tazibc]

Ibass I have replaced currentdate with my date feild in my report as the following below:-
whileprintingrecords;
numberVar dur := datediff("s",datetime({Weekday_Hub_Data.Date},{@PTA}),datetime({Weekday_Hub_Data.Date},{@AAT}));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");
hhmmss

this comes back with an error saying a date time feild is required where I have inserted my date feild which ironically is a date time feild??

 

[tazibc]

Sorry the error is a date feild is required here and my date feild is a datetime feild could this be a problem??

 

[tazibc]

I have now converted my datetime feild to a date feild as below:-
date({hub.date}) and now inserted it into the formula below:-
whileprintingrecords;
numberVar dur := datediff("s",datetime({@convert to date},{@PTA}),datetime({@convert to date},{@AAT}));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");
hhmmss

its still does not take into consideration the 24hour clock and returns the result -23:-35:00 for vehical harlow -2t where PTA= 23:55:00 and AAT= 00:25:00 result should be 00:30:00

 

[lbass]

You should have one date field for arrival date and one for planned date. If you only have one date field, then how do you know when the time refers to the next date or the same date? Isn't is possible for the arrival time to be either earlier or later than the planned time?

-LB

 

[tazibc]

Ibass the date feild would be the same for both the PTA and ATA as we are running a parameter on one feild the date feild. So we have in our record selection of the report:-

{Weekday_Hub_Data.Late Trunk, Inbound/Outbound} in ["IN", "OUT"] and
{Weekday_Hub_Data.Date} in {?Start Date} to {?End Date} and
{Weekday_Hub_Data.Arrivals, Vehicle Route ID} <> "" and
{Weekday_Hub_Data.Late Trunk, Reason Code} <> ""

and the SQL query is :-
SELECT "Weekday_Hub_Data"."Arrivals, Vehicle Route ID", "Weekday_Hub_Data"."Arrivals, Vehicle Type", "Weekday_Hub_Data"."Arrivals, Hub Planned Arrival Time", "Weekday_Hub_Data"."Arrivals, Hub Actual Arrival Time", "Weekday_Hub_Data"."Arrivals, Fill%", "Weekday_Hub_Data"."Date", "Weekday_Hub_Data"."Departures,Actual Departure Time", "Weekday_Hub_Data"."Late Trunk, Inbound/Outbound", "Weekday_Hub_Data"."Late Trunk, (Free Text) Comment?", "Weekday_Hub_Data"."Late Trunk, Reason Code", "Weekday_Hub_Data"."Departures,Planned Departure Time"
FROM "HUB"."dbo"."Weekday Hub Data" "Weekday_Hub_Data"
WHERE ("Weekday_Hub_Data"."Late Trunk, Inbound/Outbound"=N'IN' OR "Weekday_Hub_Data"."Late Trunk, Inbound/Outbound"=N'OUT') AND "Weekday_Hub_Data"."Arrivals, Vehicle Route ID"<>N'' AND "Weekday_Hub_Data"."Late Trunk, Reason Code"<>N'' AND ("Weekday_Hub_Data"."Date">={ts '2009-07-26 00:00:00'} AND "Weekday_Hub_Data"."Date"<{ts '2009-08-22 00:00:01'})
ORDER BY "Weekday_Hub_Data"."Late Trunk, Inbound/Outbound"


so we are using the same date feild to give both our PTA and AAT as the vehicle will only ever arrive on the same date. We have two feilds PTA= Planned arrival time and the AAT = actual arrival time some times the AAT can be earler than the planned but majority of the time the AAT is later than the PTA

 

[lbass]

If the vehicle can ONLY arrive on the same date, you wouldn't be having this problem. In your example:

PTA= 23:55:00 and AAT= 00:25:00 result should be 00:30:00

...the 00:25:00 is after midnight, and therefore the next date.

-LB