Today I had an experienced come to me with a problem working with dates in an SQL Server database. They were saving image files, and storing the date based in the date of the file in the file system. If a new image was saved in the file system with the same name, the system would know it was updated because the new filename would have a newer date. That’s a pretty elegant way to handle things. The only problem was that their unit tests weren’t working.
It turns out that the problem was the difference between how the Dot Net DateTime class handles time, and the data type being used in SQL Server. The Dot Net DateTime class is very granular, handling time parts as small as a tick. Many SQL Server data types for storing dates and time, or the combinations of both, are not as granular.
The DateTime data type in SQL Server is popular, and one of the oldest combined data types. Even though it shares the same name as the Dot Net DateTime Class, it does not store dates with as much granularity. In fact, the smallest time attribute it can track is a millisecond. Even so, it does not store every millisecond within a second, 001 thru 999. It rounds to .003. Here is an example incrementing a DateTime values by 1 millisecond, ten times.
DECLARE @DTM DateTime = '1/1/2010'
SELECT Number, DATEADD(millisecond, Number, @DTM)
FROM Sequence
WHERE Number between 0 AND 10
| Number | Date |
|---|---|
| 0 | 2010-01-01 00:00:00.000 |
| 1 | 2010-01-01 00:00:00.000 |
| 2 | 2010-01-01 00:00:00.003 |
| 3 | 2010-01-01 00:00:00.003 |
| 4 | 2010-01-01 00:00:00.003 |
| 5 | 2010-01-01 00:00:00.007 |
| 6 | 2010-01-01 00:00:00.007 |
| 7 | 2010-01-01 00:00:00.007 |
| 8 | 2010-01-01 00:00:00.007 |
| 9 | 2010-01-01 00:00:00.010 |
| 10 | 2010-01-01 00:00:00.010 |
As you can see, it doesn’t simply count from 0 to 10 milliseconds. It rounds the millisecond value. This can really throw off a developer, expecting time to work similar to their Dot Net code. We need to be careful when we chose a Data Type for our database, making sure the capability of the storage meets the requirements of the application. Since SQL Server 2008 there have been more granular date and time data types. If you need the precision, be sure the data type you select meets your need.
My developer determined that the DateTime data type was adequate, and truncated the Dot Net DateTime.Now value down to the nearest second, resolving the difference found within the database storage. Stripping off milliseconds resulted in a common value where differences could be detected correctly.
Cheers,
Ben