Incorrect Time from database table column Date_and_Time, with "datetime" DataType

Question

Incorrect Time from database table column Date_and_Time, with "datetime" DataType

Donald Symmons 3,096

I have an issue with time saved in the database. It does not give accurate time with my local time. If I insert data into the table with DateTime.Now, I thought it should get the time automatically in the local time and insert, so that when it displays, it will show exactly the time an event occurred.
Here is how I inserted the date and time with DataType: datetime

objCMD.Parameters.Add("@LastLogin", SqlDbType.DateTime, 100).Value = DateTime.Now;

OR

cmd.Parameters.AddWithValue("@LastLogin", DateTime.Now);

Then, displaying the date and time I use

timelbl.Text = Convert.ToDateTime(Session["LastLogin"]).ToString();

or when displaying in the Gridview I use

<asp:BoundField DataField="LastLogin" HeaderText="Date and Time" HeaderStyle-Font-Bold="false" />

Please what can I do to make the date and time work according to the real time local time and according to the machine time?

Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2022-12-03T12:09:51.407+00:00

So what time do you see, and what do you expect?
Donald Symmons 3,096 Reputation points

2022-12-03T12:39:20.787+00:00

I see an 8 hour difference with my local time. I expected to see a time same as my local time

2 answers

Your answer

Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2022-12-03T12:09:51.407+00:00

So what time do you see, and what do you expect?
Donald Symmons 3,096 Reputation points

2022-12-03T12:39:20.787+00:00

I see an 8 hour difference with my local time. I expected to see a time same as my local time

Answer 1

Dan Guzman 9,436

I see an 8 hour difference with my local time. I expected to see a time same as my local time

@Donald Symmons , it seems the web server time zone is different than as your local time zone, with a difference of 8 hours. DateTime.Now gets the local time where the code runs. An 8 hour difference is expected if, for example, your machine is Pacific Standard Time and the web server time zone UTC.

As suggested, you could store the datetime value in UTC (neither .NET nor SQL Server datetime types are time zone aware) and then convert the time to the time zone of your choosing for display purposes. Similarly, you could use datetimeoffset instead which will include the offset with DatetimeOffset.Now.

It can be a tricky to determine the end client time zone or offset on the server side to display the time in your local time. For that reason, consider formatting the value in client-side javascript from an ISO-8601 datetime format source.

Donald Symmons 3,096 Reputation points

2022-12-03T14:57:00.58+00:00
I did this and it gave me a minus one hour (-1hour) difference

cmd.Parameters.AddWithValue("@dateandtime", DateTime.UtcNow);

That's an improvement from previous one
Dan Guzman 9,436 Reputation points

2022-12-04T16:47:11.95+00:00

Is your local time currently 1 hour ahead of UTC? DateTime.UtcNow will return the current UTC time regardless of time zone of the machine where it runs. If you retrieve that value and display it without converting to your local time, it will show the stored UTC time. You can see the value in your local time with storedUtcTime.ToLocalTime().
Donald Symmons 3,096 Reputation points

2022-12-04T22:36:50.573+00:00
storedUtcTime.ToLocalTime()

This is how I got the utc time to show in my label control from the database table, but it gave me another huge time difference; very far from my local time. I know the code below isn't correct because it would have given me the actual time in local. Please can anybody show me how it's been done, as I am still learning ?

labelTime.Text = Convert.ToDateTime(Session["LastLogin"]).ToLocalTime().ToString();
Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2022-12-05T22:18:48.677+00:00

So where are you located? And where is the web server located?

Answer 2

AgaveJoe 31,266

It does not give accurate time with my local time.

Datetime.Now gets the current date and time zone on the web server not the client machine.

Please what can I do to make the date and time work according to the real time local time and according to the machine time?

Use UTC time and store the user's time zone.

https://stackoverflow.com/questions/832986/how-to-work-with-time-zones-in-asp-net

Donald Symmons 3,096 Reputation points

2022-12-03T13:05:28.71+00:00
https://stackoverflow.com/questions/832986/how-to-work-with-time-zones-in-asp-net

I just read the article you shared and found that I can use this insert parameter to insert Date and time with UTC time and it will display actual local time for any user regardless of location:

objCMD.Parameters.Add("@LastLogin", SqlDbType.DateTime, 100).Value = DateTime.UtcNow;
AgaveJoe 31,266 Reputation points

2022-12-03T13:19:14.37+00:00

By definition UTC is the same for everyone on the planet. Local time will be offset depending on the time zone and day light savings time.

I just read the article you shared and found that I can use this insert parameter to insert Date and time with UTC time and it will display actual local time for any user regardless of location:

Did your test this theory? Remember the web server has its own time zone setting. Perhaps you using a JavaScript application which runs on the client machine to display the date?

Share via

Incorrect Time from database table column Date_and_Time, with "datetime" DataType

2 answers

Your answer