CODEWITHSUNDEEP
performancesql-server-2008dateindexing
LazyProgrammer
LazyProgrammer

Reputation: 197

SQL Server 2008 search for date

I need to search rows entered on a specific date.

However the datatype of column I need to search on is datetime, and the datatype of argument is Date.

I can use the the query like

Select result 
from table 
where
     convert(date, Mycolumn) = @selectedDate

but this would affect the SARGability of the query and will not use indexes created on mycolumn.

I was trying to use the following query:

Select result 
from table 
where  
  Mycolumn 
       BETWEEN @selectedDate AND Dateadd(s, -1, Dateadd(D, 1, @selectedDate))

However this does not work since the @selectedDate is Date type and a second can't be added or removed.

Can someone help me with a working query?

Thanks.

Upvotes: 0

Views: 665

Answers (3)

Tim Jefferson
Tim Jefferson

Reputation: 21

It is my understanding that using:

convert(date, Mycolumn) = @selectedDate

is SARGable. It will use the index on Mycolumn (if one exists). This can easily be confirmed by using the execution plan.

Upvotes: 2

marc_s
marc_s

Reputation: 754398

If you need to do these searches a lot, you could add a computed, persisted column that does the conversion to DATE, put an index on it and then search on that column

ALTER TABLE dbo.YourTable
ADD DateOnly AS CAST(MyColumn AS DATE) PERSISTED

Since it's persisted, it's (re-)calculated only when the MyColumn value changes, e.g. it's not a "hidden" call to a stored function. Since it's persisted, it can also be indexed and used just like any other regular column:

CREATE NONCLUSTERED INDEX IX01_YourTable_DateOnly ON dbo.YourTable(DateOnly)

and then do:

 SELECT result FROM dbo.YourTable WHERE DateOnly = @SelectedDate

Since that additional info is stored in the table, you'll be using a bit more storage - so you're doing the classic "space vs. speed" trade-off; you need a bit more space, but you get more speed out of it.

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21756

Select result 
from table 
where  
    Mycolumn >= @selectedDate 
AND Mycolumn < Dateadd(D, 1, @selectedDate)

Upvotes: 0

Related Questions