SQL Server中DateTime的不理想的四舍五入 [英] Undesired rounding of DateTime in SQL Server

问题描述

我遇到似乎奇怪的事情。当我将其保存到 datetime 列时，SQL Server似乎舍弃了一些 DateTime 值。我怀疑我错过了一些东西，但我无法察觉。我使用.NET 4.0对SQL Server 2008运行此测试。以下应该说明问题：

我在SQL Server中创建了一个名为Timestamps的表。它有两列：

id - bigint，身份，PK

timestamp 日期时间

我还创建了一个简单的测试，执行以下操作：

1. 获取将当前时间截断为毫秒精度


2. 将截断的时间保存为时间戳


3. 从数据库检索datetime`值，并将其与原始（截断）DateTime对象进行比较。

  public static void RoundTest（）
 {
 DateTime preTruncation = DateTime.UtcNow; 
 DateTime truncated = preTruncation.TruncateToMilliseconds（）; 
 
 using（SqlConnection conn = new SqlConnection（ConfigurationManager.ConnectionStrings [test]。ConnectionString））
 {
 conn.Open（）; 
 SqlCommand cmd = new SqlCommand（@INSERT INTO Timestamps（timestamp）
 VALUES（@savedTime）; 
 SELECT SCOPE_IDENTITY（）AS id）; 
 cmd.Parameters.Add（new SqlParameter（savedTime，truncated））; 
 cmd.Connection = conn; 
 var id = cmd.ExecuteScalar（）; 
 
 SqlCommand get = new SqlCommand（@SELECT timestamp FROM Timestamps 
 WHERE id = @id）; 
 
 get.Parameters.Add（new SqlParameter（id，id））; 
 get.Connection = conn; 
 DateTime retrieve =（DateTime）get.ExecuteScalar（）; 
 
 if（retrieve！= truncated）
 {
 Console.WriteLine（original：+ preTruncation.TimeOfDay）; 
 Console.WriteLine（truncated：+ truncated.TimeOfDay）; 
 Console.WriteLine（retrieval：+ retrieval.TimeOfDay）; 
 Console.WriteLine（）; 
} 
} 
} 
  

尽管我预计截断值等于从DB返回的值，但并不总是如此。以下是一些示例输出：

 原始：19：59：13.4049965 
截断：19：59：13.4040000 
检索：19：59：13.4030000 
 
原件：19：59：14.4989965 
截断：19：59：14.4980000 
检索：19：59：14.4970000 
 
原件：19：59：15.4749965 
截断：19：59：15.4740000 
检索：19：59：15.4730000 
 
原件：19：59：30.1549965 
 truncated：19：59：30.1540000 
已检索：19：59：30.1530000 
  

TruncateToMilliseconds（）如下所示：

  public static DateTime TruncateToMilliseconds（this DateTime t ）
 {
 return new DateTime（t.Year，t.Month，t.Day，t.Hour，t.Minute，t.Second，t.Millisecond）; 
} 
  

什么给了？这是非常不合适的四舍五入，还是我在这里做出错误的假设？

解决方案

日期时间只能准确到3ms。因此，它将达到最接近的3ms的倍数。要克服这个问题，请查看 datetime2 。请注意，这仅适用于SQL2008 +

编辑：这不仅仅是3ms。 $ 0.00，0.003或.007秒的增量为四舍五入，

I ran into something that seems odd. SQL Server appears to be rounding some DateTime values inappropriately when I save them to datetime columns. I suspect I'm missing something, but I can't spot it. I'm running this test against SQL Server 2008 using .NET 4.0. The following should illustrate the issue:

I have created a table in in SQL Server called Timestamps. It has two columns:

id - bigint, Identity, PK
timestamp - datetime

I also created a simple test that does the following:

1. Gets the current time, truncating the value to millisecond precision
2. Saved the truncated time to Timestamps
3. Retrieved the datetime` value from the DB and compared it to the original (truncated) DateTime object.

public static void RoundTest()
{
    DateTime preTruncation = DateTime.UtcNow;
    DateTime truncated = preTruncation.TruncateToMilliseconds();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(@"INSERT INTO Timestamps(timestamp) 
                                            VALUES(@savedTime); 
                                            SELECT SCOPE_IDENTITY() AS id");
        cmd.Parameters.Add(new SqlParameter("savedTime", truncated));
        cmd.Connection = conn;
        var id = cmd.ExecuteScalar();

        SqlCommand get = new SqlCommand(@"SELECT timestamp FROM Timestamps 
                                            WHERE id = @id");

        get.Parameters.Add(new SqlParameter("id", id));
        get.Connection = conn;
        DateTime retrieved = (DateTime)get.ExecuteScalar();

        if (retrieved != truncated)
        {
            Console.WriteLine("original: " + preTruncation.TimeOfDay);
            Console.WriteLine("truncated: " + truncated.TimeOfDay);
            Console.WriteLine("retrieved: " + retrieved.TimeOfDay);
            Console.WriteLine();
        }
    }
}

Although I expect the truncated value to be equivalent to the value returned back from the DB, that is not always the case. Here's some sample output:

original: 19:59:13.4049965
truncated: 19:59:13.4040000
retrieved: 19:59:13.4030000

original: 19:59:14.4989965
truncated: 19:59:14.4980000
retrieved: 19:59:14.4970000

original: 19:59:15.4749965
truncated: 19:59:15.4740000
retrieved: 19:59:15.4730000

original: 19:59:30.1549965
truncated: 19:59:30.1540000
retrieved: 19:59:30.1530000

TruncateToMilliseconds() looks like this:

public static DateTime TruncateToMilliseconds(this DateTime t)
{
    return new DateTime(t.Year, t.Month, t.Day, t.Hour, t.Minute, t.Second, t.Millisecond);
}

What gives? Is this really inappropriate rounding, or am I making a mistaken assumption here?

解决方案

Datetime is only accurate to 3ms. Therefore it'll round to the nearest multiple of 3ms. To overcome this, look at the datetime2. Note that this is for SQL2008+ only

EDIT: it's not quite only to 3ms. It's rounded to increments of of .000, .003, or .007 seconds

这篇关于SQL Server中DateTime的不理想的四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！