Difference Between Two Times...

[ChiTownDiva]

Afternoon All...

I work off of an Oracle database and I have a field that represents a time (i.e. 080000 for 8 a.m.) that is VARCHAR2 type.

I need to write a formula to calculate the difference between two times. I know I need to convert this field to a Time field in order to do this--I tried the TimeValue() and got a "Bad Time Format String" error message.

What can I use to convert the string into a time and then calculate the difference.

Thanks in advance.

ChiTownDiva

 

[JoeCrystal]

TimeValue (Picture("080000","XX:XX:XX&quot)

 

[Reebo99]

Use this :

numberVar dur := DateDiff("n",DateTime(CurrentDate,Time(Picture("080000","XX:XX:XX&quot)),
DateTime(CurrentDate,Time(Picture("170000"X:XX:XX&quot)));//get the seconds between 2 times
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,0,"&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"
hhmmss;

Replace the items in bold with your field names.

Thanks to SV for faq767-3543 to display as HH:MM:SS.

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[Reebo99]

Whoops.......

numberVar dur := DateDiff("s",DateTime(CurrentDate,Time(Picture("080000","XX:XX:XX&quot)),
DateTime(CurrentDate,Time(Picture("170000"X:XX:XX&quot)));//get the seconds between 2 times
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,0,"&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"
hhmmss;

That's better!


Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[synapsevampire]

Consider doing all of this on the database side using a SQL Expression.

Unfortunately I don't have Oracle installed here so I can't work out the exact syntax for you.

You don't have a date to take into consideration, just 2 times? I guess you never have a start of 23:59:59 and and end of 01:10:10? Is the time 24 hour? What format is it stored in, HHMMSS, or?

More info might help, anyway, if you're comparing 2 times, convert both of them to datetimes (use a CAST function) in a SQL Expression and use the datediff function against them, that will net optimal performance.

-k