SQL DateTime 型別陷阱

前幾天學到新知識 - SQL 的 DateTime 型別雖然可顯示到毫秒（0.001秒），但精確度非 0.001 秒，最後一位會取相近值到 0, 3, 或 7。

想起過去曾遇過時間資料存入 SQL 比對失敗的經驗，當時不知其所以然，也忘了最後怎麼解決，猜想跟此一特性有關。決定做個實驗釐清觀念，強化記憶。

我在 LocalDB 建了一個 MyTable 資料表，TimeKey 欄位使用 SQL DateTime 型別：

我寫了一段程式會抓取當下 DateTime.Now 寫入 MyTable.TimeKey，再馬上用 SELECT WHERE 查詢讀回來，比對寫入資料庫的值與 C# 端的 DateTime 變數是否相符。有幾種可能：

1. WHERE 條件找不到剛才寫入的資料
2. 由資料庫讀取還原回 C# DateTime 與原變數完全相等
3. 資料庫讀取值與原變數到毫秒(ms)都是一致的
4. 資料庫讀取值與原變數到秒都是一致的
5. 二者連秒都不一致

程式範例如下：

static void Main(string[] args)
{
    using (var cn = GetConnection())
    {
        cn.Execute("DELETE FROM MyTable");
        Enumerable.Range(1, 32).ToList().ForEach(i =>
         {
             Thread.Sleep(100);
             var d = DateTime.Now;
             Console.Write($"{i:00}. TimeKey={d:yyyy/MM/dd HH:mm:ss.fff} : ");
             cn.Execute("INSERT INTO MyTable (TimeKey,SeqNo) VALUES (@d, @i)",
                 new { d, i });
             var res = cn.Query("SELECT * FROM MyTable WHERE TimeKey=@d", new { d }).SingleOrDefault();
             if (res == null)
             {
                 Console.WriteLine("NOT FOUND");
                 return;
             }
             var dateTimeFromSql = (DateTime)res.TimeKey;
             Debug.WriteLine(JsonConvert.SerializeObject(d) + " vs " + JsonConvert.SerializeObject(dateTimeFromSql));
             if (d.CompareTo(dateTimeFromSql) == 0)
             {
                 Console.WriteLine("完全相等");
             }
             else if (d.ToString("yyyy/MM/dd HH:mm:ss.fff") == dateTimeFromSql.ToString("yyyy/MM/dd HH:mm:ss.fff"))
             {
                 Console.WriteLine($"相符至亳秒：{d.ToString("yyyy/MM/dd HH:mm:ss.fff")}");
             }
             else if (d.ToString("yyyy/MM/dd HH:mm:ss") == dateTimeFromSql.ToString("yyyy/MM/dd HH:mm:ss"))
             {
                 Console.WriteLine($"相符至秒：{d.ToString("yyyy/MM/dd HH:mm:ss")}");
             }
             else
             {
                 Console.WriteLine($"不相等[{dateTimeFromSql:yyyy/MM/dd HH:mm:ss.fff}]");
             }
         });
        Console.ReadLine();
    }
}

執行結果印證了 SQL 端 DateTime 毫秒只有 0,3,7 三種尾數的說法，當尾數為 0,3,7 時相符到毫秒，否則只相等到秒。由於 C# 的 DateTime 的最小單位是 Tick (1/10,000,000 秒)，SQL DateTime 只到 ms (1/1,000 秒)，除非 Tick 值剛好可被 10000 整除(最後四碼均為 0)，毫秒尾數又是 0,3,7，二者才會完全相等，

SQL DateTime2 型別提供與 C# DateTime 相當的精準度，可以改善這個問題。所以我將 MyTable.TimeKey 換成 DateTime2：

天真以為換成 SQL DateTime2 型別什麼都不用做就百病消，但事實不然!

程式噴出一堆 WHERE 條件查不到錯誤。推究原因應是 Dapper 處理 INSERT 及 WHERE 時 @d 對應成 SQL DateTime 型別而非 DateTime2 來回處理過程產生誤差。一個簡單的解決方法是加入 SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2); 指定將 C# DateTime 對應成 SQL DateTime2：

static void Main(string[] args)
{
    using (var cn = GetConnection())
    {
        cn.Execute("DELETE FROM MyTable");
        Enumerable.Range(1, 32).ToList().ForEach(i =>
         {
             Thread.Sleep(100);
             var d = DateTime.Now;
             Console.Write($"{i:00}. TimeKey={d:yyyy/MM/dd HH:mm:ss.fff} : ");
             SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);
             cn.Execute("INSERT INTO MyTable (TimeKey,SeqNo) VALUES (@d, @i)",
             //...略...

重測一次，完全相等。

Tips of difference in precisions between SQL DateTime and DateTime2 type when using them in C#.