A while back I was debugging some code that needed to query a database for reservations that happened within a certain date range. For example, I’d want all reservations for a week, and would search for reservations that started anywhere between Sunday at 12:00 a.m. to Saturday at 11:59:59 p.m. The code that computed the date range was in .NET, so to get the end date I would do startDate.AddDays(7).AddTicks(-1). Assuming the start date was the first day of the week, this would be as inclusive as possible by going forward to the following Sunday and then pulling back into Saturday by the smallest increment offered by .NET. This should mean that any reservation stating in Saturday would be included, but even those at 12:00 a.m. on the following Sunday would not. Instead, the query kept returning those reservations on the following Sunday at 12:00 a.m.
The problem turned out to be the precision offered by SQL Server’s representation of dates and times. SQL Server offers two types for working with dates & times: DATETIME and SMALLDATETIME (TIMESTAMP is also offered, but that’s not not actually used to store values but rather track when a row has been modified). DATETIME stores the number of 1/300 unit increments after midnight, meaning it has precision to 3.33 milliseconds. SMALLDATETIME is stored as two two-byte integers. The date component is the number of days after January 1, 1900 (meaning dates before this date cannot be represented) and the time component is the number of minutes since midnight. With this representation seconds aren’t actually represented at all, so for input dates with seconds less than or equal to 29.998 the time is rounded down and for those greater than or equal to 29.999 the time is rounded up tot he next minute.
As it turned out, my problem query was dealing with a SMALLDATETIME and I had to back of the precision of the .NET code to accommodate. More information about SQL Server dates & times is available here.