Losing DateTimeOffset precision when using C#

Question

I have a SQL Server table with a CreatedDate field of type DateTimeOffset(2).
A sample value which is in the table is 2010-03-01 15:18:58.57 -05:00

As an example, from within C# I retrieve this value like so:

var cmd = new SqlCommand("SELECT CreatedDate FROM Entities WHERE EntityID = 2", cn);  
var da = new SqlDataAdapter(cmd);  
DataTable dt =new DataTable();  
da.Fill(dt);

And I look at the value:
MessageBox.Show(dt.Rows[0][0].ToString());

The result is 2010-03-01 15:18:58 -05:00, which is missing the .57 that is stored in the database.

If I look at dt.Rows[0][0] in the Watch window, I also do not see the .57, so it appears it has been truncated.

Can someone shed some light on this? I need to use the date to match up with other records in the database and the .57 is needed.

Thanks!
Darvis

Answer 1

SQL Server doesn't have millisecond precision.

Here's an article that goes into the details:

Advanced SQL Server DATE and DATETIME Handling

EDIT

I'm guessing C# shouldn't show the same behavior. Keep in mind that the DateTime.ToString() method doesn't include milliseconds unless you specify a Format Provider.

The Watch window would also be showing you ToString(). Try setting a Breakpoint and use the Inspector to get the more detailed information being stored and see if you mililseconds are there.

Answer 2

Fill should change the data in the dataset to match the data in the datasource -- have you tried any other methods to retrieve the data to see if other classes retrieve the data in the format you are hoping for? Simple tests like sqldatasource/gridview, etc