How can I get DateTime data from SQL Server ignoring time zone issues?

Question

My situation is that we store data in a SQL Server database, supporting 2005 on up. When a DateTime value is stored, it is in the local time of the client. I need to be able to get that date back on any other client, anywhere, without respect to whatever time zone that other client might be in.

So for instance, when a user in New York enters a DateTime value of "2012-12-20 00:00", I want the user in California to see that very same DateTime value. These DateTime values should not respect the difference in time zones, but that is what I see happening. Currently, SQL Server would deliver that DateTime to the California user as "2012-12-19 21:00". Notice how it's now the previous day because of the -3 hour rollback due to the change from EST to PST (forget about DST issues for the purposes of this conversation).

Getting that data back verbatim, and not translated by time zone, is what I need to accomplish. So what advice can you offer?

Some code to consider:

Here is the table:

CREATE TABLE [dbo].[tblMeterReadings](
[fldMeterReadingsID] [int] IDENTITY(1,1) NOT NULL,
[fldMeterID] [int] NOT NULL,
[fldUser] [varchar](50) NULL,
[fldBy] [varchar](50) NULL,
[fldDateOfReading] [datetime] NULL,
[fldReading] [float] NULL,
[fldIsArchived] [bit] NULL DEFAULT ((0)),

We have a Sql class that does the work, and in the following example "sql" is an object of that class. The call is made using a parameterized query where @data0 is the DateTime object to store in a SQL DateTime field:

sql.Execute(@"INSERT INTO tblMeterReadings (fldMeterID, fldDateOfReading) VALUES (" + MeterID + ", @data0)", Date);

Ultimately, this sets up an SqlCommand, assigns parameters, and fires a command.ExecuteNonQuery() call.

Now, to retrieve the date, I simply select it into a DataTable (again, using our Sql class helper):

DataTable myTable = sql.readDataTable(@"SELECT fldMeterID, fldDateOfReading FROM tblMeterReadings");

So what I see is that in the database itself, the date is "2012-12-20 00:00" as I expect, but when I inspect the myTable contents in debug, I see that the date in that table is "2012-12-19 21:00".

The database was created on a SQL Server that runs on a machine in an EST state. My machine, however, is set to PST. Hence the difference in how the DateTime value is delivered to me in the SELECT.

What am I missing?

Answer 1

Store the UTC date in the database. Only convert it to local time when displaying it in a client.

Server side, use getutcdate() instead of getdate().