Weird millisecond part of datetime data in SQL Server 2000

[aling]

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?

[Alex Kuznetsov]

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/

[Ed Murphy]

Alex Kuznetsov wrote:

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.

Out of curiosity, what are the supported values? The behavior
reported above (991 rounded down, 997 left alone, 999 rounded up)
is still weird - it suggests that the supported values are 3 ticks
apart, but with a 1-tick jump somewhere in the 991-997 range.

[Gert-Jan Strik]

Ed Murphy wrote:

>
Alex Kuznetsov wrote:

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.

Out of curiosity, what are the supported values? The behavior
reported above (991 rounded down, 997 left alone, 999 rounded up)
is still weird - it suggests that the supported values are 3 ticks
apart, but with a 1-tick jump somewhere in the 991-997 range.

It's all in BOL.

"datetime values are rounded to increments of .000, .003, or .007
seconds"

See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9bd1cc5b-227b-4032-95d6-7581ddcc9924.htm
for example 'rounding' with data type datetime and smalldatetime.

Gert-Jan