在C＃和SQL Server的DateTimeOffset分辨率 [英] DateTimeOffset resolution in c# and SQL Server

问题描述

文档状态，在.NET和SQL服务器的分辨率为100ns。

Docs state that in both .NET and SQL server the resolution is 100ns.

一的DateTimeOffset值的时间成分的单位是100纳秒为单位叫做蜱的 - C＃ 精度 - 100纳秒的 - SQL服务器

The time component of a DateTimeOffset value is measured in 100-nanosecond units called ticks - C# Accuracy - 100 nanoseconds - SQL Server

不过SQL似乎放弃了最后一个数字（例如：我试图挽救2013年8月15日09：19：07.2459675 -04：00，和SQL节约了2013年8月15日09：19：07.2459670 -04： 00 - 注意最后一个数字的变化）

However SQL seems to drop the last digit (e.g. I'm trying to save 2013-08-15 09:19:07.2459675 -04:00, and SQL saves 2013-08-15 09:19:07.2459670 -04:00 - notice the last digit changes.)

这会发生在同一台机器上，所以它不依赖于硬件。

This happens on a same machine, so its not hardware dependent.

不，我确实需要这一决议，但它使比较难的日期..我只是好奇。

Not that I actually need this resolution, but it makes comparing the dates harder.. and I'm just curious.

推荐答案

我会说，这个问题是你的...小code，以显示：

I'll say that the problem is yours... A little code to show:

namespace Test
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;

    /// <summary>
    /// 
    /// </summary>
    public class Program
    {
        /// <summary>
        /// 
        /// </summary>
        public static void Main()
        {
            // Change the connection string to specify your server. 
            // Probably you won't need an initial catalog because this
            // program uses a temp table
            string connStr = "Integrated Security=True";

            // The temp table is called #Temp . It will cease to exist at the end 
            // of the program automatically
            // Two columns, DateTimeOffset and ShortDateTimeOffset
            string query = @"CREATE TABLE #Temp (DateTimeOffset datetimeoffset(7) NOT NULL, ShortDateTimeOffset datetimeoffset(6) NOT NULL);INSERT INTO #Temp VALUES (@DT1, @DT2);SELECT * FROM #Temp";

            using (var connection = new SqlConnection(connStr))
            using (var command = new SqlCommand(query, connection))
            {
                const string dtString = "2013-08-15 09:19:07.2459675 -04:00";
                const string dtFormat = "yyyy-MM-dd HH:mm:ss.fffffff zzz";

                DateTimeOffset dt = DateTimeOffset.Parse(dtString, CultureInfo.InvariantCulture);

                string dtString2 = dt.ToString(dtFormat, CultureInfo.InvariantCulture);

                Console.WriteLine("Sending          : {0}", dtString2);

                // Just to be sure!
                if (dtString != dtString2)
                {
                    throw new Exception("Problem in conversion");
                }

                command.Parameters.Add("@DT1", SqlDbType.DateTimeOffset).Value = dt;
                command.Parameters.Add("@DT2", SqlDbType.DateTimeOffset).Value = dt;

                try
                {
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            DateTimeOffset dtRec1 = (DateTimeOffset)reader[0];
                            DateTimeOffset dtRec2 = (DateTimeOffset)reader[1];

                            string dtRecString1 = dtRec1.ToString(dtFormat, CultureInfo.InvariantCulture);
                            string dtRecString2 = dtRec2.ToString(dtFormat, CultureInfo.InvariantCulture);

                            Console.WriteLine("Receiving (long) : {0}", dtRecString1);
                            Console.WriteLine("Receiving (short): {0}", dtRecString2);

                            if (dtRec1 != dt)
                            {
                                throw new Exception("Difference between DateTimeOffset(.NET) and DateTimeOffset(sql)");
                            }

                            if (Math.Abs(dtRec2.Ticks - dt.Ticks) > 10)
                            {
                                throw new Exception("Too much difference between DateTimeOffset(.NET) and DateTimeOffset(6)(sql)");
                            }

                            if (reader.Read())
                            {
                                throw new Exception("Too many rows");
                            }
                        }
                        else
                        {
                            throw new Exception("No rows");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

在我的SQL：

Sending          : 2013-08-15 09:19:07.2459675 -04:00
Receiving (long) : 2013-08-15 09:19:07.2459675 -04:00
Receiving (short): 2013-08-15 09:19:07.2459680 -04:00

短是的DateTimeOffset（6）。

这篇关于在C＃和SQL Server的DateTimeOffset分辨率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！