Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

How to Search for Date and Time Values Using Microsoft SQL Server 2000 : Page 2

Before you can effectively query date/time (or temporal) data, you have to know something about how date/time values are stored. SQL Server supports two date/time data types: datetime and smalldatetime. This article explains in detail the differences between the two, talks about the performance effect of database design on querying, and teaches how to search by date and by time.


advertisement
Performance Considerations in Querying
A search based on an indexed column completes faster than a search based on a non-indexed column. So date/time columns that are searched frequently should be indexed. Be aware, though, that if you then use a function in the search condition, the index can’t be used in the same way, which slows performance. For searches that are executed thousands of times a day on a production database, this can cause significant performance problems. For this reason, you should avoid using functions in such search conditions whenever possible. As you’ll see in the examples that follow, this sometimes results in solutions that are less flexible than those that use functions.

In addition, keep in mind that some applications require that you search for portions of a date/time column. The portion could be date only, time only, or even a smaller portion, such as a year or hour. In that case, it may improve performance to split a single date/time column into two or more separate columns, and then index those that are searched most often.

How to Search by Date
Frequently. you’ll need to search a date/time column for a specific date, regardless of time. If the data in the column have been used consistently with the time component set to zero, that’s no problem. You just search for the date you’re looking for.



But consider the following table, called DateSample:

ID  DateVal
--  -----------------------
1   2001-02-28 10:00:00.000
2   2002-02-28 13:58:32.823
3   2002-02-29 00:00:00.000
4   2002-02-28 00:00:00.000

As you can see, the DateVal column is used inconsistently. The third and fourth values indicate that the column might have been intended to store dates only, but the first two values indicate that this wasn’t enforced.

As a result, if you use the following query to retrieve rows with the date February 28, 2002:

SELECT * FROM DateSample
WHERE DateVal = '2002-02-28'

The result set includes only row 4 instead of both rows 2 and 4. That’s because the date literal is implicitly cast as a datetime value which, in this case, has a zero time component. Since this doesn’t exactly match the value in row 2, that row isn’t returned.

How can you get around the time component? If the query is run often, you should base the search on a range of values, as in:

SELECT * FROM DateSample
WHERE DateVal BETWEEN '2002-02-28' AND '2002-02-28 23:59:59.997'

Remember that the BETWEEN clause retrieves values that are equal to the upper and lower limits, so you can’t code the upper limit as just '2002-02-29'. If you do, then you’ll incorrectly retrieve row 3. Another way to get the same result is to use comparison operators:

SELECT * FROM DateSample
WHERE DateVal >= '2002-02-28' AND DateVal < '2002-02-29'

If the query is run infrequently (to produce a report only once a month, for instance), you can code an expression in the WHERE clause that strips the date/time value of its fractional component. For example, this query:

SELECT * FROM DateSample
WHERE CAST(FLOOR(CAST(DateVal AS float)) AS datetime) = '2002-02-28'

returns both rows 2 and 4. In addition, there are many other expressions that you can use to accomplish this same result (my SQL book, Murach’s SQL for SQL Server, covers a couple of others).

By the way, if you wished to retrieve rows with the day February 28, regardless of year, you could code the following query:

SELECT * FROM DateSample
WHERE MONTH(DateVal) = 2 AND DAY(DateVal) = 28

which retrieves rows 1, 2, and 4. Since there isn’t a way to accomplish this without using one or more functions, however, this query shouldn’t be run frequently against a production database. If you need to perform this kind of search on a query that runs often, you should change the design of the database, if possible. Then, you can create a separate, indexed column to store the portion of the date/time value that you need to search.

How to Search by Time
Searching a column for a specific time, regardless of date, is similar to searching for date-only values. If the column consistently stores just the time portion, then searching the data is simplified. However, unlike date values, the time value is represented by an approximate numeric. So even when the date portion can be ignored, you must still consider rounding errors.

To illustrate time-only searches, consider following table, called TimeSample:

ID  TimeVal
--  -----------------------
1   2002-02-28 10:00:00.000
2   1900-01-01 13:58:32.823
3   1900-01-01 09:59:59.997
4   1900-01-01 10:00:00.000

Here, the TimeVal column is used inconsistently, sometimes storing time only and sometimes storing both date and time. So if you use the following query to retrieve rows with the time 10:00AM:

SELECT * FROM TimeSample
WHERE TimeVal = '10:00:00'

You only get row 4. Row 1 isn’t retrieved because the date literal is implicitly cast as a datetime value with a zero date component, which doesn’t match the date component of row 1. In addition, row 3 isn’t retrieved because this value is close to, but not equal to, 10:00AM.

To ignore the date component of a column, you can code an expression that strips the date/time value of its integer component, such as:

SELECT * FROM TimeSample
WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) = '10:00'

which returns rows 1 and 4. Unfortunately, there’s no way to accomplish this without using one or more functions. For this reason, it’s critical to store time-only data correctly in the first place. If you need to do this kind of search often, you should, if possible, change the database design.

To search for time values that are approximately equal is simply a matter of coding a range of values. If the time-only data are stored consistently without the date component, you can use a query like this:

SELECT * FROM TimeSample
WHERE TimeVal BETWEEN '09:59' AND '10:01'

or

SELECT * FROM TimeSample
WHERE TimeVal > '09:59' AND TimeVal < '10:01'

Both of these queries return rows 3 and 4. Of course, you have to decide what literal values to use for the range of approximation that you prefer.

If the time-only data are stored inconsistently, then you need to accommodate both non-zero date components and a range of time values. For instance, you can use a query like this:

SELECT * FROM TimeSample
WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) > '09:59'
  AND TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) < '10:01'

which returns rows 1, 3, and 4. Again, though, there’s no way to accomplish this without the use of a function. So you may need to change the database design, if you can.

One other way to approximate time values is to use the smalldatetime data type rather than the datetime data type in the original table. Since smalldatetime always rounds the time portion to the nearest minute, times in the range from 09:59:29.999 to 10:00:29.998 are stored as 10:00. If approximation to the nearest whole minute is sufficient for your application, then using smalldatetime will prevent the need to search for a range of values.



Bryan Syverson, author of Murach’s SQL for SQL Server, has worked with SQL as an application programmer, a SQL programmer, and a database developer in a variety of environments, including the health care industry, management consultancies, and not-for-profit organizations. In the process, he’s realized that most programmers could do their jobs more effectively if they knew more about SQL. So his goal in Murach’s SQL is to provide that knowledge in an easy-to-access style for both beginning and experienced SQL users.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date