How to alter a DateTime field when it is updated?

Question

Is there a database level function (trigger or something) that I can use to alter a DateTime field when new data is inserted? The issue is that a service I am integrating with happens to send out all of its times (in this case the time some info was received on their end) in GMT.

I need to automatically change this to reflect the time in the timezone the db server is in. For example, if they send me 2:34 PM, but I am in NYC, I would want it to be entered in the db as 9:34 AM. This would also have to account for differences in Daylight Savings between GMT and wherever the server is, which seems like a nightmare. Any suggestions?

Also, I am using SQL Server 2005 if that helps.

EDIT:

Let me clarify one thing. The dates going into this column are retrieved in batches every so often (5, 10, 15 minutes), so I think the only way to go is to alter the time once it has been received, not to add a TimeModified field or something. Is that even feasible?

Answer 1

Create a field of the type Timestamp - that will get updated whenever any data is modified in that row. Alas, it's literally a relative timestamp, so it can only be used for versioning. More information can be found here: http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

Answer 2

following example should do the job. Add ModifiedOn or similar DateTime field to your db table.

insert into foo (field1, field2, ...., ModifiedOn)
values (value1, value2,...., GetDate())

or for update

update Foo
set field1 = value1,
    field2 = value2,
    .
    .
    .
    .,

    ModifiedOn = GetDate()
Where ....

Answer 3

You could create

• a default value for the DateTime which gets set when you insert a new record

  CREATE TABLE dbo.YourTable
  ( ........,
    LastModifiedOn DATETIME 
        CONSTRAINT DF_YourTable_LastModifiedOn DEFAULT (GETDATE())
  )
  

• a AFTER UPDATE TRIGGER which sets the DateTime field to the new value whenever you've updated your row

  CREATE TRIGGER trgAfterUpdate
  ON dbo.YourTable
  AFTER UPDATE 
  AS BEGIN
     UPDATE dbo.YourTable
     SET LastModifiedOn = GETDATE()
     FROM INSERTED i
     WHERE i.Table1ID = Table1.Table1ID
  END
  

With the default value and the trigger, your field LastModifiedOn should always be up to date and showing the last modification date/time.

Marc

Answer 4

Another option here would be to use a calendar table where you map a UTC date and time to the local date and time value.

Disadvantages here are a loss in some of the granularity. If seconds are important I would not implement this; you can look at the size of the calendar record and compare it to the size of storing a datetime for every record in your transactional table. Obviously the smaller the volume the less beneficial this solution will be. Also, if you don't build in automatic and unattended repopulating future records in the solution the table will "run out" of records, and you will have left a time bomb for whoever comes in after you (maybe yourself too).

Advantages though are that you will be able to perform any queries on this table much more quickly (because it is an integer). Also if you ever decide that your NYC server needs to move to Sacramento, you can update the "localDateTime" and leave the UTC time in tact.

Table structure (granularity will be up to your needs):
ID int
utc_month int
utc_day int
utc_year int
utc_hour int
utc_minute int
local_month int
local_day int
local_year int
local_hour int
local_minute int

Yet another option (again depending on volume) is to deploy a managed assembly. (see this site for instructions, you do have to make a server configuration change. How to implement a managed udf or sp)

Here is the C# that I put in my udf
public static SqlDateTime udf_ConvertUTCDateTime(SqlDateTime utcDateTime)
{
DateTime dt = utcDateTime.Value;
utcDateTime = dt.ToLocalTime();
return utcDateTime;
}

The following code will return you the converted UTC datetime. Just use that value in your insert or trigger.
Declare @D datetime set @D = GetUTCDate()

select @D

select dbo.udf_ConvertUTCDateTime(@D)

Good Luck!