How Can I Change Time Zone of DateCreated Column?

Question

The DB resides in SQL Server 2008 ...

I have a db that resides in a different country, i want the tables DateCreated and DateUpdated to have Date and Time of e.g. Canada/ON/Toronto time zone instead of the time zone of US where db is located.

DateCreated has a default value GetDate() and DateUpdated has the following trigger:

CREATE TRIGGER trigger_name ON table_name
AFTER UPDATE
AS
BEGIN
    UPDATE table_name
    SET dateModified = GETDATE()
    FROM table_name cm JOIN Inserted i 
    ON cm.companyid = i.companyid;
END
GO

Answer 1

To convert a datetime from one time zone to another, either add (destination time zone is east of current time zone) or subtract (westward) the number of hours' difference between the two zones. For example, if the database resides in California (Pacific Time), add three hours to get Toronto time (Eastern Time). No need to adjust for DST because both time zones observe it.

You should consider, as mentioned, converting all of the times to UTC and adjust your applications to convert the times to local time. Then, your database will have the correct times no matter where it is.