views:

115

answers:

5

Here's the scenario. The column in question is called 'datein' and it's type is 'datetime'. I have three rows with the value of '2009-10-01 00:00:00.000' for 'datein'. Why does this query return the aforementioned rows?

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30 00:00:00.000'
        AND tc.datein <= '2009-09-30 23:59:59.999'

Using

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein BETWEEN '2009-09-30 00:00:00.000'
        AND '2009-09-30 23:59:59.999'

returns the same result

A: 

What is the column type? If it is a datetime, try casting the values you're comparing with to a datetime as well; if it is a string (char, nchar, varchar, nvarchar) what collation etc. are you using for the column?

Lucero
Column type is 'datetime'. I'll add that to the op
GregD
+5  A: 

It's the lack of precision in the thousandths of a second value. Try ".997" instead.

MSDN DateTime documentation

Run this and you'll see:

declare @dt datetime

select @dt = '2009-09-30 23:59:59.999'
select @dt
Austin Salonen
.998 returns the same 3 rows
GregD
@GregD: it's the same lack of resolution. The DateTime time is only good for every 3 or 4 milliseconds.
Joel Coehoorn
Awesome. I've marked your answer as the correct one. Also, thanks a lot for the link.@Joel Thanks for the additional information.
GregD
@Austin Disregard my comment (that I've since deleted). I was casting it to smalldatetime instead of datetime.
GregD
SQL Server 2008's DATETIME2 date type will be accurate to 100ns of precision, so you wouldn't have this problem with DATETIME2
marc_s
@marc_s thanks for that tid-bit. I'm not on 2008 yet.
GregD
A: 

According to MSDN documentation

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.

From the given example in that page, you must end your query with .997 to obtain the results that you expect.

eKek0
+2  A: 

The DATETIME accuracy is 0.00333 seconds. So you need to go to '2009-09-30 23:59:59.998' so it doesn't round up to Oct 1st.

For example:

select '2009-09-30 23:59:59.994', 
  cast('2009-09-30 23:59:59.994' as datetime)
union all select '2009-09-30 23:59:59.995', 
  cast('2009-09-30 23:59:59.995' as datetime)
union all select '2009-09-30 23:59:59.996', 
  cast('2009-09-30 23:59:59.996' as datetime)
union all select '2009-09-30 23:59:59.997', 
  cast('2009-09-30 23:59:59.997' as datetime)
union all select '2009-09-30 23:59:59.998', 
  cast('2009-09-30 23:59:59.998' as datetime)
union all select '2009-09-30 23:59:59.999', 
  cast('2009-09-30 23:59:59.999' as datetime)

returns:

2009-09-30 23:59:59.994 2009-09-30 23:59:59.993
2009-09-30 23:59:59.995 2009-09-30 23:59:59.997
2009-09-30 23:59:59.996 2009-09-30 23:59:59.997
2009-09-30 23:59:59.997 2009-09-30 23:59:59.997
2009-09-30 23:59:59.998 2009-09-30 23:59:59.997
2009-09-30 23:59:59.999 2009-10-01 00:00:00.000
Remus Rusanu
A: 

The safe way to write these queries is as follows:

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30T00:00:00.000'
        AND tc.datein < '2009-10-01T00:00:00.000''
Steve Kass