Clever Geek Handbook

How to query a SQL Server table for a specific date

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

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

There is an index by timestamp and id.

I'm interested in making a query that fetches all posts for a specific date, like '12 / 20/2008 '. However, I know just what to do when timestamp = '12 / 20/2008 'won't give me the correct result because the field is a datetime field.

Someone recommended using the DATEPART function and pulling the year, month and day from the timestamp and checking that they are 2008, 12 and 20 respectively. It looks like this would not use the index I have in the timestamp and would end up with a full table scan.

So what is the best way to build my query so that I use the index that I created?

0


source to share


5 answers 


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

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


+1


source


Using two datetime variables has always worked flawlessly in my experience. The issue of a resolution seems unlikely. However, it is important to remember that a range (of any type) includes both endpoints. Thus, you cannot test BETWEEN for two dates, because they will include both. Rather use something like

datefield> = @startdate AND datefield <@enddate



Leadership.

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

+2


source


BETWEEN operator can help you.

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

The start should be something like 12:01 am on the day you want to receive messages, and the end should be something like 11:59 pm at the end of the same day.

0


source


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, indicating that value = applies to both bounds.

0


source


Assuming @Date is the datetime value for any day and time on the day you want to get all messages, 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 CAST, not to mention that when using CAST on datetime, if you pass in a datetime value that is an integer afternoon,

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 rounds UP to an integer representing the date NEXT day. The above script will output 12/26/2008

0


source






More articles:


All Articles