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

Belgium Temu Coupon Code ↪ [acj789589] €100 off For New & Existing Customers (16m)

Oman Temu Coupon Code ⇒ [acj889447] OMR100 off For New & Existing Customers (17m)

Bahrain Temu Coupon Code → [act796165] BD100 off For New & Existing Customers (19m)

. How to Save Big on Temu with Coupon Code [act892435] in the USA (3h)

Limited-Time USA Offer: Temu Coupon Code {act892435} for Great Savings (3h)

Temu coupon code $100{act892435}off for USA customers (3h)

Temu Canada Black Friday Sale: 90% Off {acs540599} with Free Shipping 2024 (3h)

Temu Coupon Code $100 off [acr639380] Get 30% Off On Your First Order (4h)

- Advertisement -