How to change the DateTime field when updating it?

Question

Is there a database level function (trigger or something else) that I can use to change the DateTime field when inserting new data? The problem is that the service I'm integrating with is happening to send all of my time (in this case, the time when any information was received at the end) to GMT.

I need to automatically change this to reflect the time in the time zone on which the db server is located. For example, if they send me 2:34 PM, but I'm in New York, I would like it to be entered in db as 9:34. This should also take into account the differences in summer savings between GMT and wherever there is a server that seems like a nightmare. Any suggestions?

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

EDIT:

Let me clarify one thing. The dates included in this column are retrieved in batches so often (5, 10, 15 minutes), so I think the only way to go is to change the time after receiving it, rather than adding a TimeModified field or something else. Is it possible?

Answer 1

You can create

• a default value for DateTimewhich is set when a new record is inserted

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

• a AFTER TRIGGER UPDATE , which sets the field DateTimeto a new value whenever you update a row

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

datetime LastModifiedOn / .

Answer 2

, UTC .

- . , ; . , , . , , "" , , (, ).

, , , ( ). , - , NYC- , "localDateTime" UTC.

( ):

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

( ) . (. , . udf sp)

---

#, udf

public static SqlDateTime udf_ConvertUTCDateTime(SqlDateTime utcDateTime)  
{  
    DateTime dt = utcDateTime.Value;  
    utcDateTime = dt.ToLocalTime();  
    return utcDateTime;  
}

UTC. .

Declare @D datetime
set @D = GetUTCDate()

select @D

select dbo.udf_ConvertUTCDateTime(@D)

Answer 3

Timestamp - , . , , . : http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

Answer 4

. ModifiedOn DateTime db.

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

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

    ModifiedOn = GetDate()
Where ....