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.
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.
|--Table Scan(OBJECT:([master].[dbo].[DATE_SAMPLE]), WHERE:(Convert([DATE_SAMPLE].[SAMPLE_DATE])='2003-04-09'))
Therefore, if performance is a consideration then it is best to write your code to make sure it can take advantages of available indexes. Of course if the table you are searching is quite small in the number of records it contains, then possibly the performance gains may not out weigh the simplicity of writing code that uses a function of some kind.
There are always many different methods that can be used to search for records that contain dates and times, and different performance considerations with each. I hope that this article has given you some insight on the different ways to search SQL Server tables, using a date in the selection criteria.
My next article, regarding working with SQL Server date and time variables, will be the last in this series. It will discuss the use of the DATEDIFF, DATEADD, GETDATE and GETUTCDATE functions, and how these might be used in your applications.