THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #7A Datetime Yet Again

Okay one more quick teaser

You have this date '2007-01-01 00:00:00.001'

When adding 1 or 2 milliseconds to that date what will be the result?

SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))

SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))


Published Friday, July 13, 2007 10:34 AM by Denis Gobo
Filed under:



shivaram challa said:

2007-01-01 00:00:00.000

2007-01-01 00:00:00.003

Interesting results. I can see that the first result is rounded to 0, but why?

July 13, 2007 1:58 PM

Hugo Kornelis said:


First, the constant has to be converted to datetime. Precision if datetime is 3 1/3 ms, so 1 ms gets rounded down. Then 1 ms is added - and immediately rounded down again.

The second case starts with the same rounding, but than has to round up after addind 2 ms, yielding jan 1, 2007, 3 1/3 ms after midnight. For string representation, this is the rounded to the nearest ms.

July 14, 2007 3:41 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement