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

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[i]["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.

Cor
Tue Nov 29 00:02:16 CST 2005

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" <holeshot60_nospam_@yahoo.com> schreef in bericht
news:uItNrbG9FHA.3592@TK2MSFTNGP12.phx.gbl...
>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[i]["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.
>

Sylvain
Tue Nov 29 10:23:44 CST 2005

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 [MVP]" <notmyfirstname@planet.nl> wrote in message
news:OMzKaqK9FHA.1420@TK2MSFTNGP09.phx.gbl...
> 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" <holeshot60_nospam_@yahoo.com> schreef in bericht
> news:uItNrbG9FHA.3592@TK2MSFTNGP12.phx.gbl...
>>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[i]["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.
>>
>
>

Cor
Tue Nov 29 11:03:01 CST 2005

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.
>
On what do you base this advice, in my opinion is it the worst I have seen
about .Net this year.

Cor

Sylvain
Tue Nov 29 12:16:10 CST 2005

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


"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
news:OBl$yaQ9FHA.3416@TK2MSFTNGP15.phx.gbl...
> 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.
>>
> On what do you base this advice, in my opinion is it the worst I have seen
> about .Net this year.
>
> Cor
>

Cor
Tue Nov 29 12:31:37 CST 2005

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

Sylvain
Tue Nov 29 17:23:16 CST 2005

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


"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
news:uv6ETMR9FHA.1332@tk2msftngp13.phx.gbl...
>> 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?
>>
> 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
>
>
>

Cor
Wed Nov 30 00:26:18 CST 2005

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