Handling Miliseconds in .NET and SQL Server

Have you taken a look at number of such a questions in online community? It looks like that a lot of .NET developers have a problems reading and updating milliseconds value from SQL Server databases. So what is a real problem here?
Let's take a look at common scenario. Developers read data from a table which has Datetime column ColDateTime. Let's say they are storing values in some DataTable table1. Suddenly, if you want just to print a Value of that field, with

table1.Rows[0].Cells["ColDateTime"].Value.ToString()

you will get value like '2008/02/26 2:26:53 PM'. But you value in database is actually '2008/02/26 14:26:53.3480'. So milliseconds are missing. The only way to get them is to convert value from database in DateTime variable and then print out that variable:

DateTime dtDbValue = System.Covert.ToDateTime(table1.Rows[0].Cells["ColDateTime"]);
dtDbValue.ToString();

Now, you have a real value: '2008/02/26 14:26:53.3480'

Connected to this is also reverse process, where people lose milliseconds during update. Take a look at following statements:

DateTame dtValue = DateTime.Now;

string sql = "update table1 set ColDateTime = '" + dtValue.ToString() + "'";

-- create DataCommand, assign CommandText and execute.

You will see that your column has been updated with the wrong value. Again milliseconds cut.

People try very often to format ToString method call to looks like:
string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.ffff") + "'";

but this cause an exception on SQL Server side saying that varchar value can not be converted to Datetime type. Strange, and maybe lack of documentation, but the solution is to format your DateTime value with different format - using 3 f and not 4:

string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.fff") + "'";


Compare databases fast, safe, free... DBTyP.NET - in version 2008 supports Oracle besides SQL Server and MySQL. DBTyP.NET 2008 is coming... in 3 weeks.