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?"

adz,

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.

rocketscientist.

(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

Compare alpha results to INT after get values from a string (1d)

Query performance Call Center data (2d)

Looking for on Premises tool to read data from SQL logs and populate data warehouse (3d)

Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (3d)

Working with multiple WHERE statements (3d)

Create a new field value that shows a difference in value from 2 fields (4d)

Hierarchy wise Sales Targets (5d)

Get the MaxDate in results (7d)

- Advertisement -