asked on

Does Entity Framework sometimes auto-convert DateTime to EST?

Does Entity Framework sometimes auto-convert DateTime to EST?

My Javascript call is making an MVC call to a C# web method with a date that is set to midnight. But, when I query the database, I see the correct date was added to the DB table, as expected. However, there is time added, and it turns out to be UTC time:

I am on the East Cost of the US and am currently 4 hours behind UTC/GMT.

Here is the format of the data:

2019-11-01 04:00:00.000

I was expecting:
2019-11-01 00:00:00.000

Since, the JSON date/time I am passing in was at midnight.

Is there a setting in Entity Framework which may cause this?

Or, is there a JSON parser doing this?

Thanks

Eduard Ghergu

Hi,
Try to call ToLocalTime() DateTime method before saving the data to DB

curiouswebster

ASKER

I do not want local time. I want a date at midnight to be stored as midnight and not adjusted to be on UTC.

Eduard Ghergu

Ok, so where is the problem? The data that is comming from the UI is correct? The data that it's stored in the db is correct?

curiouswebster

ASKER

The data that is coming from the UI is correct? Yes.
The data that it's stored in the db is correct? No.

The data is stored correctly, but the time is UTC.

The Javascript send the following date to the back-end
11/2/2019 00:00:00

I verify in the Web Method, by logging to a text file, the date received in the web function is:
11/2/2019 00:00:00

But, the date stored in the DB is:
11/2/2019 04:00:00

For the 4 hours' difference from UTC. (I am EST)
The date that gets

Eduard Ghergu

Ok, how do you handle the data received from the UI?

curiouswebster

ASKER

It's simple. I want the date/time stored to the DB exactly as the UI passes it into the web method. I need to uncover if Entity Framework is the cause of this manipulation.

Do you know if EF has preferences which govern such changes?

SOLUTION

Duy Pham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eduard Ghergu

Hi,
I understand what you want to achieve, I need to see the code that you're using to store the data received from the UI to DB. EF is not the quilty party

Raja Jegan R

>> I verify in the Web Method, by logging to a text file, the date received in the web function is:
11/2/2019 00:00:00

Kindly run SQL Profiler trace to confirm whether your application passed 11/2/2019 00:00:00 or 11/2/2019 04:00:00 in the Database call..
That would be best way to narrow down the issue..

curiouswebster

ASKER

Hi, I executed the following:

update milestone set psd1_date='12/1/2019' where project_id=1234
select ms.psd1_date from milestone ms where project_id=1234

Open in new window

and got the return value:
2019-12-01 00:00:00.000

So, the database does not seem to move the time.

curiouswebster

ASKER

> If you still want to do that, maybe you can change to use DATETIMEOFFSET.

This sounds like it may be the problem. My server are based on EST which perfectly explains a UTC offset.

How do I check the value the DB uses for DATETIMEOFFSET?

And how would I change that value?

curiouswebster

ASKER

It can not be the database making this change...

The date is set by the Javascript and passed to the back-end as part of the milestoneParamValue array.

var saveMilestones = 'https://enterpriseservices-web.com/PServices/PService.svc/SaveMilestones';

var client1 = odataClient.createXhrClient(saveMilestones, false).create(milestoneParamValue).success(function (response, status) {

Where a breakpoint in the browser (on createXhrClient above) shows the date has the value:

"/Date(1573016400000)/"

which translates to:
Wed Nov 06 2019 00:00:00 GMT-0500 (Eastern Standard Time)

But...

On the first line of the web method (SaveMilestones):

I write the value of that time variable to a text file. I see it in UTC and not EST.

PSD1_date A: 11/6/2019 5:00:00 AM

So, it seems the change is taking place here:
[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]

Are there preferences for this JSON format?

Eduard Ghergu

Hi,

What the settings for the JSON serializer? If they're not specified, you can override these settings or part of them.

curiouswebster

ASKER

How do I find where those setting are defined?

I use C#.NET and MVC..

I see the following above the web method...

[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]

And how would I over-ride them?

Eduard Ghergu

Hi! Which version of MVC? Which version of .Net Framework?

curiouswebster

ASKER

.NET Framework, 4.5. I am not sure of the MVC version.

I am glad you brought up the topic of JSON settings. I found one which I need to experiment with:

https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_DateTimeZoneHandling.htm

The default, "RoundtripKind", has a value of 3 and means "Time zone information should be preserved when converting"

How do I alter the value of this setting?

Is that possible? I searched my entire C# solution and found no instance of work "Newton", which tells me WebMessageFormat.JSON is a different animal.

Thanks.

Eduard Ghergu

Hi!
You can have a look at:
https://www.danylkoweb.com/Blog/create-a-custom-json-serialization-binder-to-resolve-derived-types-ON

But, WebMessageFormat.JSON is specific to WCF services, so it seems that you're not using ASP.NET MVC, or?

curiouswebster

ASKER

I guess you are right. These are Web Methods. Sorry.

I only see the following namespace....
System.Runtime.Serialization.Json;

and suspect that nothing on that URL you provided can help.

I am just looking for how I can over-ride the default settings for date conversion logic.

Any ideas?

ASKER CERTIFIED SOLUTION

Eduard Ghergu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

Thanks

Eduard Ghergu

You're welcome!