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
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
SEEK:([DATE_SAMPLE].[SAMPLE_DATE] >= Convert([@1]) AND
[DATE_SAMPLE].[SAMPLE_DATE] < Convert([@2])) ORDERED FORWARD)
Execution plan for the second SELECT
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.