How does SqlDateTime do its precision reduction

问题:

Consider the following program:

DateTime dateTime = new DateTime(634546165144647370);
SqlDateTime sqlDateTime = new SqlDateTime(dateTime);

Console.WriteLine("dateTime.TimeOfDay    = " + dateTime.TimeOfDay);
Console.WriteLine("sqlDateTime.TimeOfDay = " + sqlDateTime.Value.TimeOfDay);
Console.ReadLine();

That will have the following output:

dateTime.TimeOfDay    = 10:21:54.4647370  
sqlDateTime.TimeOfDay = 10:21:54.4630000

What is odd to me is that .464737 was rounded to .463. Shouldn't that have rounded to .464?

I assume I have not found a bug in .NET code, so the question is:

Why did it round to what it did? and how can I get client side rounding that will do what SqlServer is going to do?

As a side note, I saved this date time to a SQL Server database (in a DateTime column) and pulled it out again and it came out as 10:21:54.4670000. So I am really confused. (I thought SqlDateTime would match up with what SQL Server was going to do.)

Note: Because I am using OData I cannot use DateTime2 in SQL Server.

回答1:

SQL Server DATETIME has an accuracy of 3.33ms - therefore, you cannot get all possible values, and there's a good chance .464 was just such a value.

On SQL Server 2008, you could use DATETIME2 or TIME(x) datatypes which have an accuracy to 100ns - that should be plenty enough for "regular" use

回答2:

SQL Server's datetype datatype is, internally, two 32-bit words (integers). The high-order word is the offset in days since the epoch (zero point) of the internal calendar used by SQL Server: that epoch is 1 January 1900 00:00:00.000. The low-order word is the offset in milliseconds since start-of-day (00:00:00.000/midnight).

For historic reasons, while the precision of the low-order word is 1 millisecond; the accuracy is 1/300th second (!?). That means that any given point-in-time is rounded to an increment of of 0, 3 or 7 milliseconds.

To do the conversion in the manner in which SQL Server does it, do the following: Take the milliseconds portion of the actual time under consideration, a value from 0-999, modulo 100. That gives you the low order digit, a value from 0-9. So if the current time is 23:57:23.559, the milliseconds component is 559. Modulo 100 you get 9.

1. Values 0 and 1 are "rounded down" to 0.
2. Values 2, 3 and 4 are "rounded down"" to 3.
3. Values 5, 6, 7 and 8 are "rounded down" to 7.
4. A value of 9 is rounded UP to 0. This has a rather unpleasant and nasty side effect: if the milliseconds portion of the time is 999, it ticks up 1 millisecond. This means that the time 23:59:59.999 is rounded up to THE NEXT DAY. So conversion of '31 Dec 2010 23:59:59.999' yields a datetime value of...1 January 2011 00:00:00.000.

Brilliant! Or something.

See the "Remarks" section under SQL Server 2005 BOL here: http://msdn.microsoft.com/en-us/library/ms187819(v=SQL.90).aspx

The upshot of all of this is that you can't do the obvious check for a date range/time period...something like

where myDateColumn between '1 September 2011 00:00:00.000'
  and '30 September 2011 23:59:59.999'

as that potentially brings in a [small] chunk of data from the next period. And you can't say

where myDateColumn between '1 September 2011 00:00'
  and '30 September 2011 23:59:59'

as that potentially excludes data that belongs in the period. Instead, you must say something like

• where myDateColumn >= '1 September 2011 00:00:00.000' and myDateColumn < '1 October 2011 00:00:00.000', or

• where myDateColumn >= '1 September 2011 00:00:00.000' and myDateColumn <= '30 September 2011 23:59:59.997'

It should be noted that smalldatetime, which has a precision of 1 minute, exhibits the same bogus behaviour: if the seconds component of the time under consideration is 29.998 seconds or less, it is rounded down to the nearest minute; if 29.999 or higher, it is rounded UP to the next minute, so the value 31 Dec 2010 23:59:30.000' winds up as asmalldatetimevalue of1 Jan 2011 00:00:00`.

This has all sorts of implications, especially WRT to billing systems and the like.

I would say that if precision is important to you, store your date/time values in SQL Server as strings in ISO 8601 format, something like 2011-10-30T23:59:55.1234 (or the equivalent 'compact' form (20111030T235955.1234). ISO 8601 collates and compares properly; it converts easily and it is human-readable. Even better split it out into two columns — one for date (2011-10-30) and one for time (23:59:55.1234). Then add a third, computed column to put it all together:

create table foo
(
  ...
  transaction_date char(10) not null ,
  transaction_time char(12) not null ,
  ...
  iso8601_transaction_datetime as transaction_date + 'T' + transaction_time ,
  ...
)

A pretty good summary of ISO 8601 is at http://www.cl.cam.ac.uk/~mgk25/iso-time.html. Wikipedia also has pretty good information: http://en.wikipedia.org/wiki/ISO_8601.

回答3:

SqlDateTime Structure

Represents the date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds to be stored in or retrieved from a database. The SqlDateTime structure has a different underlying data structure from its corresponding .NET Framework type, DateTime, which can represent any time between 12:00:00 AM 1/1/0001 and 11:59:59 PM 12/31/9999, to the accuracy of 100 nanoseconds. SqlDateTime actually stores the relative difference to 00:00:00 AM 1/1/1900. Therefore, a conversion from "00:00:00 AM 1/1/1900" to an integer will return 0.

I would argue that sqlDateTime is within that 3.33 ms accuracy.

The DateTime value type represents dates and times with values ranging from 12:00:00 midnight, January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D.

As a side note, I saved this date time to a SQL Server database (in a DateTime column) and pulled it out again and it came out as 10:21:54.4670000. So I am really confused. (I thought SqlDateTime would match up with what SQL Server was going to do.)

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar (excluding ticks that would be added by leap seconds). For example, a ticks value of 31241376000000000L represents the date, Friday, January 01, 0100 12:00:00 midnight. A DateTime value is always expressed in the context of an explicit or default calendar.

and

Internally, all DateTime values are represented as the number of ticks (the number of 100-nanosecond intervals) that have elapsed since 12:00:00 midnight, January 1, 0001. The actual DateTime value is independent of the way in which that value appears when displayed in a user interface element or when written to a file. The appearance of a DateTime value is the result of a formatting operation. Formatting is the process of converting a value to its string representation.

Because the appearance of date and time values is dependent on such factors as culture, international standards, application requirements, and personal preference, the DateTime structure offers a great deal of flexibility in formatting date and time values through the overloads of its ToString method. The default DateTime.ToString() method returns the string representation of a date and time value using the current culture's short date and long time pattern. The following example uses the default DateTime.ToString() method to display the date and time using the short date and long time pattern for the en-US culture, the current culture on the computer on which the example was run.

It looks like DateTime is accurate within 100ns which is about the difference you have experienced.

As a side note, I saved this date time to a SQL Server database (in a DateTime column) and pulled it out again and it came out as 10:21:54.4670000. So I am really confused. (I thought SqlDateTime would match up with what SQL Server was going to do.)

This is also within the 3.33 ms accuracy...If you need something more accurate then 3.33ms then you will have to use SQL Server 2008