Inconsistency of SqlParemeter from SqlDbType.DateType with Sql Server 2005

This is one of the strangest things I’ve seen lately, but it turns out that sending “2006-04-25 14:15:26:421” (via SqlParameter from SqlDbType.DateTime) to my Sql Server 2005 database has been saved as “2006-04-25 14:15:26:423“. Does it happen every time you might ask? well, the sad answer is NO; Sometimes(really, just sometimes) it happen and sometimes it don’t.

Why do I need such accuracy? Well, this DateTime column is one of the Primary Keys in my table. Trying to pull out “2006-04-25 14:15:26:421” will give me… nothing !

I wanted to reproduce it on a clean sheet so I created a new table named “CreationDates” with the single column “CreationDate” which is from DateTime type of course.

Then I’ve written the following code:

class Program
{
   static void Main(string[] args)
   {
      Init();
      Test();
   }

   private static void Init()
   {
      // Default culture – hebrew (I need it this way).
      CultureInfo israel = new CultureInfo(“he-il”);
      israel.DateTimeFormat.LongTimePattern = “HH:mm:ss.fff”;

      Thread.CurrentThread.CurrentCulture = israel;
      Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;
   }

   private static void Test()
   {
      string connString = @”MyConnectionString”;
      using (SqlConnection conn = new SqlConnection(connString))
      {
         string query = “INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)”;
         SqlCommand cmd = new SqlCommand(query, conn);

         DateTime dt = DateTime.Now;

         SqlParameter p1 = new SqlParameter(“CreationDate”, SqlDbType.DateTime);
         p1.Value = dt;
         cmd.Parameters.Add(p1);

         Console.WriteLine(“parameter time: “ + ((DateTime)p1.Value).ToString());

         conn.Open();

         cmd.ExecuteNonQuery();
      }

      using (SqlConnection conn = new SqlConnection(connString))
      {
         string sQuery = “SELECT TOP(1) CreationDate FROM CreationDates ORDER BY CreationDate DESC”;
         SqlCommand sCmd = new SqlCommand(sQuery, conn);

         conn.Open();
         SqlDataReader reader = sCmd.ExecuteReader();
         if (reader.Read())
         {
            Console.WriteLine(“db time: “ + reader.GetDateTime(0).ToString());
         }
      }
   }
}

* I know, I could make it nicer, but this is a simple test for god sakes !

In one of my tests, this code produce the following output:

The Sql Server 2005 Profile shows:

exec sp_executesql N’INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)’,N’@CreationDate datetime’,@CreationDate=”2006-04-25 14:15:26:423”

So I’ve sent “2006-04-25 14:15:26:421” but the database received “2006-04-25 14:15:26:423” !

Any smart ideas ??