DateTime interpretation between ADO.Net and SQL Server

DateTime interpretation between ADO.Net and SQL Server

• francescoS

  There is a DataColumn.DateTimeMode property that affects the serialization format for datetime data in a DataSet, but I don't think that applies in this case. Just wanted to mention it.

  I wrote a sample that I think does what you have described, but I am not seeing the problem. Can you look at this and figure out what is different about your scenario

  Scripts for table creation:

  create table MyTable (MyDateCol datetime PRIMARY KEY)
  create table MyChildTable (MyDateColParent datetime)
  ALTER TABLE MyChildTable ADD CONSTRAINT FK_MyChildTable_MyTable FOREIGN KEY([MyDateColParent]) REFERENCES MyTable([MyDateCol])
  

  Code:

  using (SqlConnection sqlConn = new SqlConnection(@"Data Source=myServer;Integrated Security=SSPI;Initial Catalog=mydb"))
  {
  sqlConn.Open();
  string tabName = "MyChildTable";
  SqlCommand cmdSelect = new SqlCommand("SELECT * FROM " + tabName, sqlConn);
  SqlDataAdapter da = new SqlDataAdapter(cmdSelect);
  SqlCommandBuilder cb = new SqlCommandBuilder(da);
  DataSet ds = new DataSet();
  da.Fill(ds, tabName);
  DataRow dr = ds.Tables[tabName].NewRow();
  DateTime dt = DateTime.Parse("8/22/2006 4:20:21 PM");
  dr["MyDateColParent"] = dt;
  ds.Tables[tabName].Rows.Add(dr);

  SqlCommand cmdInsert = new SqlCommand("INSERT INTO MyTable (MyDateCol) VALUES ('" + dt.ToString() + "')", sqlConn);
  cmdInsert.ExecuteNonQuery();

  da.Update(ds.Tables[tabName]);
  }

  Thanks,
  Sarah

  
  
  

• nattylife

  All right, this was not the end of it. Please, nobody trust me from now on... Although the conversion between local and UTC is something to bear in mind when working with DateTime types, it wasn't my problem. Feel free not to trust me on that one either, but I think I finally got it. The problem was - brace yourself - rounding errors!

  The first thing I did to confirm my previous assumption was to change the Kind in my DateTime object to Unspecified. If the Kind conversion was the problem, that should have taken care of it. But it didn't. Then I decided to see by how much do the two DateTimes really differ. Looking at the Ticks property it seemed huge but then again - those are 100ns intervals. It turns out that whether the object is UTC or Local, the difference was small, much less than the expected 5 hours (I'm on the East coast). How small Less than a second... Then it hit me - SQL server keeps a resolution of milliseconds, just like the DateTime .Net object. When I send it using Update(), it sends the millisecond portion, as well. But when I parse it off of the string, there are no milliseconds and the sub-field gets a value of "0". That is enough for the entire DateTime field to differ and thus - to get the constraint violation. To confirm that, I went through a "hack" to remove the constraint as the program was running (because it otherwise would correct the schema discrepancy), and this allowed the differing value to be written anyway. Looking at the table using the Visual Studio browser (or the SQL Server 2005 management studio Express), I could see no difference between the two values. But, then again, none of those utilities (which, by the way, seem to be using the same underlying code), show the millisecond values. If I had a tool to retrieve the full DateTime values off of the server, I'm sure I could see that one has "0" as milliseconds, and the other - some other, real value.

  As a consequence, now I understand that what my fix was actually doing is rounding off the DateTimes to whole number of seconds. As I said - not a big deal. And I can't fix that unless I find a way to represent Transact SQL TIMESTAMPS as a string with the full resolution. On to the SQL Server forums...

  So the same problem that used to plague me when I was learning to program some quarter century ago - rounding errors - hit hard again! I hope this exercise was useful for everyone, and sorry for the diversions!

  Kamen

  
  
  

