It was surprising when the value of time (millisecond part) changed, when you use insert/update or select .Here is snap of update statement. The question comes in our mind, why millisecond part of datetime value changed ?
This kind of issue you face only with datatype Datetime not with other datatypes like datetime2 or datetimeoffset.
The reason behind this is accuracy level of these datatypes. The datatype Datetime is having an accuracy of one three-hundredth second, or 3.33 milliseconds. That’s why values are rounded to increments of .000, .003, or .007 milliseconds. Whereas datetime2 is having accuracy level of 100 nanoseconds.
Datatype datetime:
| Accuracy | Rounded to increments of .000, .003, or .007 seconds /3.33 milliseconds |
Datatype datetime2:
| Accuracy | 100 nanoseconds |
Here is sample output of insert query :
/*Creating Table with column datatypes datetime and datetime2 */
/*Inserting Values in this table with different time */
/*Here you get output */
