ADO.net is giving me the WRONG date here...

  • Thread starter Thread starter 0to60
  • Start date Start date
0

0to60

I have a table with a datetime column. Then, in my C# program I open a
datatable with that column in it and access it with the following syntax:

dt.rows["datefield"]

Now, when I look at the data in the table from say Enterprise Manager, the
row in question has an "11/17/2005 8:15:29 am" in it. But, my ado.net code
yields a "11/17/2005 8:15:28 am". This isn't for all the rows in the
datatable, only a few. Is there some sort of "conversion" that goes on
between .net and SQL Server? Is there some rounding going on?

Any help would be much appreciated.
 
Oto60

Your datetime field is in the SQL server in units of ticks from
milliseconds*10/3 starting at 1-1-1753. In Net it is units of
nanoseconds*100 starting at 1-1-1 0:0:0

The representation of DateTime in the Microsoft software is on more places
different. VBNet represent it everywhere in the USA (literal) format, C#
shows it to you in your local format. It cost me as well often a lot of time
to check if the representation is right when I am busy with different
systems, (which is at the moment the nicest done in my opinion in C# 2005)

Normally there should for you not be any reason to do anything in the
conversion between the 2 tick types by the way.

I hope this helps,

Cor
 
If the precision between .NET and SQL-Server is really important, one
possible solution would be to convert the datetime to a string directly on
the SQL-Server before returning the result.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Cor Ligthert said:
Oto60

Your datetime field is in the SQL server in units of ticks from
milliseconds*10/3 starting at 1-1-1753. In Net it is units of
nanoseconds*100 starting at 1-1-1 0:0:0

The representation of DateTime in the Microsoft software is on more places
different. VBNet represent it everywhere in the USA (literal) format, C#
shows it to you in your local format. It cost me as well often a lot of
time to check if the representation is right when I am busy with different
systems, (which is at the moment the nicest done in my opinion in C# 2005)

Normally there should for you not be any reason to do anything in the
conversion between the 2 tick types by the way.

I hope this helps,

Cor


0to60 said:
I have a table with a datetime column. Then, in my C# program I open a
datatable with that column in it and access it with the following syntax:

dt.rows["datefield"]

Now, when I look at the data in the table from say Enterprise Manager,
the row in question has an "11/17/2005 8:15:29 am" in it. But, my
ado.net code yields a "11/17/2005 8:15:28 am". This isn't for all the
rows in the datatable, only a few. Is there some sort of "conversion"
that goes on between .net and SQL Server? Is there some rounding going
on?

Any help would be much appreciated.
Click to expand...
Click to expand...
 
Sylvain,
If the precision between .NET and SQL-Server is really important, one
possible solution would be to convert the datetime to a string directly on
the SQL-Server before returning the result.
Click to expand...
On what do you base this advice, in my opinion is it the worst I have seen
about .Net this year.

Cor
 
And could you explain to me why it is so worse to resolve a difference in
precision between two different systems by using an intermediate character
representation?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
 
And could you explain to me why it is so worse to resolve a difference in
precision between two different systems by using an intermediate character
representation?
Click to expand...
Because you cannot fix that with a string, a string cannot be more precise
than one of those.

The .Net datetime can give the exact equivalent in the same precision as the
datetime in SQL server, while the String is not always able to do that, it
has to be rounded.

However worse is that a datetime in a String has the bad behaviour to be
culture depended, something that are the DateTime formats in SQL server and
..Net not.

Cor
 
I never said that a string was more precise, I only said that by using a
string, the value "11/17/2005 8:15:29 am" as seen on the SQL-Server will
always been seen exactly like this on the .NET side and never become
something like "11/17/2005 8:15:28 am" by magic because of some floating
point conversion problem between .NET and SQL-Server.

You say in your previous post that « The .Net datetime can give the exact
equivalent in the same precision as the datetime in SQL server » ; I agree
with you that this should be the case in a perfect world and I don't know
how the original discrepancy that the OP saw had occured. Maybe it's
normal, maybe not; I don't know because for one reason I didn't take the
time of exploring any further this conversion problem. Furthermore, I don't
know how OP will react to a suggestion like this or if he will be plagued by
culture problem or anything else. In fact, I don't even know if this is a
real problem for him or only a curiosity matter; however it's up to him to
take any decision on that point and not to you to say that my suggestion was
stupid.

Finally, I don't remember having read any solution from your previous posts.
Saying that the numbers of ticks between SQL-Server and .NET are not the
same is an explanation, not a solution.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
 
Sylvain,

In fact, I don't even know if this is a real problem for him or only a
curiosity matter; however it's up to him to take any decision on that
point and not to you to say that my suggestion was stupid.
Click to expand...
I did not say that your suggestion was stupid, I said that it was in my
opinion bad. In my opinion not such a slight difference.

The problem with the representation will probably be that those datetimes
are given in a very high precision. In that way are they than as well in the
database. Because the fact that the ticks are (as I wrote) in SQL server
precise until milliseconds*10/3 and in Net in nanoseconds / 100 will that
probably give rounding problems when represented to string. If there is a
less precision wanted, than the time has in my idea to be given in less
precise values. By instance
dt = new DateTime(2005,11,29,7.08.00)

I hope that this gives the idea

Cor
 
Back
Top