Posted by Gert-Jan Strik on 12/18/06 21:04

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

[Back to original message]