Formatting .NET JSON dates to DataWeave datetime objects

When working with date and time formats and JSON, you probably already mentioned that there are a lot of different formats that are being used. Technically none of these is incorrect because the JSON standard does not specify how a date should be formatted. Let’s have a look how we can translate the .NET format to DataWeave datetime object.

Please note that while writing this blog, Mule 3.9.x was being used. This means that the DataWeave code in this blog is compatible with DataWeave 1.0.

Which format does DataWeave expect

As mentioned, the JSON standard does not specify how a date should be formatted. However, when casting a JavaScript Date object to a JSON representation will display the date in the ISO 8601 format.  This format will look something like ‘2019-04-24T11:28:21Z’. This is also the format being used by the DataWeave datetime object.

The .NET JSON date format

The date format which is used in some (older) .NET applications looks totally different. The same date and time as mentioned in the previous paragraph will look something like ‘/Date(1556105301000)/’ (without a time zone offset) or ‘/Date(1556105301000+0000)/’ (when a time zone offset has been specified). The numeric value of this date is the Epoch timestamp in milliseconds, so the amount of milliseconds that have elapsed since 01-01-1970 00:00:00.

Transformation in DataWeave

Now that we know what the date formats on both sides of the transformation look like, we can start writing a DataWeave function that can be used for easy formatting. Our goal was to have the transformation completely in DataWeave, so without any Java classes/functions in between. You can find the function parseDate in the below DataWeave file. This file can also be used to test the function.

%dw 1.0
%output application/json
// Some test values
%var after1970WithoutTimezone = "/Date(1556105301000)/"
%var after1970PositiveOffset = "/Date(1556105301000+0100)/"
%var after1970NegativeOffset = "/Date(1556105301000-0600)/"
%var before1970WithoutTimezone = "/Date(-6247821000)/"
%var before1970PositiveOffset = "/Date(-6247821000+0100)/"
%var before1970NegativeOffset = "/Date(-6247821000-0600)/"
// Function to parse a .NET date format to a DataWeave datetime object.
// The below types of .NET date formats are supported. The epoch value is expected to be specified in milliseconds.
//	 - /Date(epoch)/
//	 - /Date(epoch[timezone])/
// When no timezone has been provided, "+0000" will be used.
// When no, and empty, or an invalid value has been provided, "null" will be returned.
%function parseDate(input) (
	(
		using (
			// Retrieve content between "/Date(" and ")/" characters.
			dateValue = (( input splitBy /(\/Date()|()\/)/ )[1])
		) (
			(
				using(
					// Split date content in epoch and timezone values, and identifiers whether these values are negative (-) or positive (+).
					epochAndTimezone = dateValue match /(+|-{0,1})(\d{0,})(+|-{0,1})(\d{0,})/
				) using (
					// Concat epoch value and positive/negative identifier to get epoch value
					epoch = (epochAndTimezone[1] ++ epochAndTimezone[2]) as :number,
					// Concat timezone value and positive/negative identifier to get timezone value. When not provided, "+0000" will be used as default.
					timezone = (
						(
							epochAndTimezone[3]
							when epochAndTimezone[3] != ""
							otherwise "+"
						)
						++
						(
							epochAndTimezone[4]
							when epochAndTimezone[4] != ""
							otherwise "0000"
						)
					)
				) (
					// Convert epoch to datetime object and convert to specified timezone.
					epoch as :datetime { unit: "milliseconds" } >> timezone
				)
			)
			when dateValue != null and ( dateValue matches /(+|-{0,1})(\d{0,})(+|-{0,1})(\d{0,})/ )
			otherwise null
		)
	)
	when input != null and input != ""
	otherwise null
)
---
{
	beforeFormat: {
		after1970WithoutTimezone: after1970WithoutTimezone,
		after1970PositiveOffset: after1970PositiveOffset,
		after1970NegativeOffset: after1970NegativeOffset,
		before1970WithoutTimezone: before1970WithoutTimezone,
		before1970PositiveOffset: before1970PositiveOffset,
		before1970NegativeOffset: before1970NegativeOffset
	},
	afterFormat: {
		after1970WithoutTimezone: parseDate(after1970WithoutTimezone),
		after1970PositiveOffset: parseDate(after1970PositiveOffset),
		after1970NegativeOffset: parseDate(after1970NegativeOffset),
		before1970WithoutTimezone: parseDate(before1970WithoutTimezone),
		before1970PositiveOffset: parseDate(before1970PositiveOffset),
		before1970NegativeOffset: parseDate(before1970NegativeOffset)
	},
	emptyAndIncorrectFormats: {
		null: parseDate(null),
		empty: parseDate(""),
		incorrect1: parseDate("This is not a valid date"),
		incorrect2: parseDate("/Date(And this is not a valid date either)/")
	}
}

As you can see, null- empty and invalid formatted values will return a null value. Also, the function can cope with values both with and without the time zone specification. When no time zone was specified the default UTC (+0000) time zone will be used. Also, the function will take both positive (dates on/after 01-01-1970) and negative (dates before 01-01-1970) epoch values into account.

When executing the provided DataWeave file (for example using the Preview function in Anypoint Studio), the output will be as below:

{
	"beforeFormat": {
		"after1970WithoutTimezone": "/Date(1556105301000)/",
		"after1970PositiveOffset": "/Date(1556105301000+0100)/",
		"after1970NegativeOffset": "/Date(1556105301000-0600)/",
		"before1970WithoutTimezone": "/Date(-6247821000)/",
		"before1970PositiveOffset": "/Date(-6247821000+0100)/",
		"before1970NegativeOffset": "/Date(-6247821000-0600)/"
	},
	"afterFormat": {
		"after1970WithoutTimezone": "2019-04-24T11:28:21Z",
		"after1970PositiveOffset": "2019-04-24T12:28:21+01:00",
		"after1970NegativeOffset": "2019-04-24T05:28:21-06:00",
		"before1970WithoutTimezone": "1969-10-20T16:29:39Z",
		"before1970PositiveOffset": "1969-10-20T17:29:39+01:00",
		"before1970NegativeOffset": "1969-10-20T10:29:39-06:00"
	},
	"emptyAndIncorrectFormats": {
		"null": null,
		"empty": null,
		"incorrect1": null,
		"incorrect2": null
	}
}

Now that we have the value as a datetime object, we can cast and parse it to any other DateWeave object type as always.

Good luck!