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.
» 
Comment and Contribute
