milli-second update issue....

[DLu]

Hello:

I have a testing table table_1 with Key1 int as PK and dateField datetime default getutcdate as Alternative key

select * from Table_1
key1 dateField
----- -----------------------
1 2007-03-26 21:13:53.163
2 2007-03-26 21:13:56.913
3 2007-03-26 21:14:04.117
4 2007-03-26 21:14:05.320
5 2007-03-26 21:14:08.193
6 2007-03-26 21:14:08.647
7 2007-03-26 21:14:09.397
(7 row(s) affected)
--testing if the millsecond are part of the unique key
update Table_1 set dateField = '2007-03-26 21:14:08.194' where key1 = 6

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Table_1' with unique index 'AK1_Table_1'.
The statement has been terminated.

I think 2007-03-26 21:14:08.194 is a new value and is unique within the range of field. Why it is accepted at the insertion time while reject updating later?

This verifies that:
1. datatime field could be accurated to milli-second
2. Guess there is a milli-second rounging issue.... thus could not be accurated to milli-second sometimes?

thanks
David

[nosepicker]

Datetime values in SQL Server are rounded to every 3.33 milliseconds. Therefore, the datetime value of '2007-03-26 21:14:08.194' would be rounded to '2007-03-26 21:14:08.193' (the value where key1 = 5). If you change the value to '2007-03-26 21:14:08.195', this will get rounded to '2007-03-26 21:14:08.197', and you should then be able to complete the update.

[DLu]

Hi "nosepicker":

[Rounded to every 3.33 milliseconds].
that probably explains why the last digit of milliseconds I could see will always be 0, 3 and 7 due to the 3.33 rounding mechanism.

p.s. why some datetime field with the millisecond as .000, is there some 'switch' to turn the millisecond part active?

thanks for the info.
David

[skhanal]

That may be because time portion was not specified when column was inserted or updated.

[DLu]

Hello Skhanal:

Thus, the milli-second portion is totally depended on the insert value, there is no 'switch' to turn on this portion.

thanks for the info.
David

[skhanal]

Yes, sometimes you may only insert date value then time will be 12:00:00.000

[DLu]

thanks again.
-D

[nosepicker]

Actually, if you insert only the date portion, the time will be 00:00:00.000 (12:00 AM), not 12:00:00.000 (which is 12:00 PM).