Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    64

    Unanswered: "text-delimited" numbers via AS400

    Recently I had imported AS400 files via ODBC, new with AS400 and ODBC. These files are “text-delimited”.
    How would I format “text-delimited” 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....

  2. #2
    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....

  3. #3
    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....

  4. #4
    Join Date
    Aug 2003
    Posts
    64

    Red face "AGAIN" (text delimted numbers)

    Recently I had imported AS400 files via ODBC, new with AS400 and ODBC. These files are “text-delimited”.
    How would I format “text-delimited” 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....

  5. #5
    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 Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Aug 2003
    Posts
    64

    Response:

    Good suggestion, but could you give an example from the illustration I have above, Thanks.

  7. #7
    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 Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Aug 2003
    Posts
    64

    Talking 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:17-23:18:21=00:00:04 but the results are 22:01:51 not sure how to fix this?

    Please advised....

  9. #9
    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 seconds
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •