Update timestamp in datetime2

Question

We have a software designed to run on MS SQL 2005, we have upgraded the database to 2008 R2, and are trying to increase accuracy in our timestamp by using Datetime2 instead of Datetime. The software running is still unchanged, and will have the accuracy of Datetime. But there is a extra field updated with correct ms.

So my question is; Are there any best practice for a trigger that takes the datetime2, and replace the MS-part with the correct value? I was hoping to avoid the cup-demanding convert to string and back again but has not been able to find a good function for this.

I'm not sure what "cup-demanding" is meant to mean. I'm also not sure how you intend for the trigger to know what the correct ms value **is**. Could you elaborate on that part? — Damien_The_Unbeliever, Mar 14 '14 at 09:35
Hi, I know CPU-demanding is not a good formulation on my part. What I mean is less cpu-demanding than my initial thought, converting timestamp to a string, replacing the MS column and converting back to datetime2. As I described in the query, the trigger will know the correct timestamp from a seperate column that contains a integer with correct ms. — user3419063, Mar 14 '14 at 09:58
I was trying to figure what word `cup` was meant to be. didn't think of just a transposition of letters :-| — Damien_The_Unbeliever, Mar 14 '14 at 10:20

score 0 · Accepted Answer · answered Mar 14 '14 at 10:03

This seems to do the job:

create table T (ID int not null, Stamp1 datetime2 not null,
                RealMillis int not null);
go
create trigger TT on T
instead of insert
as
    set nocount on;
    insert into T(ID,Stamp1,RealMillis)
    select
        ID,
        DATEADD(millisecond,RealMillis - DATEPART(millisecond,Stamp1),Stamp1),
        RealMillis --or 0 now that it's been used?
    from inserted;
go
insert into T(ID,Stamp1,RealMillis) values (1,'2014-03-14T10:01:29.343',344);
go
select * from T;

I chose to do it as an instead of trigger since it avoids having to write the wrong data to the table in the first place.

Update timestamp in datetime2

1 Answers1