3 Replies Latest reply: May 15, 2014 10:04 AM by Data Kruncher RSS

    Converting Time

    Arich _

      I have a "time" field which is actually a numeric field of mmhhss. It is using a 24hr clock. I want to convert it to a REAL time field. The problem is that my times do not have leading zeros.

       

      So I have times that look like 45 (45 seconds after midnight), 809 (8 minutes 9 secs after midnight) etc.... When I convert to Date only the times that use the full 6 digits are converting correctly.

       

      Is there an easy method/formula for converting all these to a valid TIME format?

       

      -Arich

        • Converting Time
          Data Kruncher

          Hi Arich. Welcome to the forum.

           

          This is an interesting twist to the typical time challenges.

           

          Assuming that the TimeNumber has the value 809, then:

          [SIZE=2]

          ctod(

          substr(right("000000"trim(str(TimeNumber)),6),1,2)":"+

          substr(right("000000"trim(str(TimeNumber)),6),3,2)":"+

          substr(right("000000"+trim(str(TimeNumber)),6),5,2),

          "m/d/y","T")

          /SIZE[/CODE]

           

          will produce a Date/Time type field (formatted for Time) with the value 12:08:09 AM, and the TimeNumber value of 205312 will produce 8:53:12 PM.

           

          Is this what you had in mind?

           

          Kruncher