How to handle non-standard Json dates

Question

We've got a SQL table of data that is populated by clients for later processing by a background service. This works most of the time, but it appears that a certain subset of clients has been sending us an unexpected date format, which is resulting in JSON parsing errors. We're expecting dates of the format:

2018-01-20T13:42:43.1040000Z

but what we're getting from these clients is:

2018-01-20T3:42:43.1040000 p. m.Z

First prize would be to find a standard JSON parser or format string that knows how to convert these date strings properly. Second prize would be to write an update script for SQL that converts these strings into something usable. Otherwise, we're going to be writing scripts to parse these dates as strings and then massage them until they're valid, which is nasty.

Anyone know any standard parsers, or can suggest a date format string that will be able to interpret these weird date strings correctly?

You want to create a Custom String Converter. Instructions are [here](https://stackoverflow.com/questions/8639315/how-to-create-a-json-net-date-to-string-custom-converter) — Daniel, Jan 21 '18 at 12:47

score 3 · Accepted Answer · answered Jan 21 '18 at 13:03

You can set the DateFormatString of the JsonSerializerSettings object to customise the date formatting. However, as you also have a peculiar AM/PM designator, you also need to set the culture. You could create your own but for these purposes we can steal from the Catalan culture as that seems to match. Given that, we can use a format string like this (see here to understand what each part means):

yyyy-MM-dd'T'h:m:s.fffffff tt'Z'

So for example, this should work.

var catalan = System.Globalization.CultureInfo.CreateSpecificCulture("ca-FR");

JsonSerializerSettings settings = new JsonSerializerSettings
{
    DateFormatString = "yyyy-MM-dd'T'h:m:s.fffffff tt'Z'",
    Culture = catalan
};

var result = JsonConvert.DeserializeObject<YourObject>(json, settings);

@Crowcoder yes, but I'm assuming OP will be able to determine which of his clients are sending this format. — DavidG, Jan 21 '18 at 14:13

How to handle non-standard Json dates

1 Answers1