Time Zones

By Chris Miller on 17 August 2000 | 0 Comments | 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.

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

overload in sql a server Processs is taking much t (7 Replies)

production issue need to replicate and resolve (4 Replies)

i need to group by adding the quantity (1 Reply)

comparing dates (2 Replies)

Join 2 select Statements (3 Replies)

which is small and which is large data (1 Reply)

add new columns to create script of a database (1 Reply)

Change Row Value as Column Header (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -