Parsing JSON dates that end in T24:00:00Z

Question

I'm hitting an API which is returning to me ISO dates using 24:00:00 as the time portion of the datetime.

I'm using JSON.NET, but when I try to parse this datetime, it is currently blowing up. The return from the API is the following:

{
   "endTs":"2015-07-30T24:00:00Z"
}

I would expect to parse this as "2015-07-31 12:00:00AM", but instead I get the following error currently:

Could not convert string to DateTime: 2015-07-30T24:00:00Z. Path 'endTs'...

Answer 1

Update

This is fixed in Json.NET 8.0.1. From the Release Notes:

Fix - Fixed reading 24 hour midnight ISO dates

Original Answer

Since DateTime.Parse() itself throws an exception on a date & time in this format, you could create your own subclass of IsoDateTimeConverter that checks for and handles this case:

public class FixMidnightDateTimeConverter : IsoDateTimeConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(DateTime?) || objectType == typeof(DateTime);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        Type type = (Nullable.GetUnderlyingType(objectType) ?? objectType);
        bool isNullable = (Nullable.GetUnderlyingType(objectType) != null);

        var token = JToken.Load(reader);
        if (token == null || token.Type == JTokenType.Null)
        {
            if (!isNullable)
                throw new JsonSerializationException(string.Format("Null value for type {0} at path {1}", objectType.Name, reader.Path));
            return null;
        }

        // Fix strings like "2015-07-30T24:00:00Z"
        if (token.Type == JTokenType.String)
        {
            const string midnight = "T24:00:00Z";
            var str = ((string)token).Trim();
            if (str.EndsWith(midnight))
            {
                var date = (DateTime?)(JValue)(str.Remove(str.Length - midnight.Length) + "T00:00:00Z");
                if (date != null)
                {
                    return date.Value.AddDays(1);
                }
            }
        }

        using (var subReader = token.CreateReader())
        {
            while (subReader.TokenType == JsonToken.None)
                subReader.Read();
            return base.ReadJson(subReader, objectType, existingValue, serializer); // Use base class to convert
        }
    }
}

Doing this at the converter level avoids problems that might arise with modifying data inside string fields.

Then register it as a global converter or pass it to JsonConvert directly.

Answer 2

Sadly this is one of those cases where the .NET framework doesn't match with the ISO standard. ISO 8601 allows for time values with 24 in the hour to indicate the end of a day, but the DateTime object doesn't know how to deal with this.

This means that you are going to have to do some string manipulation to fix the JSON data before you parse it.

You have two basic options:

1. Set the time portion of the string to 23:59:59, introducing a 1-second error into the value but keeping the "end of day " nature of the value

2. Adjust the whole string to midnight on the following morning.

Assuming that the format remains consistent, the first one is simple enough:

jsonSrc = jsonSrc.Replace("T24:00:00Z", "T23:59:59Z");

The second option is a little more complex. You need to locate any offending dates, parse them out and update the string with valid values. Timestamps with 24:00:00 will have a day added and the time zeroed.

Here's some code to achieve that with the specific format you showed:

public static string FixJSONTimes(string source)
{
    string result = source;
    // use Regex to locate bad time strings
    var re = new Regex("\"([\\d-]+)T24:00:00Z\"");
    foreach (Match match in re.Matches(result))
    {
        // parse out date portion of string
        // NB: we want this to throw if the date is invalid too
        DateTime dateVal = DateTime.Parse(match.Groups[1].Value);

        // rebuild string in correct format, adding a day
        string rep = string.Format("\"{0:yyyy-MM-dd}T00:00:00Z\"", dateVal.AddDays(1));

        // replace broken string with correct one
        result = result.Substring(0, match.Index) + rep + result.Substring(match.Index + match.Length);
    }

    return result;
}

So for the input "endTs":"2015-07-30T24:00:00Z" this will return "endTs":"2015-07-31T00:00:00Z". Will also handle month and year rollover correctly since the day offset is calculated via the DateTime value.

Of course this is a simple case that will only work for the specific format you've specified. ISO 8601 strings have a complex format that includes too many edge cases for any sane regex to handle, including decimal fractions for hours, minutes or seconds. Not many programs I know of use fractional hours though, thank Ghu.

---

So just for fun I figured I'd try to compose a regex that validates and decomposes any valid ISO 8601 Datetime string. Here's the pattern:

@"(?<date>(?<yr>\d\d\d\d)(?:(-?)(?<mon>(?:0[1-9]|1[012]))(?:\1(?<day>0[1-9]|[12]\d|3[01]))?|(-?)W(?<wk>0[1-9]|[1-4]\d|5[0-3])(?:\2(?<wkd>[1-7]))?|-?(?<yrd>[0-3]\d\d))?)(?:T(?<time>(?<hh>[01]\d|2[0-4])(?:(:?)(?<mm>[0-5]\d)(?:\3(?<ss>[0-5]\d))?)?(?<fff>[,.]\d+)?)(?<timezone>Z|[+-](?:(?<tzh>[01]\d|2[0-4])(?<tzm>:?[0-5]\d)?))?)?"

Or if your prefer something broken out, indented and commented for a little clarity:

(?<date>
    (?<yr>\d\d\d\d)(?# Year is always included)
    (?:
        (?#Month with optional day of month and separator)
        (-?)(?<mon>(?:0[1-9]|1[012]))
        (?:\1(?<day>0[1-9]|[12]\d|3[01]))?

    |(?#or...)

        (?#Week of year with optional day of week and separator)
        (-?)W
        (?<wk>0[1-9]|[1-4]\d|5[0-3])
        (?:\2(?<wkd>[1-7]))?

    |(?#or...)

        (?#Day of year, separator mandatory)
        -?(?<yrd>[0-3]\d\d)
    )?
)

(?:T
    (?<time>
        (?#Required hour portion)
        (?<hh>[01]\d|2[0-4])
        (?:
            (?#Optional minutes and seconds, optional separator)
            (:?)(?<mm>[0-5]\d)
            (?:\3(?<ss>[0-5]\d))?
        )?

        (?#Optional fraction of prior term)
        (?<fff>[,.]\d+)?
    (?# end of 'time' mandatory group)
    )

    (?<timezone>
        (?#Zulu time, UTC+0)
        Z

    |(?#or...)

        (?#Numeric offset)
        [+-](?:
            (?#Hour portion of offset)
            (?<tzh>[01]\d|2[0-4])
            (?#Optional Minute portion of offset)
            (?<tzm>:?[0-5]\d)?
        )
    )?
)?

Now you have two problems. :P