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

How SQL Server Stores Dates and Times

When you first encounter a datetime column in a database, it feels natural to think of it as a simple string that holds a calendar date and a clock time. That image works for everyday use, but the way SQL Server 2000 stores those values under the hood is a little different. Understanding that storage model saves you from common mistakes and helps you write queries that hit the right rows.

SQL Server 2000 offers two built‑in date/time types: datetime and smalldatetime. The difference between them is both size and precision. A datetime value consumes 8 bytes and can represent dates from January 1, 1753 to December 31, 9999, while a smalldatetime value takes only 4 bytes and supports dates from January 1, 1900 to December 31, 2079. The smaller range of smalldatetime is the price you pay for saving space.

Both types encode a value as a floating‑point number: the whole number part counts days since the base date of midnight, January 1, 1900, and the fractional part represents the portion of a day that has elapsed. For instance, the datetime value for noon on January 4, 1900 would be 3.5 because that day is the fourth after the base date (three full days plus half a day). You can see the internal representation with the following query:

The inner cast turns the string literal into a datetime value. The outer cast then converts that value into a float, revealing the 3.5 figure. This trick helps you understand why time comparisons can sometimes behave unexpectedly.

Because the fractional part is stored as a floating‑point number, it is an approximate numeric. The IEEE‑754 standard that underlies floating‑point arithmetic introduces tiny rounding errors. When you store 8:00 AM - exactly one third of a day - the internal value becomes 3.3333333333333335. A quick test demonstrates the effect of rounding:

The first column shows the original 8:00 value. The second and third columns show how small changes in the float translate into a few milliseconds of time difference. In many applications the discrepancy is negligible, but it matters when you write a query that requires an exact match.

SQL Server does not have a dedicated type for storing just a date or just a time. If you store a string that contains only a date, SQL Server sets the time portion to midnight, zero hours. Conversely, if you store only a time, SQL Server sets the date portion to the base date of January 1, 1900. A quick experiment illustrates both scenarios:

The result shows 1900-01-04 00:00:00.000 for the date-only value and 1900-01-01 10:00:00.000 for the time-only value. Knowing this behavior is essential when you try to isolate a day or a time from a column that may contain both parts.

Because the datetime type can store values before the base date as negative numbers, it provides a wider range than smalldatetime. The trade‑off, however, is that the negative values have the same floating‑point approximation issues as positive ones. When you retrieve a datetime from a column, you can safely compare it to other datetime values, but you should avoid comparing against a literal that would force an implicit cast to datetime if the original value is only a date or only a time. Those implicit casts can hide subtle bugs.

Common Pitfalls When Querying Date and Time Data

Many developers encounter surprises when they write a query that should return a handful of rows, but the result set turns out empty or contains unexpected records. Two fundamental reasons for those surprises are the approximate nature of date/time values and the lack of dedicated date‑only or time‑only types in SQL Server.

First, because datetime is an approximate numeric, a value that looks the same to the human eye may differ by a few milliseconds under the hood. If you write a query that uses an equality operator against a literal such as '1900-01-04 08:00', SQL Server will implicitly cast that string to a datetime. The cast produces a value that has an internal representation of 3.3333333333333335. Only rows that contain exactly that float will match. Any row that was stored with a value slightly off - perhaps 3.3333333 or 3.3333334 - will be missed. This problem shows up often when you try to locate invoices dated a particular day and find that only one of several expected records appears.

The second issue is that a datetime column can hold any combination of date and time. If a designer never enforced that a column should store only dates, some rows might contain midnight times, while others contain real times. The same problem can happen with time‑only values. A column that stores '1900-01-01 10:00:00' for a time-only value is indistinguishable from a date/time that actually began on January 1, 1900. If you forget that distinction, you can accidentally pull rows that belong to a completely different day or ignore rows that should have matched.

Detecting how a column is used before you write complex queries is a good safeguard. The following query demonstrates a simple check that tells you whether a datetime column ever contains a non‑zero time component:

If this query returns rows, the column stores a mix of date‑only and date/time values. If it returns none, the column consistently stores both parts. Remember that the floor function strips the fractional day portion, leaving only the integer part that represents whole days. Subtracting that integer part from the original value yields the time portion.

When a column mixes date‑only and date/time values, you might still want to isolate the date portion in a query. A quick way to do that is:

Because the floor function removes the time component, this query will return all rows whose calendar date is February 28, 2002, regardless of the stored time. If you find that the same query returns only a subset of the rows you expect, you may need to adjust your database design or add a dedicated date column to hold the value.

Designing the database to enforce data constraints at the schema level is the most reliable defense. Use a CHECK constraint or a trigger to reject rows that contain a time component when only a date should be stored, and vice versa. For example, a trigger that sets the time part to midnight whenever a row is inserted into a date‑only column ensures that later queries will behave predictably. Even if you cannot change the existing database, testing for consistent usage before writing your query saves you a lot of debugging later.

Beyond data consistency, the approximate numeric nature of datetime forces you to think carefully about comparison operators. Equality (=) is rarely safe for dates and times when the value may come from user input or a different source. Using range comparisons (between or greater‑than and less‑than) is typically safer. Those comparisons let you capture all values that fall within a logical window, sidestepping the small rounding errors that can trip up equality checks.

