Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges - Page 3

Finding First Record of the Month

Sometimes you may want a specific record, although do not know the exact date you need to search for to find it. You may want to find the RECORD number for the first record that was inserted in a given month. Since you don't know what the SAMPLE_DATE date and time might be for the first records, you will need to search for all records in the desired month, and use the TOP clause to return the first one. Here is an example that uses the LIKE Clause to return the first record that has a SAMPLE_DATE in April 2003.

SELECT TOP 1 RECORD FROM DATE_SAMPLE WHERE SAMPLE_DATE 
LIKE 'APR%2003%' ORDER BY SAMPLE_DATE

Note that I have used the ORDER BY clause. The reason for this is due to fact that records in SQL Server are not necessarily stored in order.

Performance Considerations

If you are searching large tables with lots of records, you will most likely index some of the date columns that are commonly used to constrain queries. When a date column is used in a where clause, the query optimizer will not use an index if the date column is wrapped in a function. In addition, using the LIKE clause to search for particular records will keep the query optimizer from using an index thus slowing down how long it takes SQL Server to complete your query. Let me demonstrate.

I have now placed a non-clustered index on column SAMPLE_DATE in the DATE_SAMPLE table called 'SD_IX'. Below there are two different SELECT statements I will be using for my example.

SELECT * FROM DATE_SAMPLE WHERE
SAMPLE_DATE >= '2003-04-09' AND SAMPLE_DATE <'2003-04-10'

SELECT * FROM DATE_SAMPLE WHERE
CONVERT(CHAR(10),SAMPLE_DATE,121) = '2003-04-09'

The first SELECT statement selects records without using a function, while the second select statement uses a CONVERT function. Both SELECT statements return the same results, all the records for '2003-04-09'. By issuing the "SET SHOWPLAN_TEXT ON", we can display the execution plans of each SELECT statement in TEXT format. If you review the execution plans (see below), you can see that the first SELECT statement uses an index seek on index 'SD_IX', while the second one only uses a table scan.

Execution Plan for first SELECt statement

 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), 
OBJECT:([master].[dbo].[DATE_SAMPLE]))
       |--Index Seek(OBJECT:([master].[dbo].[DATE_SAMPLE].[SD_IX]), 
SEEK:([DATE_SAMPLE].[SAMPLE_DATE] >= Convert([@1]) AND 
[DATE_SAMPLE].[SAMPLE_DATE] < Convert([@2])) ORDERED FORWARD)

Execution plan for the second SELECT statement.