1 Reply Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Milliseconds To Date Time

    Rusty_Haley _

      I have a system generated file where part of the file name is a string of numbers.  The numbers represent the number of milliseconds between 1 Jan 1970, 00:00 and the time the file was created.  An example would be SAP1074785283487.IDOC.  The operating system creation date/time stamp for the file is 1/22/2004 10:28AM. 

       

      I am looking for a way to convert this string of numbers into date and time fields in Monarch 6.01.  I can parse out the string of numbers and convert the string into a numeric value.  I can also calculate the number of days by dividing the number by 86400000.

       

      This results in 12439.64448.  Looking for help getting this number into Monarch date and time fields.

       

      Any thoughts?

        • Milliseconds To Date Time
          Grant Perkins

          Rusty,

           

          Very simple in V7[/b]

           

          Try a calculated field like

           

          {01/01/1970}+(12439.64448)

           

          i.e. add the number of days from your division by the number of millisecs per day calculation.

           

          {01/01/1970}+(/86400000)

           

          The only down side is that you will get the seconds as well and you may need to set the format of the date display!

           

          V6 is not quite so simple but similar.[/b]

           

          You need a CHARACTER field something like

           

          DToC({01/01/1970}(/86400000))"  "+(SecondstoChar(.64448*86400))

           

          using your example.

           

          The first part of the formula gets the date string (then I added a couple of spaces) and the second part gets the hours:mins:secs.

           

          Obviously the fixed value '.64448' is really a variable. In a formula this would, I think, be something like

           

          (/86400000)-INT(/86400000)

           

          although there may well be more elegant variations.

           

          See if that helps at all.

           

          Grant

           

            Originally posted by Rusty_Haley:

          I have a system generated file where part of the file name is a string of numbers.  The numbers represent the number of milliseconds between 1 Jan 1970, 00:00 and the time the file was created.  An example would be SAP1074785283487.IDOC.  The operating system creation date/time stamp for the file is 1/22/2004 10:28AM. 

           

          I am looking for a way to convert this string of numbers into date and time fields in Monarch 6.01.  I can parse out the string of numbers and convert the string into a numeric value.  I can also calculate the number of days by dividing the number by 86400000.

           

          This results in 12439.64448.  Looking for help getting this number into Monarch date and time fields.

           

          Any thoughts? /b[/quote]

           

          [size="1"][ January 27, 2004, 09:16 AM: Message edited by: Grant Perkins ][/size]