json serializer convert datetime to any string

Question

The following query which converts the data into json in SQL server:

SELECT top(1) Convert(DATETIMEOFFSET, Convert(DATETIME2(0), [Time])) as "time.$date" 
from [rops].[dbo].[PreOrder] po
where po.RetainDate > getdate() - 1
for json path;

yields:

{"time":{"$date":"2021-01-11"}}

Now we have a C# object with a Time member

PreOrder po = new PreOrder { time = new DateTime("14-6-2021 09:28:49") };

I need it to convert to the same string as the one from SQL server with NewtonSoft JSON serializer.

string jsonString = JsonConvert.SerializeObject(preOrder, settings);

Should become:

{"time":{"$date":"2021-01-11T00:01:57Z"}}

How to convert a Datetime object to any string with newtonsoft json serializer?

Doing this:

class CustomDateTimeConverter : IsoDateTimeConverter
{
    public CustomDateTimeConverter()
    {
        DateTimeFormat = "{ \"$date\": \"yyyy-MM-dd\" }";
    }
}

yields:

{ "time": "{ $date: yyyy-MM-dd }" }

So no quotes around the date and the date stays yyyy-MM-dd without being translated to

Does [This](https://stackoverflow.com/questions/18635599/specifying-a-custom-datetime-format-when-serializing-with-json-net) answer your question? — Izzy, Jun 23 '21 at 12:48
saw that one, but that puts extra quotes around the curly braces — Serve Laurijssen, Jun 23 '21 at 12:56
This is ISO8601 format. You can't simply put a T in between and add a Z at the end — Thomas Weller, Jun 23 '21 at 13:05
Does this answer your question? [Given a DateTime object, how do I get an ISO 8601 date in string format?](https://stackoverflow.com/questions/114983/given-a-datetime-object-how-do-i-get-an-iso-8601-date-in-string-format) — Thomas Weller, Jun 23 '21 at 13:07
datetime.[ToString("o")](https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings) and [that should be the default for json.net](https://www.newtonsoft.com/json/help/html/DatesInJSON.htm) — Thomas Weller, Jun 23 '21 at 13:09
nothing to do with ISO format and T in middle, just the raw output string — Serve Laurijssen, Jun 23 '21 at 13:11

Andrei Tătar · Accepted Answer · 2021-06-23T13:21:17.310

You need to write a custom converter:

https://dotnetfiddle.net/q2Oo28

public class MyDateConverter : IsoDateTimeConverter
{
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        writer.WriteStartObject();
        writer.WritePropertyName("$date");
        base.WriteJson(writer, value, serializer);
        writer.WriteEndObject();
    }
}

and use it like (or add the converted to the serializer settings, if you can't add an attribute to the property):

public class PreOrder
{
    [JsonConverter(typeof(MyDateConverter))]
    public DateTime time { get; set; }
}

will output:

{
  "time": {
    "$date": "2013-01-20T00:00:00Z"
  }
}

json serializer convert datetime to any string

1 Answers1