CODEWITHSUNDEEP
sqlsql-server-2008c#-4.0
Bill
Bill

Reputation: 2382

DateTime values read wrongly from Database

I have data stored on a SQL Server in a country that is GMT + 2. So for instance, a DateTime value is something like 1/1/1800 12:00:00 AM.

Now, I have a C# console app, that is running on a server in UK. The C# is reading those values from the remote database. However, the date received are 2 hours less, for instance: 31/12/1799 22:00:00.

I changed culture to en-US, the only thing that changed was the printing of 10:00:00 PM instead of 22:00:00

Any idea why the date values are being "auto-converted" to locale timezone instead of keeping the values as is?

Thank you Regards

Upvotes: 0

Views: 363

Answers (2)

evenro
evenro

Reputation: 2646

The problem you're experiencing is not related to the culture on your computer, it is related to a conversion of timezones.

When initializing the connection - there's a negotiation happening between you and the server that tells the server the time zone of your location, and does the conversion for the communication automatically for you.

First, try to understand what is stored in the DBMS, and what you want it to store.

Maybe you need to change your DateTime type to one that considers timezones: for example: http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

If you don't want to have timezone "adjustments" try to synchronize the DBMS session to be on the same timezone (between your client and the SQL Server) - so no conversions would occur.

Upvotes: 1

chiastic-security
chiastic-security

Reputation: 20520

There are two things you shouldn't confuse here: (1) how the data is stored in the database; (2) how it is displayed. The database should be storing the information in a timezone-agnostic way; it's up to you how you display it.

This means that there are two things that might be going wrong. Either it's being stored wrong, because the application putting things into the database is not correctly interpreting the date/time that it wants to store, or, more likely, you're converting to local time when you display it.

I'm not sure how you're doing the conversion, but lots of date/time functions will use the default locale if you don't specify one. They will allow you to specify a locale if you want to, though.

If I were you, I'd have a look at the raw data in the database to see whether that's correct.

Upvotes: 0

Related Questions