How to query SQL Server table based on a specific date

Question

I have a table in SQL Server 2005 which has three columns:

id (int), 
message (text), 
timestamp (datetime)

There is an index on timestamp and id.

I am interested in doing a query which retrieves all messages for a given date, say '12/20/2008'. However I know that simply doing where timestamp='12/20/2008' won't give me the correct result because the field is a datetime field.

Somebody had recommended using the DATEPART function and pulling the year, month, and day out of timestamp and verifying that these are equal to 2008, 12, and 20, respectively. It looks like this would not use the index I have on timestamp and would end up doing a full table scan.

So what is the best way to construct my query so that I am taking advantage of the index that I have created?

Answer 1

-- avoid re-calculating @MyDate +1 for every row
DECLARE @NextDay DateTime
Set @NextDay = @MyDate + 1

SELECT 
    -- ...
WHERE [timestamp] >= @MyDate AND [timestamp] < @NextDay

Answer 2

The BETWEEN statement can help you.

SELECT *
FROM MyTable
WHERE TimeStamp BETWEEN @Start AND @End;

Start would need to be something like 12:01 am for the day you want messages for, and end would be like 11:59pm for the end of the same day.

Answer 3

The use of two datetime variables has always worked infallibly in my experience. The issue of the resolution seems highly unlikely. The important fact to remember, however, is that a range (of any type) includes both end points. So you can't test using BETWEEN on two dates, because it will include both. Rather use something like

datefield >= @startdate AND datefield < @enddate

The Manual.

C'mon folks - the documentation for this isn't that hard to find. :D

Answer 4

BETWEEN does NOT do >=, <. It does >=, <=, as this code proves:

declare @low datetime
declare @high datetime
set @low = getdate()
set @high = @low+1

select case when @low between @low and @high then 1 else 0 end, 
    case when @high between @low and @high then 1 else 0 end

The result will be 1,1, showing that the = is applied to both bounds.

Answer 5

Assuming @Date is a datetime value for any datetime on the day you want all the messages for, use this

Where [timestamp] >= DateAdd(day, DateDiff(day, 0, @Date), 0) 
  And [timestamp] <  DateAdd(day, DateDiff(day, 0, @Date), 1)

This is much faster than using a CAST, not to mention that when using CAST on datetimes, if you Cast a datetime value which is after noon to an integer,

Declare @MyDate as Datetime
Set @MyDate = '12/25/2008 12:01:00'
Declare @IntVal Integer
Set @IntVal = Cast(@MyDate as Integer) 
Select Cast(@IntVal as DateTime)

it will round UP to the integer representing tne NEXT day's date. The above script will output 12/26/2008