In working with milliseconds in a datetime value, I've stumbled across what seems to be a bug. Two examples illustrate the problems I've run into. First, when I execute the following script:
CREATE TABLE #MyTable (
MyID int IDENTITY(1,1) NOT NULL,
MyDate datetime NULL
)
INSERT INTO #MyTable (MyDate) VALUES ('01/01/99 00:00:00.001')
SELECT * FROM #MyTable
I get the following result set:
MyID MyDate
------ ------------------
1 1999-01-01 00:00:00.000
which doesn't show the millisecond value of .001 that I entered.
Second, when I execute the following script:
CREATE TABLE #MyTable (
MyID int IDENTITY(1,1) NOT NULL,
MyDate datetime NULL
)
INSERT INTO #MyTable (MyDate) VALUES ('01/02/99 00:00:00.000')
the following command returns an empty result set:
SELECT * FROM #MyTable WHERE MyDate > '01/01/99 23:59:59.999'
But changing the millisecond value from .999 to .998 causes the query to return the expected row.
Many people don't know this, but according to SQL Server Books Online (BOL), datetime values that reference milliseconds are accurate only to one three-hundredth of a second, or 3.33 milliseconds. In addition, SQL Server rounds values to increments of .000, .003, or .007 milliseconds, as Table 2 shows.
In your first problem, SQL Server is rounding down the millisecond value from .001 to .000. And in your second problem, when you execute the script, SQL Server is rounding up the millisecond value of .999. However, when you change the value to .998, SQL Server rounds down the value to .997, and you get your expected result. (For more information about milliseconds and datetime values, see Kalen Delaney, Inside SQL Server, "Inside Datetime Data," October 2000.)
How do I retrieve records that contain a datetime value if I'm interested only in the date part of the value? For example, how do I retrieve all records for 7/1/2000, regardless of their time value?
You can easily isolate the date part of the datetime value by using the CONVERT function, as in:
SELECT convert(char(27), getdate(), 109)
Sep12 2000 10:59:06:703AM
Isolating the date makes your code easier to read because you don't have to reference the time values in a BETWEEN clause, but use the CONVERT approach with caution. The problem is that you can render indexes useless if your WHERE clause contains a search argument against a column referenced by a function such as CONVERT; the index will have stored data differently from the way the CONVERT function stores the result. Thus, I encourage you to consider the time portions of your data when writing queries. (For more information about milliseconds and datetime values, see Kalen Delaney, Inside SQL Server, "Inside Datetime Data," October 2000.)