I expect my select to return data. Why not?

CREATE TABLE #Test
(
testdatetime datetime
)

INSERT INTO #Test
(testdatetime)VALUES ('2006-03-02 14:29:24.000')

SELECT * FROM #Test WHERE testdatetime > '2006-03-02 14:29:23.999'
DROP TABLE #Test

Datetime appears to carry miliseconds to three decimal places, but it
does not quite do that.  The details can be found in the
documentation.  Below is from the 2000 version of Books on Line:

datetime

Date and time data from January 1, 1753 through December 31, 9999, to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). Values are rounded to increments of
..000, .003, or .007 seconds, as shown in the table.

Example            Rounded example
01/01/98 23:59:59.999         1998-01-02 00:00:00.000

01/01/98 23:59:59.995
01/01/98 23:59:59.996
01/01/98 23:59:59.997
01/01/98 23:59:59.998         1998-01-01 23:59:59.997

01/01/98 23:59:59.992
01/01/98 23:59:59.993
01/01/98 23:59:59.994        1998-01-01 23:59:59.993

01/01/98 23:59:59.990
01/01/98 23:59:59.991        1998-01-01 23:59:59.990

Roy Harvey
Beacon Falls, CT


Show quoteHide quote

In addition to what Roy already stated you may find these of interest:



http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching


Show quoteHide quote

The FIPS-127 test suite required at least 5 decimal place in the
seconds.  SQL Server does nto meet the spec, but there are manmy toher
products that do.  You might want to look at DB2, for example, which is
a much more conforming product.

Hierarchically arranged attributes...mission impossible?
problem: select Max(date) which is less than actual Max(date) ?
Parameterize table name without constructing dynamic query?
use trigger
An easy query: how to retrieve TOP n values of each <other field here>