DateTime Problem



I'm selecting some columns using a .net dataadapter using Select myDate from
myTable. then I...
DIM dt as new datatable

Now in dubugging I am examining the dt.rows(0).ItemArray (because I didn't
trust what was displaying in my datagrid)

It shows my date as being 8/31/2005 1:26:45 PM. The problem is that the
real date in the database is 8/31/2005 1:26:45:197 PM !!!

Something in the data adapter or something somewhere else is cutting off the

This is a big problem for me because I use this date later in a where clause
for an update and the lack of the 197 is causing the where to fail.

Why is the 197 being cutoff? What can I do about this?




Kevin Spencer

Hi Tina,

SQL Server supports 2 DateTime data types. The datetime data type has an
accuracy of 3/100ths of a millisecond, or yyyy/mm/dd hh:mm:ss.---, whiich is
what you're seeing in the database. A DataTable is a serialized
representation of data from a database. The DateTime.ToString() method
returns a string representation of a date that is accurate to the second.
Therefore, the extra precision is "lopped off."

Of course, the problem is how to reconcile the 2 dates of differing
precision. I doubt that you need the precision you're storing in the
database. You may want to truncate the milliseconds when doing an INSERT.
Another alternative is to use the DATEDIFF Transact-SQL function, with "s"
as the DatePart parameter in your query. Using DATEDIFF, however, will need
to perform a full table scan, whereas if the data is already truncated, it
only has to be done once.


Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question