• Andy Swinton

  Just a short note to update you on the remaining issue of getting the date/time to the server at full resolution. The following format will do the job:

  MyDateTimeObj->ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'fff", DateTimeFormatInfo::InvariantInfo)

  However, you need to bear in mind that the resolution of the DATETIME type in SQL Server can vary and is often 3 ms, while the one of System::DateTime is 100 ns. But with the above conversion (which is in an ISO format and is culture-invariant) I got repeatable results with higher resolution than before (and no need to take the CPU time to truncate the DateTime object's value when loading it in the proper column of the tables in the DataSet).

  Kamen

  
  
  

• Ted12893

  I think that's what Kamen is already doing. My example was just simplified and didn't use parameters.

  However, even with parameters I am still not seeing the problem, and I can't figure out where the conversion would be happening. Kamen, I know you got it working, but if you want to understand what's really happening, could you post enough code to demonstrate what you are seeing (similar to what I did), or send me a simple project via e-mail My e-mail address is in my forum profile, but you have to REMOVE the "online" from the address. I don't quite understand how you have everything hooked up, and I'm sure there's some small thing that's making a difference. It would be easier if you could just show me what you're doing in code.

  When you assign a string to the DateTime column in the DataTable, it can just use Convert.ToDateTime or DateTime.Parse, it doesn't need a DateTime constructor with a string.

  Thanks,
  Sarah

  
  
  

• Anpiro

  Thank you for taking the time for this, Sarah, that is very kind of you (as always)!

  The main difference, which is probably the key to the problem, is that I form the InsertCommand differently - using SqlParameters. The way you are dong it, it kinda makes the use of a DataSet redundant and, most importantly, you'd have to generate a new InsertCommand for every row of data. I omitted to mention that performance is of utmost importance to me. I am defining the InsertCommands ahead of time, then filling in the tables in the DataSet, and then using Adapter->Update() with batch size of 0 (so they are all bundled in a single RPC).

  I think the DateTime conversion may be occurring during the population of the parameters. By the way, I wanted to post a follow-up yesterday but the Forums' server was down. Here is what I wanted to say:

  Well, something's definitely going on: I changed the code that assigned the DateTime object to the column in the DataRow as follows:

  from: NewRow["MyDateCol"] = MyDateTimeObject

  to: NewRow["MyDateCol"] = MyDateTimeObject->ToString()

  and now it's working fine! There is definitely some conversion going on here but I don't know where. ToString() uses the 'G' format specifier with the current culture, but I don't see how formatting can change the value of the DateTime object. I should mention that MyDateTimeObject->Kind is "Local", so no "unspecified" ambiguities.

  I think if you changed the generation of the SqlCommand to something like:

  SqlCommand cmdInsert = new SqlCommand("INSERT INTO MyTable (MyDateCol) VALUES (@MyDateCol)", sqlConn);
  cmdInsert->Parameters->Add(gcnew SqlParameter(@MyDateCol, SqlType::DateTime, 0, MyDateCol);

  then you might see what I ran into. Even though what I wanted to do is working now, I'd like to see this mystery resolved.
  

  Thanks again,

  Kamen

  
  
  

• Elfar Alfreðsson - MCAD

  Sarah, I think I was able to save you from dealing with my brawny project, which is actually a DLL that is being loaded by another process - both projects being hundreds of lines of managed and native C++ code... Luckily, you gave me enough information to hunt the problem down - somehow, even after reading the documentation of the DateTime type, and reading your sample code (I must admit - I don't know C#), I had missed the Parse method. Now that I know what the DateTime column was doing to get the value, I was able to do a simple test:

  DateTime ^DTRaw = SessionConfig->GetStartTime();
  DateTime ^DTParsed = (gcnew DateTime)->Parse(SessionConfig->GetStartTime()->ToString());

  where SessionConfig->GetStartTime() is what holds the DateTime object in question. Looking at the dates of both objects it showed the exact same thing: {23/8/2006 15:11:48 System.DateTime}. But the number of ticks was different! That made me check the Kind property and it was the parsed object that had the Unspecified kind (as I mentioned above, my DateTime object was of the Local kind). The reason why you can't reproduce the problem is because you load your DateTime object using Parse() in the first place, and thus you are creating it in the Unspecified kind. If you were to use DateTime::Now(), like I did, you'd get it in Local time and then you would see the difference.

  When I create my original "index" table, I use plain SQL and thus I have no choice - SQL does not distinguish between local and UTC in its DATETIME data type. It appears that when the ADO.Net methods write DateTime objects, there is some implicit conversion - such that an Unspecified kind is assumed to be UTC and not local; same behavior as if you were to call ToLocalTime(). Anyway, it seems like what I did to "patch" this problem appears to be the best I could do. And it's not a disaster that I have no control over whether the DateTime is local or UTC - in my database, the DateTime field is really relative, so as long as it stays consistent, even though it is "unspecified", it should be all right.

  Thanks again for your help and tremendous dedication!

  Kamen

  
  
  

• dougakers

  I just tried changing NewRow["MyDateCol"] = MyDateTimeObject->ToString() to NewRow["MyDateCol"] = MyDateTimeObject->ToUniversalTime() in order to test the assumption that when you send to the SQL Server a DateTime object as a string (in the original SQL statement), it assumes it is UTC. Which would be a problem if after that the DateTime is sent as local (which is how it is stored in the DataSet). But that didn't work. The strange thing is that the type of the DataColumn item in NewRow["MyDateCol"] is already set to DateTime so even though I am passing it a String, it should know to convert but ... DateTime does not have an overload of its constructor that takes a String! How does that work I think that is where the explanation of this problem can be found.

  Kamen

  
  
  

• Lawrence 007

  I suggest when using datatypes as datetime do not put it in the command text as string. Use sqlparameters. Meaning that, the statement will be:
  
  Dim theDate as DateTime = "8/22/2006 4:20:21 PM"
  command.CommandText = "INSERT INTO MyTable (MyDateCol) VALUES (@MyDateCol)"
  command.Parameters.Add( "@MyDateCol", theDate )
  command.ExecuteNonQuery()
  
  
  
• DateTime interpretation between ADO.Net and SQL Server