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"
Post PatronHi,
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
- Greg_Deckler
Hmm, I creatd a column in a test model called Column1.DutyTimeFrom and was able to interact with it no problem. Only thing I can think of is that perhaps it doesn't really like your table naming convention. Can you right click it and rename it to something simple like "ClickData" and see if that resolves the issue?