在SQL Server中插入smalldatetime

本文关键字：插入 smalldatetime Server SQL | 更新日期: 2023-09-27 18:25:00

我正在尝试将日期插入SQL Server 中的smalldatetime列

我试着这样做：

DateTime  transfer_date;
transfer_date = DateTime.Now;
SQL = "insert into MyTbl (DateT) values (transfer_date)";
SqlCommand Cmd_SQL = new SqlCommand(SQL, Conn_SQL);
Cmd_SQL.CommandText = SQL;
Cmd_SQL.ExecuteNonQuery();

但我得到了这个错误：

将varchar数据类型转换为smalldatetime数据类型导致值超出范围。语句已终止。

在SQL Server中插入smalldatetime

您需要定义一个参数化查询，然后设置参数值-类似于以下内容：

// define SQL statement to use, with a parameter
string sqlStmt = "insert into dbo.MyTbl (DateT) values (@transferDate)";
// define connection and command objects
using (SqlConnection conn = new SqlConnection(your-connection-string-here))
using (SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    // add parameter and set value
    cmd.Parameters.Add("@transferDate", SqlDbType.SmallDateTime).Value = DateTime.Now;
    // open connection, execute SQL query, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

您当前根本没有对transfer_date变量执行任何操作。SQL语句包含文本transfer_date，但它不会自动从数据库中获取值。你想要类似的东西：

// @transfer_date is now a *parameter*.
string sql = "insert into MyTbl (DateT) values (@transfer_date)";
// Avoid using a shared connection - it'll cause problems. Let the connection
// pooling do its job. But use using statements to ensure that both the connection
// and the statement are disposed.
using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlCommand(sql, connection))
    {
        // No need to set the CommandText value now - it's already set up above.
        // But we need to set the value of the parameter.
        command.Parameters.Add("@transfer_date", SqlDbType.SmallDateTime).Value
             = DateTime.Now;
        command.ExecuteNonQuery();
    }
}
相关文章：