|
Date comparisons are often found in business applications most notably those
with product/inventory tracking, and order processing components. The
following two examples depict such instances.
Example 1
Obtain the upper-most record from the record set with the date field value closest
to the current date, but not after today's date. This is a great example of a query
in which the the record set must satisfy the effective date closest to the current date.
The query yields one record due to the 'TOP 1' limit clause. The other records are
irrelevant, since the sort order reveals the best matching effective date record first.
|
SELECT TOP 1 date_effective, cost FROM inventory_cost
WHERE item_id = '34' AND
date_effective <= getdate()
ORDER BY date_effective DESC
|
Example 2
Obtain the record set that excludes obsolete records based on the
date field value. Records are considered obsolete or inactive
if their date field value is both specified, and define a date on or before today's date.
Two conditions test for an integer value of zero (1900-01-01) or
'NULL' in a field of type 'datetime' incase the field allows NULL
values. In this particular example the 'datetime' field type is named: date_obsolete.
There is a distinction between the two in MS-SQL. Often
times one of the two conditions are excluded, and so the overall query would yield
inaccurate result sets.
|
SELECT item FROM products
WHERE (
CAST(date_obsolete AS int) = '0' OR
CONVERT(varchar,date_obsolete) IS NULL OR
date_obsolete > getdate()
)
|
|
