# Convert JSON /DATE()/ to Date

• ### 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 Thursday, October 30, 2014 4:32 PM Solved
Thursday, October 30, 2014 12:51 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.

Ola

• Marked as answer by 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.

Ola

• Marked as answer by 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