The other day I needed to do a data change to add milliseconds to date times just to prevent duplicates. I won’t go over the whole scenario but we needed to make the DateTime different, so I decided to add just 1 millisecond to accomplish my task. When I added the millisecond, the datetime did not change.. What!? Now at this point I could have just said ok let’s just add a second ( for this case 1 second or 1 milliseconds did not matter) But of course, I needed to understand why this wasn’t working, so I added 2 milliseconds and ok that worked… but why doesn’t 1 work. This led me to some investigation and I found that milliseconds are accurate to1/300th of a second so adding 1 is going to round down to zero..
Milliseconds Explained
If you do a DATEADD of 1 milliseconds to a DateTime data type, it will get rounded down to 0. You need to add 2 or more to actually increase milliseconds. I think this comment on this post on MSDN puts it in the perfect words:
“…This has been a big source of misunderstanding for 20+ years — all the way back to Sybase in the 80’s. It has grieved many.”
I searched for a while before I found the answer, so hopefully by me reposting this someone can find the answer faster that I did. It’s weird one for sure. Has this ever happened to you, were you able to understand why?
