THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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:

Shivaram,

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

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement