CODEWITHSUNDEEP
sql-server-2008t-sql
TizzyFoe
TizzyFoe

Reputation: 1499

Manipulate the results of getdate() for databases in different time zones

I have an application with limited support for running in different time zones. Essentially, the application has one database/environment for the west cost, and one for the east cost.

The applications figures the current time using getdate().

We don't have source code, so replacing getdate with a custom function isn't an option. Is there some way over overriding the Getdate function? Or is it possible to configure a different time zone for different databases? What about different instances?

Upvotes: 0

Views: 744

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

SQL Server takes its time directly from the host operating system - it is not timezone-aware and there is no way, on one server, to say this database or this instance is in this timezone, and this other database or other instance is in this other timezone. The only way you would be able to accomplish that is to have completely different servers and turn off any date/time synchronization services (including from the host if they're virtual machines).

You should store UTC time using GETUTCDATE() or SYSUTCDATETIME(). Then you don't need to know which timezone a piece of data came from, and it is always easy to convert it to either of these timezones (or any others you need to support later). ASP.NET, for example, has very extensive timezone support.

You can override whatever the source code is sending using an INSTEAD OF INSERT trigger and ignoring anything where they sent GETDATE(). A quick example:

CREATE TRIGGER dbo.whatever
ON dbo.tablename
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.tablename(col1, col2, date_created)
    SELECT col1, col2, SYSUTCDATETIME() FROM inserted;
    -------------------^^^^^^^^^^^^^^ this overrides what they passed
END
GO

You can also consider the new TIMEZONEOFFSET data type, but since it's not DST-aware, I'm not sure if it is valuable or not for your scenario.

Upvotes: 4

Related Questions