Time Zones

By Chris Miller on 17 August 2000 | Tags: Application Design

adz writes "My SQL server is runing in the US and is supporting customers in europe. Trancactions is therefore getting a wrong timestamp using current_timestamp. I don't have the possibilty to change the time on the machine. Is the only way to replace current_timestamp with dateadd(hh,6,getdate) or is there a better way?"


SQL Server stores all datetime information (both datetime and smalldatetime) as the number of intervals since a given epoch date (for example, the number of seconds since January 1 1900 00:00 GMT).

The display of the time is determined by the time zone on the server. A more correct way to display the time would be to either change the time zone on the server or make the client program handle the time conversion instead of SQL Server. SQL Server does not have built-in functions to handle timezone shifts, but it would be reasonably easy to write one in SQL Server 2000 with UDF's or even as a stored procedure in SQL 7.0. The function would need to take the time to be converted and the target time zone as arguments, and would return a shifted datetime.

I think if I was in that situation I'd lean towards changing the client code to correctly handle the time zone and then make SQL Server return all time information as GMT by changing the local time zone.


(Editors Note: You can also see this article for a discussion of a new feature in SQL Server 2000 that might help with this.

Related Articles

Application Locks (or Mutexes) in SQL Server 2005 (7 January 2008)

What I Wish Developers Knew About SQL Server (Presentation) (11 October 2007)

Multiple Active Result Sets (MARS) – Transactions and Debugging (17 June 2007)

Multiple Active Result Sets (MARS) (3 April 2007)

How SQL Server 2005 Enables Service-Oriented Database Architectures (8 September 2006)

Presentation: What I Wish Developers Knew About SQL Server (17 November 2005)

GeoCoding with SQL Server and Google (8 August 2005)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Other Recent Forum Posts

What simple checks can I do? (16h)

Using case statement in where clause (1d)

Combine records based on date in between date columns as a continuous single record (2d)

Need Help in SQL query optimization (5d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (5d)

Suspect Parameter Sniffing? (6d)

See values of a proc when it is called from another proc? (6d)

Varchar() datatype and C# SqlDataReader truncates string (7d)

- Advertisement -