1

I am using Visual Studio 2015 and I have an Asp.NET website application that uses webapi 2 to return data. The System.Web.Http.dll version is 5.2.30128.0.

Lately, I came across this issue: new Date() works differently in Chrome and Firefox. To fix this particular issue I want to add the time zone to the serialized datetime values that come from the sql table. All the users of the app are in the same timezone.

How can I achieve that across the entire app?

The weird problem is that Date.Now or DateTime.Today are converted to json with time zone, however, the values that come from the database (I use sql server 2008R2 with datetime fields) don't have a timezone when converted to json.

I created this sql table:

CREATE TABLE [dbo].[Entity]
  (
    [Id]                  INT IDENTITY (1, 1) NOT NULL
  , [Name]                NVARCHAR(50) NULL
  , [LastModifiedDate]    DATETIME NULL
  , [LastModifiedDate2]   DATETIME2(7) NULL
 )

I added it to EF 6 model:

using System;
using System.Collections.Generic;

public partial class Entity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Nullable<System.DateTime> LastModifiedDate { get; set; }
    public Nullable<System.DateTime> LastModifiedDate2 { get; set; }
}

and I have in the WebApi controller method:

public IEnumerable<object> Get()
{
  //throw new Exception("This is my exception handler");
  //
  TestEntities dbctx = new TestEntities();

  Entity entity = new Entity {Id = 0, Name = "Test", LastModifiedDate = DateTime.Now, LastModifiedDate2 = DateTime.Today};

  var list = dbctx.Entities.OrderBy(x => x.Id).Take(1).ToList();
  list.Add(entity);
  return list;
}

Json output:

[{"Id":1,"Name":"Entity 1111111","LastModifiedDate":"2016-06-05T17:34:28.787","LastModifiedDate2":"2016-06-05T17:38:14.547"}
,{"Id":0,"Name":"Test","LastModifiedDate":"2016-06-05T18:05:14.8194-07:00","LastModifiedDate2":"2016-06-05T00:00:00-07:00"}]

The data returned by the following query:

select top 1 Id, Name, LastModifiedDate, LastModifiedDate2
from dbo.Entity
order by 1

is:

  1 Entity 1111111  6/5/2016 17:34:28.7870000   6/5/2016 17:38:14.5470000

I came across this blog from 4 years ago: http://www.hanselman.com/blog/OnTheNightmareThatIsJSONDatesPlusJSONNETAndASPNETWebAPI.aspx but I was wondering if there is another way in the latest versions of the software. I haven't tried yet the solution proposed in the article.

Update:

I think it is because of the Kind property of the DateTime structure. The values that come from the database have Kind=Unspecified while LastModifiedDate and LastModifiedDate2 have Kind=Local.

Community
  • 1
  • 1
boggy
  • 3,674
  • 3
  • 33
  • 56

1 Answers1

1

I want to add the time zone to the serialized datetime values that come from the sql table.

The reason your datetimes coming from the database do not serialize 'correctly' is because DateTime.Kind is set to Unspecified.

To 'fix' this you need to specify the DateTimeKind for each datetime field you pull out of the database

if(entity.LastModifiedDate != null)
    entity.LastModifiedDate = DateTime.SpecifyKind(entity.LastModifiedDate, DateTimeKind.Local);

The reason Date.Now and DateTime.Today work is because DateTimeKind is set to DateTimeKind.Local for these already

warning you should not be using datetimes in this fashion - instead all your dates should be saved as UTC and then the dates are converted from utc to the browsers local time on the client side. DateTime has no concept of timezone info.

wal
  • 17,409
  • 8
  • 74
  • 109
  • Yes, you are right. I wonder if there is a way to do it globally somewhere. One way would be to use datetimeoffset in the database but I don't particularly like it. – boggy Jun 06 '16 at 01:37
  • store as UTC; by that i dont mean store the timezone... i mean convert all datetimes to Utc time before saving; then when you pull them out set `DateTime.SpecifyKind` to UTC... send them back to your client then *they* do the conversion to the desired timezone – wal Jun 06 '16 at 01:38
  • I am not too sure it would fix my issue. If you run this jsfiddle https://jsfiddle.net/qbce6yjw/1/ you get different results in FF and Chrome. Even though I convert the dates to UTC if I don't include the timezone in the serialized value FF is going to think the date is in the local timezone while it would work in Chrome. – boggy Jun 06 '16 at 01:41
  • always include the timezone – wal Jun 06 '16 at 01:43