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

All applications need to retrieve data in SQL Server tables based on DATETIME and/or SMALLDATETIME columns. In your particular application, you may need to select records that were entered on a particular date. On the other hand, you might need to select a set of records that have a DATETIME column value for a particular month, or year. In other case, you might want to find all the records between two different dates. Possibly, you might need to find the first, or last record entered in a given month, day, or year. This article will discuss selecting records from a database table based on values in a DATETIME, or SMALLDATETIME column.

Prior to discussing selecting records for a particular DATETIME value, let's review what specific values are stored in a given DATETIME and SMALLDATETIME column. From my first article in this series you should recall that a DATETIME column contains a date and time value, where time is accurate to milliseconds and SMALLDATETIME columns hold a date and time value, but the time portion is only accurate to one minute. Since these date/time columns store the time portion you will need to consider this when searching for records where the column holds a specific date. You will need to provide the date and time portion in the search criteria or you may not return any records or the records you wish to return. If you are not sure of the exact time associated with the records you want to retrieve you should search based on a date and/or time range. Let's go through a couple of examples to show you what I am talking about.

DATE_SAMPLE Table

In order to show you different methods of searching SQL Server tables, I will need a sample table. The table I will be using is a very simple table called DATE_SAMPLE and here is a list of records in that table.

RECORD      ------------------- 1           2 3 4 5 6 7 8  9  10

SAMPLE_DATE ------------------------------------- 2001-11-08 00:00:00.000 2002-04-08 16:00:00.000 2003-04-12 16:59:00.000 2003-04-09 00:00:00.000         2003-04-09 08:00:00.000 2003-04-09 14:58:00.000 2003-04-09 23:59:00.997 2003-04-10 00:00:00.000 2003-04-12 00:00:00.000 2003-05-10 00:00:00.000

Common Mistakes When Searching for Dates:

When searching for dates there are a number of common mistakes that new SQL Server programmers sometimes make. In this section, I will show you two common date/time pitfalls.

The intent of this first example is to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE equal to '2003-04-09'. Here is the code:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = '2003-04-09'

When this code is run only record 4 is returned. Why are records 5, 6 & 7 not returned? Can you tell why? Remember DATETIME, or SMALLDATE columns contain not only the date but also the time. In this particular example SAMPLE_DATE is a DATETIME column, so all the dates store contain a time, down to the milliseconds. When you specify a search criteria that only contains a date, like the above example, SQL Server needs to first convert the string expression '2003-04-09' to a date and time value, prior to matching the string with the values in the SAMPLE_DATE column. This conversion creates a value of '2003-04-09 00:00:00.000', which matches with only record 4.

Another common mistake is to use the BETWEEN verb like so:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09' AND '2003-04-10'

When using the BETWEEN verb all records that are between or equal to the dates specified are returned. Now if in my example above I only wanted to return records that have a SAMPLE_DATE in '2003-04-09'. This example returns all the records that have a SAMPLE_DATE in '2003-04-09' (records 4 - 7), but also returns record 8 that has a SAMPLE_DATE of '2003-04-10'. Since the BETWEEN clause is inclusive of the two dates specified, record 8 is also returned.

Now if you really desire to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE sometime in '2003-04-09' you have a couple of options. Let me go through each option and then explain why one might be better than another might.

Using the Convert Function:

This first example selects all records from the DATE_SAMPLE where the date portion of the SAMPLE_DATE is equal to '2003-04-09'.

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

The reason this example works, and the first example above does not, is because this example removes the time portion of the SAMPLE_DATE column prior to the comparison with string '2003-04-09' being performed. The CONVERT function removes the time portion by truncating the value of the SAMPLE_DATE field to only the first 10 characters.

SELECTING BASED on a DATE RANGE:

The next example selects records base on a date range. This example is also going to retrieve only the records that have a SAMPLE_DATE in '2003-04-09'.

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

Note that the first condition uses a greater than and equal (>=) expression instead of just greater than (>). If only the greater than sign was used the SELECT statement would not return record 4. This record would not be returned because when SQL Server converts the string '2003-04-09' to a date/time value it would be equal to the SAMPLE_DATE on record 4.

Using the DATEPART Function:

Another way to return the records that have a SAMPLE_DATE for a particular date is to use the DATEPART function. With the DATEPART function you can build a WHERE statement that breaks apart each piece (year, month, day) of the SAMPLE_DATE and verifies that each piece is equal to the year, month and day you are looking for. Below, is a DATEPART example that once again returns all the records that have a SAMPLE_DATE in '2003-04-09'.

SELECT * FROM DATE_SAMPLE WHERE DATEPART(YEAR, SAMPLE_DATE) = '2003' AND DATEPART(MONTH,SAMPLE_DATE) = '04' AND DATEPART(DAY, SAMPLE_DATE) = '09'

Using the FLOOR Function:

As I have said before there are many ways to accomplish the same thing. Here is a method that uses the FLOOR and CAST function to truncate the time portion from a date. The inner CAST function converts a DATETIME variable into a decimal value, then the FLOOR function rounds it down to the nearest integer value, and then the outer CAST function does the final conversion of the integer value back to a DATETIME value.

SELECT * FROM DATE_SAMPLE WHERE CAST(FLOOR(CAST(SAMPLE_DATE AS FLOAT))AS DATETIME) = '2003-04-09'

Using the LIKE clause:

The LIKE clause can also be used to search for particular dates, as well. You need to remember that the LIKE clause is used to search character strings. Because of this the value which you are searching for will need to be represented in the format of an alphabetic date. The correct format to use is: MON DD YYYY HH:MM:SS.MMMAM, where MON is the month abbreviation, DD is the day, YYYY is the year, HH is hours, MM is minutes, SS is seconds, and MMM is milliseconds, and AM designates either AM or PM.

The LIKE clause is somewhat easy to use because you can use the wildcard to select all the records in a particular month, AM or PM records, a particular day, and what not. Again using our DATE_SAMPLE table above, let me show you how the return records using the LIKE clause.

Say you want to return all the records with a SAMPLE_DATE in '2003-04-09'. In that case, your SQL Statement would look like so:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE LIKE 'Apr  9 2003%'

Note the month is specified as "Apr", instead of using the numeric "04" value for April. This SELECT statement, similar to the ones I showed earlier, returns records 4 through 7.

Now, say you want to return all the records for April 2003. In this case, you would issues the following statement:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE LIKE 'Apr%2003%'

This statement would return records 3 through 9 from the DATE_SAMPLE table.

If you would like to return any record that has a SAMPLE_DATE in April regardless of the year, then the LIKE statement makes this easy. The following statement uses the LIKE clause to retrieve not only the 2003 records, but also the one record in table DATE_SAMPLE for 2002.

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE LIKE 'Apr%'

The above statement would return records 2 through 9.

If you wanted to return all the records that have a PM designation (RECORD's 2,3,6 and 7), you could do this easily using the following LIKE clause:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE LIKE '%PM'

As you can see, the LIKE statement allows you another alternative to search the database for records with a particular date criteria that supports wildcard characters.

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.

|--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.

Conclusion:

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.