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!
android1Hi,
Thanks for your assistance. I'm having a bit of trouble with this. I have stripped out the text using extract. I am left with values such as 453808709000. When I change this column to a decimal number I get 4.53809E+11.
I perform the following DAX formula -> Temp1 = DATE(1970,1,1) + [Column1.DutyTimeFrom]/1000/86400.
[Column1.DutyTimeFrom] contains 4.53809E+11.
i get the following error message -> The value for 'Column1.DutyTimeFrom' cannot be determined. Either 'Column1.DutyTimeFrom' doesn't exist, or there is no current row for a column named 'Column1.DutyTimeFrom'.
- Greg_Deckler
android1 - Can you post a screen shot of your data model so that I can see your columns? The error indicates that it can't find [Column1.DutyTimeFrom]. This could be because you need to put the table name in front of it or that there is a typo or something.
- android1
