Forum Discussion
Post PatronConvert json DateTime format
I was referring to change your query name so that your table name in the data model doesn't have the GUID and such in it and ? and such.
Community ChampionMy understanding of JSON Dates is that it is the number of milliseconds from a reference date of 1/1/1970. Therefore, I would strip out all the text characters, if you need assistance with that, let me know but you could do it with a MID that grabs the first character after the ( and grabs the number of characters up until the ). Make sure it is a number format and then you can use the following DAX:
Date = DATE(1970,1,1) + [JSONDate]/1000/86400
Basically, divide by 1,000 to get seconds and then by 86,400, the number of seconds in a day (you could just divide by 864,000. This gives you the number of days since 1/1/1970 and then you just add that to a date of 1/1/1970. I got back "3/7/2016 1:00:00 PM"
Greg_Deckler This worked like a champ for me! Thanks so much!