469,299 Members | 2,078 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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 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[i]["no"].ToString();
DateTime dt=(DateTime)dt1.Rows[i]["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

Jul 9 '07 #1
4 5211
On Mon, 09 Jul 2007 15:13:52 -0700, Manikandan
<pl**********@gmail.comwrote:
>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[i]["no"].ToString();
DateTime dt=(DateTime)dt1.Rows[i]["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

--
http://bytes.thinkersroom.com
Jul 11 '07 #2
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
"Manikandan" <pl**********@gmail.comschrieb im Newsbeitrag
news:11*********************@w3g2000hsg.googlegrou ps.com...
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[i]["no"].ToString();
DateTime dt=(DateTime)dt1.Rows[i]["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

Jul 16 '07 #3
Rad [Visual C# MVP] wrote:
On Mon, 09 Jul 2007 15:13:52 -0700, Manikandan
<pl**********@gmail.comwrote:
>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[i]["no"].ToString();
DateTime dt=(DateTime)dt1.Rows[i]["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-
Jul 17 '07 #4
>>
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-
Which is what I'm saying. If you call the plain ToString() and pass
that, you lose the precision of the smaller units

--
http://bytes.thinkersroom.com
Jul 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Art at ABE Computer Consultants | last post: by
6 posts views Thread by Dean Slindee | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.