如何以某种格式将日期和时间插入SQL Server数据库? [英] How Do I insert Date and Time into SQL Server database in certain format?

问题描述

我在C#中使用Windows窗体.

I am using Windows forms in C#.

我正在尝试在SQL Server数据库中填充一个包含 DateTime 列的表.

I am trying to fill a table in a SQL Server database which contains DateTime column.

现在，当我的PC的时钟格式为 dd-MM-yyyy 时，当我尝试通过以下查询插入数据时，会出现错误

Now when my PC's clock format is dd-MM-yyyy and when I try to insert the data with the following query, I get an error

将varchar数据类型转换为日期时间数据类型会导致超出范围的值

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

代码:

 SqlConnection cn = new SqlConnection("Data Source=PCN11-TOSH;Initial Catalog=mydb;Integrated Security=True");
 cn.Open();

 SqlCommand cm = new SqlCommand("Insert into Orders (Order_number, Phone, DateTime, address ) values ('" 
                  + textBox1.Text + "','" + textBox2.Text + "', '"+  DateTime.Now  +"' ,'" 
                  + textBox3.Text + "')");

 cm.Connection = cn;
 cm.ExecuteNonQuery();
 cn.Close();

但是当我将PC的时钟格式更改为 yyyy-MM-dd 时，它可以正常工作.看来SQL Server仅接受 yyyy-MM-dd 格式.

But when I change my PC's clock format to yyyy-MM-dd it works fine. It seems that SQL Server only accepts yyyy-MM-dd format.

我的问题是:如何根据PC的时钟格式( dd-MM-yyyy )将 DateTime 插入SQL Server列?

My question is : how can I insert DateTime into SQL Server column according to my PC's clock format (dd-MM-yyyy)?

谢谢

推荐答案

使用参数化查询代替串联sql命令文本.

Instead of concatenating your sql command text, use parameterized query.

它不仅可以保护您免受sql注入的侵扰，而且还使用强类型的参数(例如，参数应具有 SqlDbType.DateTime 的类型以传递日期时间值)，因此问题将消失.

It will not only protects you from sql injection, but also it uses strongly typed parameters (for example, parameter should has type of SqlDbType.DateTime to pass datetime values), thus problem will gone.

var cm = new SqlCommand("Insert into Orders (Order_number, Phone, DateTime) values (@OrderNumber, @Phone, @DateTime)");
cm.Parameters.Add("@OrderNumber", SqlDbType.VarChar);
cm.Parameters["@OrderNumber"].Value = textBox1.Text;
cm.Parameters.Add("@Phone", SqlDbType.VarChar);
cm.Parameters["@phone"].Value = textBox2.Text;
cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
cm.Parameters["@DateTime"].Value = DateTime.Now;

这篇关于如何以某种格式将日期和时间插入SQL Server数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！