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.