Table to JSON node


Before writing Data to MongoDB, I am using Table to JSON Node to convert the data into JSON format. Now while doing so, Table to JSON node is converting all Date columns into Strings. Is there a way to write the Date Columns in the DateTime format using Table to JSON Node?

Since JSON doesn't know about dates they have to be converted into strings. If the default format isn't what you need you can simply prepend the Date/Time to String node and select an appropriate format.

Thor, Thanks for your prompt reply. I am quite new to the tool and I am not sure If I understand your suggestion completely. For more clarity I am attaching the screenshot of my flow, highlighting the step where I am converting my table JSON and the output before (Proper Date Format) and after JSON Conversion (Dates coming as String).

Basically, I am looking to get Date Format in the final JSON output highlighted in the red. Is there is a way I can do this?

As I said you can accomplish this with the Date/Time to String node before the JSON to Table node. There you can define the desired format (yyyy-MM-dd in your case). If you replace the original date/time columns with the new string columns your JSON will show the desired values.

The JSON specification does not specify a format for exchanging dates which is why there are so many different ways to do it. JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with JSON date and really JavaScript in general – is that there’s no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:

var jsonDate = new Date(1297246301973);

Then let’s convert it to js format:

var date = new Date(parseInt(jsonDate.substr(6)));

The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .

For ISO-8601 formatted JSON dates, just pass the string into the Date constructor:

var date = new Date(jsonDate);