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

[Manikandan]

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

SQL server table details
Table name:date_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

 

[Rad [Visual C# MVP]]

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

SQL server table details
Table name:date_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

Try using a parameterized query, and let .NET and SQL server agree
precisely how to store the value of the datetime.

Calling DateTime.Now.ToString() will not return the millisecond
values. On my PC for instance it returns the following:

7/11/2007 9:45:34 PM

 

[Roman Stehrenberger]

Hi

is your time value generated outside the database or do you need it as a
verification when the record has been generated or modified? If it is the
latter one, use for insert the default value constraint of the database
field. For updates, you need a trigger on the table definition. SQL-Server
inserts the values with the milli second part.

If you need more help, please ask me directly on mail, I can send you some
sample code for SQL-Server

Regards

Roman

 

[Rick Lones]

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

SQL server table details
Table name:date_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

Try using a parameterized query, and let .NET and SQL server agree
precisely how to store the value of the datetime.

Calling DateTime.Now.ToString() will not return the millisecond
values. On my PC for instance it returns the following:

7/11/2007 9:45:34 PM

Not by default - it will if you tell it to though, as per:
DateTime.Now.ToString("M/dd/yyyy H:mm:ss.fff tt")

-rick-

 

[Rad [Visual C# MVP]]

Not by default - it will if you tell it to though, as per:
DateTime.Now.ToString("M/dd/yyyy H:mm:ss.fff tt")

-rick-

Which is what I'm saying. If you call the plain ToString() and pass
that, you lose the precision of the smaller units