Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges - Page 2
May 21, 2003
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'.
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'.
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.
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:
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:
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.
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:
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.