Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Julian Time Conversion

My data is imported from JDE. I have 2 separate columns for Date and Time. 

Eg:

Date: 121195

Time: 224012

 

How can I convert Time from Julian format to HH:MM:SS format? I want it as a separate column rather than a measure.

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    you may try

    CONVERT ( TableName[Date], DATETIME ) + CONVERT ( TableName[Time], DATETIME )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That didn't work, unfortunately.

       

      What I want is to fill the '?'. But what I'm getting is this Calculated Datetime.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I have a question to confirm . What does 121195 represent ? What does 224012 represent ? 

    If the time format you want , what should be this time according to the data you provided ? You need to provide specific calculation logic so that we can provide the corresponding formula.

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Data imported from JDE shows Date and Time in Julian format as separate columns

      For Example:

      I have the date as 119200 and I have another column with time stored as 161140.  I have managed to convert the date and I've got 19 July 2019 after conversion.

      I used Power Query to get the Date:

       

      #"Calendar Date Updated" = Table.AddColumn(#"Calendar Promised Ship", "Date Updated Cal", each #date(1900+Number.IntegerDivide([Date Updated], 1000),1,1)+#duration(Number.Mod([Date Updated], 1000)-1,0,0,0))

       

       

       

      I'm not sure about how I can get time to be converted from the Julian format of 161140 to standard HH:MM:SS. I don't know what Julian time 161140 corresponds to in standard format.