By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Weird millisecond part of datetime data in SQL Server 2000

P: n/a
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?

Dec 18 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

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/

Dec 18 '06 #2

P: n/a
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.
Dec 18 '06 #3

P: n/a
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
Dec 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.