CODEWITHSUNDEEP
c#sql-serverdatetimeasp.net-web-apijson.net
adam0101
adam0101

Reputation: 30995

How do I globally serialize and deserialize Date vs DateTime when stored as UTC?

In my SQL Server database, I store all DateTime values as UTC. There are some instances, though, where I do not care about time like when a user in some random timezone selects a date using a DatePicker. In these cases, it seemed to make more sense to just store as Date vs DateTime.

When getting the dates out of the database and sending them down to my Angular app through Web API, I wanted to ensure all my DateTime values were formatted in a way so that Angular would know they were UTC dates and display as local time so I added this to Web API to add the "Z" at the end:

// Set all dates to UTC
config.Formatters.JsonFormatter.SerializerSettings.Converters.Add(new IsoDateTimeConverter
{
    DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
});

This works, but the problem is that this conversion is also being applied to my Date values as well. When a date is pulled from the database into C#, they are in DateTime variables as C# doesn't have a Date variable so they get a time of midnight. So if a user in CST picks a date of 11/24/2018, when it goes round-trip to the database and back, it becomes 11/23/2018 (with the time of 6:00pm truncated).

How can I prevent this behavior? Some ideas could be:

  1. Conditionally apply the IsoDateTimeConverter?
    • I don't think this is possible globally since I wouldn't be able to tell if it was a Date or DateTime value stored in the C# DateTime variable.
  2. Change the datatype in SQL Server to DateTime?
    • I don't think this would work either because even if I stored the time based on where the data originated, couldn't it still be wrong for users in other timezones?

Upvotes: 1

Views: 1234

Answers (1)

adam0101
adam0101

Reputation: 30995

Using @John's suggestion, I created a custom IsoDateTimeConverter:

public class DateConverter : IsoDateTimeConverter
{
    public DateConverter()
    {
        DateTimeFormat = "MM-dd-yyyy";
    }
}

And manually applied it to each Date value which overrides the global converter I added to the Web API config.

[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }

I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.

A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111

Upvotes: 2

Related Questions