Sql Datetime
-
Hello, I am using the following piece of code to save data to my database:
SqlDateTime date = DateTime.Now; SqlCommand myCommand2 = new SqlCommand("INSERT INTO orders (date,customerid,productid,sum) VALUES ('" + date + "'," + index + ",'" + productid + "','" + aantal + "')", connect); myCommand2.ExecuteScalar();the date-field in the database has the property datetime. When i try to run the query, he always gives following error:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.rnThe statement has been terminated.I tried to use DateTime.Now directly in the query, but it still gives the same error, also writing a date by hand (in the values-field in the query) gives an error. When I write a date (exactly the same format as the one above) directly in the database (without a query), i have no errors what so ever. Can somebody help? Thanks! -
Hello, I am using the following piece of code to save data to my database:
SqlDateTime date = DateTime.Now; SqlCommand myCommand2 = new SqlCommand("INSERT INTO orders (date,customerid,productid,sum) VALUES ('" + date + "'," + index + ",'" + productid + "','" + aantal + "')", connect); myCommand2.ExecuteScalar();the date-field in the database has the property datetime. When i try to run the query, he always gives following error:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.rnThe statement has been terminated.I tried to use DateTime.Now directly in the query, but it still gives the same error, also writing a date by hand (in the values-field in the query) gives an error. When I write a date (exactly the same format as the one above) directly in the database (without a query), i have no errors what so ever. Can somebody help? Thanks!Have you had a look at at the actual SQL string produced by your string concatenation? I'm guessing there's a format mismatch, maybe due to some regional settings or something. (Often a problem when putting datetimes into the db like this.) A safe and easy way to fix this would be to use parameters in your SQL query:
string query = @"
INSERT INTO orders (date,customerid,productid,sum)
VALUES (@date, @customerid, @sum)
";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@date", DateTime.Now);
cmd.Parameters.Add("@customerid", customerid);
cmd.Parameters.Add("@sum", sum);
cmd.ExecuteNonScalar(); -
Hello, I am using the following piece of code to save data to my database:
SqlDateTime date = DateTime.Now; SqlCommand myCommand2 = new SqlCommand("INSERT INTO orders (date,customerid,productid,sum) VALUES ('" + date + "'," + index + ",'" + productid + "','" + aantal + "')", connect); myCommand2.ExecuteScalar();the date-field in the database has the property datetime. When i try to run the query, he always gives following error:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.rnThe statement has been terminated.I tried to use DateTime.Now directly in the query, but it still gives the same error, also writing a date by hand (in the values-field in the query) gives an error. When I write a date (exactly the same format as the one above) directly in the database (without a query), i have no errors what so ever. Can somebody help? Thanks!SqlServer likes to see a char dates in a specfic format. You will have to experiment a little but from memory it likes: 01/01/98 OR 01/01/98 23:59:59.999 It is not very tolerant with other formats. Try
date.ToString("dd/MM/yyyy HH:mm:ss.000")in place ofdateBTW: I normally let the SQL Server set a DateTime stamp by usingGetDate()in place of'" + date + "'. It will usually have a more accruate time then a workstation (at least it will be a common clock). There is alsoGetUTCDate()for when your users are in different time zones. -
Have you had a look at at the actual SQL string produced by your string concatenation? I'm guessing there's a format mismatch, maybe due to some regional settings or something. (Often a problem when putting datetimes into the db like this.) A safe and easy way to fix this would be to use parameters in your SQL query:
string query = @"
INSERT INTO orders (date,customerid,productid,sum)
VALUES (@date, @customerid, @sum)
";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@date", DateTime.Now);
cmd.Parameters.Add("@customerid", customerid);
cmd.Parameters.Add("@sum", sum);
cmd.ExecuteNonScalar(); -
SqlServer likes to see a char dates in a specfic format. You will have to experiment a little but from memory it likes: 01/01/98 OR 01/01/98 23:59:59.999 It is not very tolerant with other formats. Try
date.ToString("dd/MM/yyyy HH:mm:ss.000")in place ofdateBTW: I normally let the SQL Server set a DateTime stamp by usingGetDate()in place of'" + date + "'. It will usually have a more accruate time then a workstation (at least it will be a common clock). There is alsoGetUTCDate()for when your users are in different time zones.