Millisecond values missing when inserting datetime into datetime column of sql Server

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

Look at this post: http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

You'll have to use a CAST or a CONVERT in that INSERT statement to the format you desire.

|||

You have got the SQL Server part right but the .NET type you are using the wrong data type, to get milliseconds you have to use INT32, INT64 or Double the later two does not exist in SQL Server so you have to do conversion. I have found you two links with ready to use code, pay close attention to the string and formatting code. Hope this helps.

http://blogs.msdn.com/kathykam/archive/2006/09/29/.NET-Format-String-102_3A00_-DateTime-Format-String.aspx

http://authors.aspalliance.com/aspxtreme/sys/datetimeclass.aspx