Results 1 to 9 of 9

081203, 00:00 #1Registered User
 Join Date
 Aug 2003
 Posts
 64
Unanswered: "textdelimited" numbers via AS400
Recently I had imported AS400 files via ODBC, new with AS400 and ODBC. These files are “textdelimited”.
How would I format “textdelimited” numbers in a 24:00:00 time format?
When they are imported the numbers are 1313. I need to convert it like 13:13:00 or 01:13:00 AM and calculate the difference using two fields. Doing this from the delimited numbers leaves a negative number when they roll over midnight.
CDbl(Left([MYTIME],2))+(CDbl(Mid([MYTIME],4,2)/60))+(CDbl(Right([MYTIME],2)/3600))
Results: 13.3936111111111
Prefer: 13:13:00 or 01:13:00 AM
I put 00:00:00 from using the input mask. When the time rolls over midnight and I calculate the fields, i.e. Total: [strtime][endtime], if the times is over midnight the results are negative or I can't format the results properly.
Please advised....

081303, 12:46 #2Registered User
 Join Date
 Aug 2003
 Location
 Bosnia & Hercegovina
 Posts
 57
this should do it....
Str$(Left(Table1!MYTIME,Len(Table1!MYTIME)2)) & ":" & Str(Right(Table1!MYTIME,2)) & ":00"
this is version for hours and minutes (3 or 4 digit numbers) only!!!
if you want to do 5 or 6 digit, you'll have to modify little bit, only the first one is a problem because of it could be 1 or 2 digit....
just follow this and combine with your original idea (left, mid, right).....
now calculating with date/time type of field, and just using time, will result in fraction of a day....
I don't know what you are going to do with data, but I am sure you can convert it as in the above sample statement....

081903, 22:46 #3Registered User
 Join Date
 Aug 2003
 Posts
 64
Originally posted by Strucnjak
this should do it....
Str$(Left(Table1!MYTIME,Len(Table1!MYTIME)2)) & ":" & Str(Right(Table1!MYTIME,2)) & ":00"
this is version for hours and minutes (3 or 4 digit numbers) only!!!
if you want to do 5 or 6 digit, you'll have to modify little bit, only the first one is a problem because of it could be 1 or 2 digit....
just follow this and combine with your original idea (left, mid, right).....
now calculating with date/time type of field, and just using time, will result in fraction of a day....
I don't know what you are going to do with data, but I am sure you can convert it as in the above sample statement....

081903, 22:47 #4Registered User
 Join Date
 Aug 2003
 Posts
 64
"AGAIN" (text delimted numbers)
Recently I had imported AS400 files via ODBC, new with AS400 and ODBC. These files are “textdelimited”.
How would I format “textdelimited” numbers in a 24:00:00 time format?
When they are imported the numbers are 1313. I need to convert it like 13:13:00 or 01:13:00 AM and calculate the difference using two fields. Doing this from the delimited numbers leaves a negative number when they roll over midnight. Know I need to convert them into numbers and calculate the difference.
I have tried two expressions,
STRTIME: nz([MYTIME],"unknown MYTIME")/3600 or
STRTIME:CDbl(Left([MYTIME],2))+(CDbl(Mid([MYTIME],4,2)/60))+(CDbl(Right([MYTIME],2)/3600))
Results: 13.3936111111111 when I format the results they are 1:26:48 AM
Prefer: 13:13:00 or 01:13:00 AM
I put 00:00:00 from using the input mask. When the time rolls over midnight and I calculate the fields, i.e. Total: [strtime][endtime], if the times is over midnight the results are negative or I can't format the results properly.
Please advised....

082003, 02:27 #5Registered User
 Join Date
 Aug 2003
 Location
 Delft, The Netherlands (EU)
 Posts
 447
Re: "AGAIN" (text delimted numbers)
I assume your time is formatted like hhnn (n = minutes). If not, convert it into such a string in the first place, eventually add trailing zeros.
Convert it into a time variable in the second step using the function TimeSerial()
Make a complete datetime of it by adding your time to your date.
Now, you may determine differences between two datetimes in each date unit using DateDiff
Were you looking for this answer?Make everything as simple as possible, but not simpler!  A. Einstein
DB Problems? DB Explorer, BTrieve Reengineering, DB Conversions & ETL? Conversion Tool

082003, 03:36 #6Registered User
 Join Date
 Aug 2003
 Posts
 64
Response:
Good suggestion, but could you give an example from the illustration I have above, Thanks.

082103, 05:22 #7Registered User
 Join Date
 Aug 2003
 Location
 Delft, The Netherlands (EU)
 Posts
 447
Re: Response:
Originally posted by comf36
Good suggestion, but could you give an example from the illustration I have above, Thanks.
Sorry, I answered you, but my message wasn't sent appearently. I saw you opening another thread about the same issue, so here is my answer again:
Your time field is numeric, right? It contains the digits of hours and minutes in 24 h format, right? I recommand the following transformation:
Let's call your field f.
1) transform it into a string s of the format "hhnn" by
Let s = format(f, "0000")
2) Convert s into a time variable t with
Let t = TimeSerial(val(left(s,2)), val(right(s,2)),0)
3) form a datetime variable dt by adding your time value to a date value d:
Let dt = d + t
You can also combine these transformations like
d + TimeSerial(val(left(format(f, "0000"),2)), val(right(format(f, "0000"),2)),0)Make everything as simple as possible, but not simpler!  A. Einstein
DB Problems? DB Explorer, BTrieve Reengineering, DB Conversions & ETL? Conversion Tool

082103, 18:15 #8Registered User
 Join Date
 Aug 2003
 Posts
 64
Reply
Yes I did post this question twice because I was not sure how to ask,
Thanks for your patients.
Your example did work also, but I need to include seconds from your expression. It's only showing hh and nn.
Also using two fields with the same example how would I determine the total elapsed time.
It's giving me the time when it's finishes I only need the process time like,
(MYTIME)(THEIRTIME)
23:18:1723:18:21=00:00:04 but the results are 22:01:51 not sure how to fix this?
Please advised....

082203, 05:13 #9Registered User
 Join Date
 Aug 2003
 Location
 Delft, The Netherlands (EU)
 Posts
 447
Re: Reply
1) Using seconds: I assumed that AS/400 does not deliver seconds. In case it does, enhance the format command from 4 to 6 zeros, and replace the left() and right() by the mid(s, <Start = 1 for hh, 3 for nn, 5 for ss>, 2)
2) Determining the difference:
Use DateDiff("s", time1, time2) to get the number of secondsMake everything as simple as possible, but not simpler!  A. Einstein
DB Problems? DB Explorer, BTrieve Reengineering, DB Conversions & ETL? Conversion Tool