Rounding the Corner

Thoughts About Software Development From the MST Staff.

DateTime vs DateTime2: SQL is Rounding My 999 Milliseconds!

/ 12. September 2011

SQL ServerThis 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:

  1. Set my end of day values to 997ms.
    Not a very good solution… that just feels dirty.
     
  2. 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.
     
  3. 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.

Development , ,

Add comment


(won't show your email and don't even know how to sell it!)

  Country flag

biuquote
  • Comment
  • Preview
Loading