This morning I stumbled across an interesting situation that led me down into depths of SQL data types that I had never explored before. I was simply trying to update a single datetime value…
2011-10-01 00:00:00.000 to 2011-09-30 23:59:59.999
… but as hard as I tried (“harder… [slams keyboard] ... harder!”), the value wouldn’t take. A much wiser SQL sage than I explained the value was being updated, but it was being rounded up. Microsoft’s datetime documentation revealed datetime has an accuracy of only 0.00333 seconds. A quick test revealed that the value 2011-09-30 23:59:59.997 saved without a problem. Rounding confirmed.
So what’s the solution? As you may be able to tell from the data, I’m trying to represent the very end of a day; the very last millisecond. We’re storing a field in our billing system called ProcessAfterUTC, which indicates that the order should only be processed after a certain datetime. Based on that information, it looks like my possible fixes are:
- Set my end of day values to 997ms.
Not a very good solution… that just feels dirty.
- Change from exclusive to inclusive logic.
As the field name ProcessAfterUTC suggests, we are excluding all values prior to that datetime. Since I know the value 2011-10-01 00:00:00.000 works just fine, I could rename the field ProcessOnOrAfterUTC and revise my logic accordingly.
- Use the newer datetime2 datatype.
According to the documentation, datetime2 represents the dates 0001-01-01 to 9999-01-01 with a precision of 100 nanoseconds; the exact same representation as the C# datetime structure. In addition, the storage size is an interestingly variable 6-8 bytes, as opposed to the older datetime being stored as 8 bytes and having a much smaller range of values.
After a bit of research and testing, I’ve gone with solution #3 and changed the table’s datetime field to datetime2. A few notes about the experience:
- By using a datetime2(3) precision instead of the default datetime2(7) precision, my storage size is reduced to 7 bytes and the values will match exactly when they’re tossed around the business logic as C# datetime data types.
- In Microsoft’s SQL datetime documentation, the very first note section recommends using datetime2 for new work.
- Changing datetime to datetime2 failed in the SQL Server Management Studio designer. But the following query ran without a hitch:
alter table Account alter column MembershipExpiresUTC datetime2(3)
- Updating my Entity Framework files after the database schema change worked just fine. No exceptions were thrown after the change and my values saved just fine.