How to convert a 4 char string to time format? 1

[haneen97]

Hi,
My field is a 4 char string field. I would like to subtract two fields of the same format from each other. I need to convert to time format to get the correct results. Please help.

Thanks a lot

Mo

 

[synapsevampire]

Hey Mo, try posting what's in the string, and why.

4 characters won't handle a time very well as a time has HHMMSS.

I understand that you're very busy, but that's pretty basic.

-k

 

[haneen97]

Hi,

I know what you mean, but that is how the database is designed (IMEIN VARCHAR2(4)) to take military time format time. TIMEOUT is the same. The data is stored like this:
1120 for 11:20 AM
1535 fro 3:35 PM
and so on.

I tried to convet to number and subtract but, the results were wrong because it treats the whole number as an integer.

I hope this helps

Thanks a lot

Mo

 

[bdreed35]

I derived this from sv's faq. It converts your 2 values to a date and time first, then figures the difference.

stringVar one := "0800";
stringVar two := "1520";
datetimevar time1 := DateTime(1900,1,1,val(left(one,2)), val(right(one,2)),0);
datetimevar time2 := DateTime(1900,1,1,val(left(two,2)), val(right(two,2)),0);

numberVar dur := datediff("s",time1, time2); //get the seconds between 2 dates
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") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

~Brian

 

[synapsevampire]

You still didn't define the format, is there a padded 0 if the time is 9:00AM?

Here's a FAQ on using time:

faq767-3543

In addition, here's a formula that should help:

whileprintingrecords;
stringvar TimeinStr trim({table.timein});
stringvar TimeoutStr trim({table.timeout});
datetimevar timein;
datetimevar timeout;
numberVar dur;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
If len(trim(timeinstr)) = 4 then
timein := currentdate+time(left(TimeinStr,2)+":"+mid(timeinstr,3,2)+":00")
else
timein := currentdate+time(left(TimeinStr,1)+":"+mid(timeinstr,2,2)+":00");

If len(trim(timeoutstr)) = 4 then
timeout := currentdate+time(left(TimeoutStr,2)+":"+mid(timeoutstr,3,2)+":00")
else
timeout := currentdate+time(left(TimeoutStr,1)+":"+mid(timeoutstr,2,2)+":00");
dur := datediff("s",timein,timeout);
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");
hhmmss

-k

 

[haneen97]

Thank you all,
Between both answers I was able to scrap what I was looking for
Thanks a gain.

Mo