none
Convert JSON /DATE()/ to Date RRS feed

  • Question

  • I have a imported JSON Table with one DateTime column.

    How can I convert this to a Date?

    //Ola

    STARTDATE
    /Date(1151704800000)/
    /Date(1167606000000)/
    /Date(1167606000000)/
    /Date(1172703600000)/
    /Date(1172703600000)/

    -----

    For the record:

    The long solutions is:
    1. Remove text
    2. convert to numbers
    3. Use Arithmetic formulas, one for each step, and for each column
    =((number/1000)+7200)/84600+25569

    • Edited by OlaSa Thursday, October 30, 2014 4:32 PM Solved
    Thursday, October 30, 2014 12:51 PM

Answers

  • Thanks Curt,

    I copied the code to a blank query and it worked like a charm. Though it will take me some time to learn how to integrate it with my other code (how to call up the function) + to decipher the code.

    Many thanks for your help
    Ola

    • Marked as answer by OlaSa Thursday, October 30, 2014 5:36 PM
    Thursday, October 30, 2014 5:36 PM

All replies

  • Here's some sample code that defines a function which is able to parse this format. If you take the definition of the Json.Date function and add it to your query, you'll be able to use it to add a new column with the actual datetime.

    let Json.Date = (date) =>
        let
            Stripped = if Text.StartsWith(date, "/Date(") and Text.EndsWith(date, ")/") then Text.Range(date, 6, Text.Length(date) - 8) else error "Not a date",
            Position = Text.PositionOfAny(Stripped, {"+", "-"}, 1),
            Parts = if Position < 0 then { Stripped, "0" } else { Text.Range(Stripped, 0, Position), Text.Range(Stripped, Position) },
            NumberParts = { Number.FromText(Parts{0}), Number.FromText(Parts{1}) },
            Result = DateTime.FromText("1/1/1970") + #duration(0, 0, 0, (NumberParts{0} + 36000 * NumberParts{1}) / 1000)
        in
     Result
    in Json.Date("/Date(1151704800000)/")
    Thursday, October 30, 2014 1:17 PM
  • Thanks Curt,

    I copied the code to a blank query and it worked like a charm. Though it will take me some time to learn how to integrate it with my other code (how to call up the function) + to decipher the code.

    Many thanks for your help
    Ola

    • Marked as answer by OlaSa Thursday, October 30, 2014 5:36 PM
    Thursday, October 30, 2014 5:36 PM
  • Hi Curt: Very interesting coding you wrote. Is there a way I can step through all the lines to see how they are working? In the first "if" statement you didn't close the statement after the "else" and then you started a another step. Didn't know that M engine would allow it.

    Friday, April 12, 2019 10:07 AM
  • You can re-write it like so:

    //let Json.Date = (date) =>
        let
            //* Start of debug-parameters
            date = "/Date(1151704800000)/",
            //*/ End of debug-parameters
            
            Stripped = if Text.StartsWith(date, "/Date(") and Text.EndsWith(date, ")/") then Text.Range(date, 6, Text.Length(date) - 8) else error "Not a date",
            Position = Text.PositionOfAny(Stripped, {"+", "-"}, 1),
            Parts = if Position < 0 then { Stripped, "0" } else { Text.Range(Stripped, 0, Position), Text.Range(Stripped, Position) },
            NumberParts = { Number.FromText(Parts{0}), Number.FromText(Parts{1}) },
            Result = DateTime.FromText("1/1/1970") + #duration(0, 0, 0, (NumberParts{0} + 36000 * NumberParts{1}) / 1000)
        in
     Result
    //in Json.Date("/Date(1151704800000)/")
    or see this blogpost: https://www.thebiccountant.com/2016/05/30/analyze-m-functions-step-step/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, April 13, 2019 6:10 AM
    Moderator
  • Thanks Imke. I can work through the steps now. However, in the final  output I can see the below, which doesn't look like a date.

    Query1
    1151704800000
    0



    Saturday, April 13, 2019 7:31 AM