Posted by Alex Kuznetsov on 12/18/06 14:07

aling wrote:
> Execute following T-SQL within Queary Analyzer of SQL Server 2000:
>
> =======================================
> DECLARE @dTest DATETIME
>
> SET @dTest='2001-1-1 1:1:1:991'
> SELECT @dTest
>
> SET @dTest='2001-1-1 1:1:1:997'
> SELECT @dTest
>
> SET @dTest='2001-1-1 1:1:1:999'
> SELECT @dTest
> =======================================
>
> You get what?
> This is my result which is weird:
>
> 2001-01-01 01:01:01.990
> 2001-01-01 01:01:01.997
> 2001-01-01 01:01:02.000
>
> Then what's the reason of this weird problem?

DATETIME has accuracy of 3 ticks - it does not support 999 ms. The
value is rounded to the nearest supported value.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

• Next in forum: Re: Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition
• Prev in forum: Re: Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition
• Thread view: Re: Weird millisecond part of datetime data in SQL Server 2000

[Reply to this message]