Practical Techniques for Date and Time Queries

Once you know how the values are stored, you can craft queries that reliably locate the records you want. The most common scenarios involve searching for a specific day or a specific time, sometimes ignoring the other part of the value. Below are several patterns that work well with SQL Server 2000 and that avoid the pitfalls discussed earlier.

Searching for a Specific Day
If the column consistently stores dates with the time portion set to midnight, you can write a simple equality check:

When the time component is not guaranteed to be zero, a range search is safer. This pattern captures all rows that fall on the target day, regardless of what time they were recorded:

The second clause adds one day to the target date, creating a half‑open interval. Every value that starts on January 6, 2003, but ends before January 7, 2003, satisfies the condition. This approach also works if the column contains times in the range 00:00:00.001 through 23:59:59.999; those values still fall inside the interval.

Searching for a Specific Time
When you need rows that share a particular clock time but you don't care about the date, the process is similar. Suppose you want all rows that happened at 10:00 AM, regardless of the day:

That statement is a bit verbose, but the idea is to strip away the integer (date) portion of the value, leaving only the fractional part. Then you compare that fraction to the value that represents 10:00 AM (3.3333333333333335). Because the fractional part is approximate, you should use a range rather than an equality test:

Here the boundaries are expressed as midnight on the base date plus the desired time plus a tiny buffer. This pattern captures times that fall within one second of 10:00 AM, which is more than sufficient for most reporting needs.

Combining Date and Time
Sometimes you want to match both the date and the time, but you still want to ignore any tiny rounding errors. In that case, use a double range: one for the day, one for the time. For example, to find all rows that occurred on February 28, 2002 at around 8:00 AM, you could write:

The first two conditions lock the date, and the last two lock the time to a one‑minute window. Because the comparison uses < for the upper bound, any value that lies exactly at 08:01:00 will be excluded, ensuring that the window is precise.

Avoiding Implicit Casts in the WHERE Clause
A frequent source of performance problems is the implicit cast that occurs when you compare a column to a string literal. The engine has to convert the literal to datetime, which can prevent it from using an index on the column. When possible, cast the column once and reuse the result, or better yet, use a parameterized query that passes a typed value. For instance:

Because the parameter is already typed, no implicit conversion takes place, and the index on InvoiceDate remains usable.

Using Built‑In Date Functions
SQL Server 2000 provides several handy functions for extracting parts of a datetime value: DATEPART, MONTH, DAY, YEAR, HOUR, and so on. When you need to pull all rows for a particular month, you can write:

Be aware that using these functions on the column itself forces a row‑by‑row evaluation, which can bypass indexes. If the query is run frequently, consider adding a computed column that extracts the month and year, marking it as persisted and indexed. Then your queries can reference the computed column directly, preserving index efficiency.

In summary, the key to reliable date and time queries in SQL Server 2000 is to treat the values as floating‑point numbers, use ranges instead of equality where rounding can occur, and be mindful of how the database engine interprets literals. With those guidelines in mind, you can write queries that return exactly the records you need, every time.

Performance and Indexing Considerations

Speed matters when you run a query every hour, daily, or even just once a month. Knowing how SQL Server interacts with indexes when you work with date and time columns helps you keep your applications responsive. The most important rule is simple: avoid applying a function directly to an indexed column in the WHERE clause. When you do, the optimizer typically performs a table scan instead of a seek, which can be orders of magnitude slower.

For example, consider a query that searches for invoices on a particular day using the BETWEEN construct:

Because the literal values are typed as datetime, SQL Server can use an index on InvoiceDate to jump directly to the relevant rows. Contrast that with a query that uses DATEPART:

In this case, the database engine cannot use an index on InvoiceDate, because the function forces a full scan. If you find yourself writing many queries that filter on specific days, months, or years, a more efficient approach is to add a computed column that extracts the date part and mark it as persisted:

Then create an index on InvoiceDateOnly and use it in queries:

That strategy turns the costly row‑by‑row function call into a simple index lookup, drastically cutting execution time for large tables.

Another optimization technique involves splitting a combined datetime column into two separate columns: one for the date and one for the time. If your workload frequently queries by date alone, or by time alone, the split can let you index each column independently. The trade‑off is a slightly more complex schema and potential data redundancy. Use this approach only if performance measurements confirm that the benefits outweigh the extra maintenance cost.

When dealing with smalldatetime, remember that it rounds to the nearest minute. That rounding simplifies comparisons: two values that differ by only a few seconds are considered equal. For applications that only care about minutes, using smalldatetime can reduce storage requirements and eliminate the need for range queries. However, if you need precision beyond a minute - such as logs that record seconds or milliseconds - stick with datetime and use explicit ranges to accommodate floating‑point inaccuracies.

Finally, test your queries with real data. Use the SET STATISTICS IO ON command to see whether SQL Server is performing index seeks or scans. If you notice a scan, revisit your query to eliminate the function or rewrite the predicate so that the optimizer can use the index. Small changes, like swapping an equality test for a range or moving a cast to a parameter, often bring significant performance improvements.