How can I add exactly 1 millisecond?

Question

select getdate(),DATEADD(millisecond,1,getdate())

yields me same answer....How to add exactly 1 millisecond?

I cannot use a datetime2 field.

Answer 1

You can't. The accuracy of datetime is 3.33 milliseconds.

Date and Time

Answer 2

The resolution of the DATETIME type is insufficient for your needs; per the documentation, it is:

Rounded to increments of .000, .003, or .007 seconds

Answer 3

You'll need to store milliseconds separately if you need that much accuracy. In SQL Server 2005 there is no native date/time type that will allow you to be more precise than ~3 ms. This is why, for example, the last time you can have in a day is 23:59:59.997, not .998 or .999.