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)

   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;

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



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

         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 ??



Oren Ellenbogen


4 thoughts on “Inconsistency of SqlParemeter from SqlDbType.DateType with Sql Server 2005

  1. SQL Server’s DATETIME is not accurate to the millisecond. I’ve read somewhere it has the accuracy of three-hundredth-of-a-second so that’s why it rounds up the value (though it should have rounded it down…).

  2. This is not a problem, actually. It’s about the datatype.
    The datatime in SQL Server is accurate to 3 milliseconds, that is all.
    It is bad idea in general to use dates as exact keys, since there are wide differences in their accuracies accross machines.

  3. well, i checked it out on a SQL server 2000.
    it gave me the same effect.

    the strange thing is that the profiler gets the query in the altered condition,
    so , it gives us a clue that the problem is not in the SQL server itself.

    a quik view in the MSDN on the SqlDbType "DateTime" shows us an interesting thing :

    "Date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds"

    another dig in the "SQL server books online" (The help for the sql server)
    says :


    Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

    Example Rounded example
    01/01/98 23:59:59.999 —– > 1998-01-02 00:00:00.000

    01/01/98 23:59:59.995,
    01/01/98 23:59:59.996,
    01/01/98 23:59:59.997, or
    01/01/98 23:59:59.998 —— > 1998-01-01 23:59:59.997

    01/01/98 23:59:59.992,
    01/01/98 23:59:59.993,
    01/01/98 23:59:59.994 ——- > 1998-01-01 23:59:59.993

    01/01/98 23:59:59.990 or
    01/01/98 23:59:59.991 ——- > 1998-01-01 23:59:59.990

    Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999.

    so according to this info, i would’nt expect it to be accurate to the milisecond.

    this info is valid to the SQL server 2000, maybe in the 2005 version they changed the rounding table…

Comments are closed.