The correct way to get dates between dates in SQL Server

Question

I have a table on an SQL server that has a DATETIME field named Date_Printed. I am trying to get all records in a table that lie between the specified date range. I am currently using the following SQL

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-01-01'
SET @EndDate = '2010-06-18 12:59:59 PM'


SELECT * FROM table WHERE Date_Printed BETWEEN @StartDate AND @EndDate

I have an index in a Date_Printed column. I was wondering if this is the best way to get the rows in the table that lie between these dates or if there is a faster way. The table now has about 750,000 entries, and it will continue to grow. The request is pretty quick, but I would like to do it faster if possible.

Answer 1

I would recommend you use:

 DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @StartDate = '2010-01-01' SET @EndDate = '2010-06-19' SELECT [someColumn],... FROM table WHERE Date_Printed >= @StartDate AND Date_Printed < @EndDate 

I do not know what could happen in this last second of the day!

Answer 2

Ideas for optimizing this query:

• Make the index in Date_Printed a clustered index. However, this can slow down other queries and DML.
• Explicitly specify the columns if you do not need all the columns in the table. (And even if you do, it’s best to make a list of columns.) This will reduce the amount of traffic sent back to the caller of the request.
• If you do not create an index in Date_Printed as a clustered index, and you can restrict the columns you require, then create a coverage index for this query. A coverage index is one where all the columns needed for a query are included in the index. An alternative to SQL Server 2008 is to use the include option to include columns in the index without indexing those columns. In other words, the index is only for date_printed, but includes other returned columns.

Some of the ideas above may include trade-offs at the speed of another DML and increased storage space.

Answer 3

There is nothing you can do here except to have an appropriate index. Well, get rid of the "*" in select - enter the fields and do not request all the fields (data transfer limit).

This is basically it. If you have a pointer to Date_Printed, as you say, then this is for this query. Higher performance - a better machine, mostly faster disks, more RAM for caching.

Answer 4

If you are using Microsoft SQL Server, this is a good way to check how efficient the query to start SQL Server Profiler (from tools) is. Then when you start the request, you should see that your request is called with information about how effective it is. You can also export your log from the profiler as an XML file to the Database Engine Tuning Advisor, which can help you create indexes in your table.

Another method is to include the SET of your STOTISTICS IO ON query in your query, then in the messages you will get a list of tables (1 in your case) and the number of reads, etc., executed on request

Another is to include an execution plan in the results by clicking the “Include Current Execution Plan” button in SQL Server Management Studio. It can be a bit complicated, but you should tell if your index is used or not.

Answer 5

In MS SQL Server you can try using DATEDIFF

 SELECT * FROM table WHERE DATEDIFF(dd, @StartDate, Date_Printed) > 0 AND DATEDIFF(dd, Date_Printed, @EndDate) > 0 

Not sure how effective this comparison with BETWEEN . Just throw it there.