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

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.