question

AdrianN-4768 avatar image
0 Votes"
AdrianN-4768 asked AdrianN-4768 commented

DateTime Discrepancy

Hey All

Just noticed an issue when inserting an object into my database with a DATE Column.
I have an object called a SalesOrder. In my app a sales order has an OrderDate Field which gets filled out automatically via DateTime.Today.

The issue is, when I run a breakpoint at the time of the object being inserted, the DateTime values are correct.

127063-datetime.png

But when I view the database, the field for order date is incorrect. It makes it the day before.
127054-db-row.png




Just wondering if this is caused by my SQL Server being located in US, and it does some sort of time conversion, and would this be fixed if I created my SQL Server and Database to somewhere closer.

Thanks in advanced!

dotnet-xamarinazure-sql-database
datetime.png (17.9 KiB)
db-row.png (1.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Add the code that inserts the row (as code, not image).

0 Votes 0 ·

Hey Dan

I usually use DateTime.Today to store Dates within the database.
My SQL Column is generally just DATE instead of DATETIME.

0 Votes 0 ·

1 Answer

KalyanChanumolu-MSFT avatar image
0 Votes"
KalyanChanumolu-MSFT answered AdrianN-4768 commented

@AdrianN-4768 Thank you for reaching out.

It seems like the machine where the code is being debugged and the server where the database is hosted are in different time zones/locales.
This should get corrected if you match the time zone settings on both machines.

However, the recommended way of storing time stamps is to convert them to UTC first and then store in the database.
When you retrieve the value, you convert them from UTC into the time zone of the client application.


If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the help!

I generally use DATE as my Column type for storing them into the database however what I'll have to do is use DATETIME to get the hour and minute extension and be able to convert them from UTC to local time zone better.
Thanks!

0 Votes 0 ·