Timezone conversion 3

[Jcfx]

CR10,sql db

There are two tables orders and city. One of the joins is orderCitycode = CityCityCode

I have a datetime field stp_arrivaldate that I perform a calculation on using the currentdatetime that the report is run to determine if a delivery is running late or not.


at the moment I am not taking time zones into consideration if report is run at 10:45 eastern

OrderNum stp_arrivaldate HowLate CallforExcuse cty_GMTDelta
1234      02/07/06 7:00   0d:3h:45m  123-4567     5.00
4567      02/07/06 10:00  0d:0h:45m  123-4569     8.00

Line one is correct, line two is not

We did not have the ability to use field cty_GMTDelta until now it was always null. Without that field, the following is the code I am currently using to determine HowLate

numbervar tsecs := datediff("s",{Rpt_LateLoadsScheduled;1.stp_arrivaldate},currentdatetime);
numbervar ndays := truncate(tsecs/86400);
tsecs := remainder(tsecs,86400); 
numbervar nhours := truncate(tsecs/3600); 
tsecs := remainder(tsecs,3600);
numbervar nmin := truncate(tsecs/60); 
tsecs := remainder(tsecs,60); 

if nhours-24<=0 then totext(ndays,0)+"D: "+totext(nhours,0)+"H: "+ totext(nmin,0)+
"M: "

Cty_GMTDelta
5.00 = eastern
6.00 = central
7.00 = mountain
etc.

I was thinking of using an if/else statement that checks for the cty_GMTDelta then changes the above calculation in some way. If the field is 5.00 no change is needed. If it is 6.00 I need the CurrentDatetime + 1 hour. Would that be CurrentDateTime + 3600?

If there is an easier way to do this, I am all ears

Thanks again


Julie
CR 9,10 CE10 Sql DB

 

[muffntuf]

have you considered using a cr custom function that converts the GMT for you? They should be in your functions library.

 

[synapsevampire]

Well the dateadd function is the common thing here, however I'm not sure how you are determining the proper timezones, it's a bit of a sticky wicket...

dateadd("h",1,currentdatetime} is the simpler way to do this.

Check out Ido's UFl for time conversion:

http://www.milletsoftware.com/CUT_Light.htm

It handles timezone conversion and has lots of other functionality.

-k

 

[Jcfx]

I don't have such a function listed under additionals.

I did read a prior thread about using third party libraries, but that is not an option.



Julie
CR 9,10 CE10 Sql DB

 

[Jcfx]

Thanks Synapsevampire,

The timezone the report is run from will always be eastern (5.00) so CurrentDateTime is always the time the report is run at.

The timezone the stp_arrivaldate is in is determined by cty_GMTDelta.

So in the first example because cty_GMTDelta for order 1234 is 5.00 So the arrival time of 7:00 is eastern. I don't have to do any conversion, and late is really late

In the second example, cty_GMTDelta is 8.00, which means the arrival time of 10:00 am is pacific time.

I now have to convert either the arrival time to eastern, or the report time to pacific, whichever is easier to determine if the second order is really late or not, and if it is display just how late it is.

I don't know if that information helps any





Julie
CR 9,10 CE10 Sql DB

 

[synapsevampire]

I think that I'd simplify by standardizing the time, perhaps to Eastern, then all calculations are handled the same way.

whileprintingrecords;
Datevar MyDate;
if Cty_GMTDelta = 8 then
MyDate:=dateadd("h",3,{stp_arrivaldate})
else
MyDate := {stp_arrivaldate}

So your formula could then reference the MyDate variable, as in:

evaluateafter({@MyDateFormula});
Datevar MyDate;
numbervar tsecs := datediff("s",MyDate,currentdatetime);
numbervar ndays := truncate(tsecs/86400);
tsecs := remainder(tsecs,86400);
numbervar nhours := truncate(tsecs/3600);
tsecs := remainder(tsecs,3600);
numbervar nmin := truncate(tsecs/60);
tsecs := remainder(tsecs,60);

-k

 

[lbass]

I think you can just change the first part of your formula to:

datetime var currdt := dateadd("h",{cty_GMTDelta}-5,currentdatetime);
numbervar tsecs := datediff("s",{Rpt_LateLoadsScheduled;1.stp_arrivaldate},currdt);

-LB

 

[Jcfx]

Thanks so much to everyone, both suggestions work great.



Julie
CR 9,10 CE10 Sql DB